How to Move Database Files in SQL Server: Part 1
Moving database files in SQL Server is a very common task. Here we’re going to show you how to do it with minimal interruption to the server and to the database itself. You may recall from our other article on how to plan SQL Server data files, that we talked about the need to separate your data and log files. That’s easy to do if you create the database from scratch, but what if you inherit it? In this case, you’ll need to move the files while they’re on the production server.
There are three ways to accomplish this task, but we’re only going to show you two of them. That’s because the third way is just pointless and wasteful so, you’d never actually do it. We’ll mention it anyway, just so you know it’s a method that will achieve your goal (regardless of how ridiculous).
Following are the three methods we’re going to talk about, listed in order of preference:
- Take database offline
- Detach database
- Backup/Restore with move
Method 1: Take Database Offline
This is the simplest, most flexible, and least invasive method. The steps needed to complete this task are easy and don’t necessarily have to be performed in the order we present them.
What is “Inside Microsoft Teams”?
“Inside Microsoft Teams” is a webcast series, now in Season 4 for IT pros hosted by Microsoft Product Manager, Stephen Rose. Stephen & his guests comprised of customers, partners, and real-world experts share best practices of planning, deploying, adopting, managing, and securing Teams. You can watch any episode at your convenience, find resources, blogs, reviews of accessories certified for Teams, bonus clips, and information regarding upcoming live broadcasts. Our next episode, “Polaris Inc., and Microsoft Teams- Reinventing how we work and play” will be airing on Oct. 28th from 10-11am PST.
For all of these examples we’ll assume the database name is Mydatabase. Let’s assume you want to move the log file to drive D:\.
- Open a query window in SQL Server Management Studio (SSMS) and type the following code:
ALTER DATABASE Mydatabase
MODIFY FILE (NAME = 'MydatabaseLog1', FILENAME = 'D:\MydatabaseLog1.ldf')
This command is easy. You’re telling SQL Server to modify the log file by its virtual name (“MydatabaseLog1”) and saying you want it mapped to the D:\MydatabaseLog1.ldf physical file. For more info on the basics of this command, see our other article How to manage SQL Server database files. To get the logical file name, connect to the database you’re interested in using SSMS and type ‘sp_helpfile’. The column you’re looking for is ‘Name’.
- In the same query window you can type the following code and highlight it to run:
ALTER DATABASE Mydatabase
This query takes a bit of explanation. You really need to switch to the master database (with “USE master”) because once your command finishes, it’s going to try to set the connection back to Mydatabase, but won’t be able to since you just took it offline. SQL Server will then switch you to the master database anyway. You’ll get a message similar to this if you fail to switch to master first: Failed to restart the current database. The current database is switched to master.
Setting the database offline doesn’t harm it in any way. It merely keeps any users from being able to connect to it.
We want to make one more comment about this command. In order to set a database offline, you must have exclusive access. That means that all other users have to be out of the database. This can be an arduous task and you could actually hurt your business if you do it wrong, so be careful how you go about getting everyone out of the database. For more on this topic see our article on how to gain exclusive access to a SQL Server database.
- Go to Windows and physically move the .ldf file to the new location you specified in #1 above (you can drag-and-drop or cut and paste the file to the destination).
- Go back to your SSMS query window and type the following command:
ALTER DATABASE Mydatabase
Clearly, this statement opens the database up for user connections again. For some reason, this method is oftentimes overlooked. The most common methods for doing this operation are either the detach method, which we’re going to show you in the 2nd part of this article, or this method of altering the database, but choosing to restart the service, instead of taking the database offline. We don’t know why restarting the service is chosen over this method, but it’s very invasive because if you’ve got more than one database on the server, you’re interrupting the work of all of them just to move a single log file. So stick with this method for moving database files. It’s quick, it’s easy, and it’s minimally invasive.
OK, so that’s it… four easy steps are all it takes. So why do we say that this is the most flexible method? Well because it is. Remember when we said above that you don’t necessarily have to perform these tasks in the exact order we wrote them? That’s because the changes don’t actually take effect until you bring the database back online. So you could actually take the database offline first (step 2), then physically move the file (step 3), then map it to the new location (step 1), then bring it back online (step 4). You could even do step 1 and then wait several days before doing the rest of the steps. As long as nothing takes the database offline during that time you’ll be fine. The only thing you really can’t do is move the file (step 3) while the database is online. This is because SQL Server maintains an exclusive lock on the files it’s using and you’ll get an error in Windows if you try to copy or delete them.