I have a test database server, at the time the server was setup it had minimal drives and space on those drives was small. It was setup on the stingy side because it is a testing server. I knew I would have problems with it if I was going to do any work on the majority of my databases due to the size limitations.
Recently, I had additional drives and space added so I wanted to move some things around a bit. This is a short post on how to move data and log files for your database. We are going to be talking about the TempDB today but this code and method works for all your databases.
Find File and Name Location
Before we move any files around we need to make sure we know where they are. We have standard configurations but some of the servers were setup prior to the establishment of standards, so the data files can be almost anywhere. I know, it’s not the best environment but as servers are modified I try and get things closer to our standards.
Open a query window in SQL Server Management Studio (SSMS) and type or copy the following code into the window and execute.
Your results will be like this. Notice the files are located on the D:\TempDB drive and folder.
Move The Files
Now that we know where the files are we will move them to the T:\TempDB drive by executing the following command.
MODIFY FILE ( NAME = logical_file_name, FILENAME = ‘ new_path/os_file_name ‘ )
This is the code I used to my the files.
For this demo and to keep things simple I moved the log file as well. Ideally, you want to place the log file with other log files on their own drive separated from data files.
Restart SQL Server
Now, we want to restart SQL Server
Now we want to confirm the files are where we want them to be.
OK, so the files are now moved to where we wanted them, our work here is done.
As you can see this is very handy and a really good thing to know, especially if you cover other projects that have difficulty managing their disc space. Not that something like that would go on in my shop or yours for that matter. J
If you want to read up more on ALTER Database T-SQL, here is the link to MSDN