Moving Database Files, It Ain’t That Hard

From time to time, the database data files (like *.mdf and *.ndf), or transaction log files (like *.ldf) need to be moved or renamed, in some way or another. Sometimes, to a different drive or volume. Sometimes, to a different folder on the same drive. Sometimes, the files need to be renamed too. For some reason, people get a bit anxious at the thought of moving database files – both DBAs and other people in the business. I used to be the same. There really isn’t anything to be worried about, because it ain’t that hard.

Unfortunately for this sort of maintenance, you’ll require some down time on the database. In fact the easiest way to approach this, is to have a maintenance window, where you’re allowed to take time to copy files around.

There are two methods to moving database files: Detaching and attaching the database, and setting the database offline then online. I’ll take you through the basic steps to both of those methods the way I would approach it.

Detach and Attach Method

This method is generally the better one if you want to move databases to another SQL Server instance (although other methods are better for this type of move too). It isn’t as good for moving databases when they are staying on the same SQL Server instance. The problem I have with this method is that it sets some of the database level settings back to default, like DB Chaining. Some people prefer it though, so here it is:

use [master]
go

-- Step 1. Displays all files for this database
exec sp_helpdb [<databasename>]


-- Step 2. Detach the database so the files can be moved
exec sp_detach_db '<databasename>'

-- Step 3. Move the databases files to the new location and rename the files.

-- Step 4. Attach the database with the files in the new locations
-- Only need to specify the files that have moved AND the primary file
execute sp_attach_db 
	 @dbname = '<databasename>' --Database name
    ,@filename1 = 'D:\SQLData\<databasename>.mdf' --example of data file new location
    ,@filename2 = 'E:\SQLLogs\<databasename>_1.ldf' --example of log file new location

-- Step 5. Display all files for this database to confirm all the new files are in correct place
execute sp_helpdb <databasename>	

Now I’ll actually move one of my test databases, which for this I’m calling AttachDetachTest. It has one data file and one transaction log file. The first step I’d take is to check the details of the database by running sp_helpdb:

This has given me the current full file path of both the data and log files. Great, now I know where they are stored and I can go find them. Lets say I wanted to move these to a new sub folder called File_Move_Demo. Before detaching the database, I would prepare the attach command first, it’s just better to do it first. Once I’ve prepared the attach script, I can detach the database, then move it. For these small databases, I just use copy and paste.

The file is now in the new folder I’ve called File_Move_Demo. But I haven’t just moved it. I’ve actually copied it, to give me a bit of a rollback position. I kept the old file in the old location and renamed it with a prefix of “OLD_”. This means if for whatever reason, the attach command doesn’t work, or the file gets corrupted in the copy process, then I can easily rollback to the old file by renaming it back to it’s old name then attaching it again. I rename it so that to ensure that there is no chance that I accidentally attach the same old file, instead of the intended file in the new location.

Now the files in the new locations can be attached:

It’s attached and online. By running sp_helpdb again we can see the new file path of both files. This is also an easy way to validate that the database is online.

Lastly, now we’ve confirmed the database is happy in it’s new file location, we should clean up the “Old_” files.

This all worked very nicely, but my prefered method is taking it offline, moving the files, then setting it online again.

Offline and Online Method

This method is better when moving database files and still keeping the database on the same SQL Server instance. It keeps all the same database level settings, so you don’t have that awkward moment when you lose all your database settings and can’t remember database settings were enabled. The code I use is:

use [master]
go

-- Step 1. Step 1. Displays all files for this database
execute sp_helpdb <DBName>

--Step 2. Set the database to offline so the files can be moved
alter database <DBName> set offline;

--Step 3. Move the database files to the new location and rename the files. Use copy/paste

--Step 4. Tell SQL where the new files are
alter database <DBName>
modify file ( name = N'<Data_LogicalName>', filename = N'<FilePath>');
go
alter database <DBName>
modify file ( name = N'<Log_LogicalName>', filename = N'<FilePath>');
go

--Step 5. Set the database online
alter database <DBName> set online;

--Step 6. Display all files for this database to confirm all the new files are in correct place
execute sp_helpdb <DBName>	

For this demonstration, I’ll use a database called OfflineOnlineTest. It also has one data file and one transaction log file. And again, just like the Attach Detach method, I first check the details of the database by running sp_helpdb:

Again, I will move this to the File_Move_Demo sub folder. I have prepared the commands to alter the file locations and now ready to take the database offline. Next I set the database offline.

Then copy the files, using the same process I used in the attach/detach method. Then I alter the databases to set the new filename paths:

Once that is done. I bring it online again and check it again by running sp_helpdb:

An of course, lastly I clean up the “Old_” database files.

Multiple Files in a Database

If you have databases with many secondary data files, then you can script out these commands from a query, rather than manually typing up each one. i.e. this little query could generate all your alter database commands for their current location for when the database is offline:

select N'alter database [<DBName>] modify file (name = N''' + [name] + N''', filename = N''' + physical_name + N''');' as modify_file_command
from sys.database_files 
where [type_desc] = '{ROWS|LOG}' and data_space_id between x and xx

It ain’t that hard

See, it ain’t that hard. Just use these script templates to create your scripts before moving the databases and you should have no more to worry about when moving databases.