How to Move Database Files in SQL Server: Part 1

Published: Sep 16, 2010

SHARE ARTICLE

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:

  1. Take database offline
  2. Detach database
  3. 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.

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:\.

  1. 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’.

  1. In the same query window you can type the following code and highlight it to run:

USE master
Go
ALTER DATABASE Mydatabase
SET offline

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.

  1. 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).
  2. Go back to your SSMS query window and type the following command:

USE master
Go
ALTER DATABASE Mydatabase
SET online

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.

SHARE ARTICLE