Have you ever had to move databases and their files? A database migration involves some downtime and mistakes made can certainly ruin your day. You don’t want to be in the middle of a migration and be uncertain about what to do.

Read on to learn how to move SQL Server database files and also see some demos.

Why Move Database Files

Why would we need / want to move database files? There are some scenarios where a migration is done.

Disk is out of space Sometimes when an admin is sleeping on the job full disks go unnoticed until it it too late. In these cases some databases can be moved to a different disk, volume, mount point, etc.

Moving to new storage New storage for the database comes in and we need to move some (or all) databases need to be moved.

Separate database files Following best practices to separate database files – data file (mdf), transaction log (ldf), tempdb, backups, trace files, etc. I wrote about this here and more specifically here under the section called “Disk Setup”.



Pre Move Actions

The database will be unavailable during this operation so we need to notify our end users. Consider the ramifications if an application is using the database – we might want to stop application services or take some other custom action during the move.

Plan ahead before starting the job. Know what you are going to do before doing it. If you can test your method against a lab or development database that will help too.

Methods to Move Database Files

Once we have the game plan we can start to implement it. Let’s look at some details.

Migration – General Ways to Move Database Files

There are a few methods we can employ to get this job done:

Detach Database / Move Files / Attach Database Set Database Offline / Move Files / Alter DATABASE MODIFY FILE / Set Online Backup / Restore Elsewhere

Here are some Microsoft KBs to help along the process.

Attach a Database – explains CREATE DATABASE FOR ATTACH sp_attach_db – deprecated! Please use the above method for attach Create Database – more on the FOR ATTACH clause Move User Databases – highlights ALTER DATABASE MODIFY FILE sp_detach_db – caveats about detach

Let’s look in depth at each way.

Method 1: Detach Database / Move Files / Create Database For Attach

We create a few databases to test it out.

USE master; GO /************************************** Create Sample Databases ***************************************/ --DB1 CREATE DATABASE [DB1] ON PRIMARY ( NAME = N'DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB1.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB1_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO --DB2 CREATE DATABASE [DB2] ON PRIMARY ( NAME = N'DB2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB2.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'DB2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB2_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO --DB3 CREATE DATABASE [DB3] ON PRIMARY ( NAME = N'DB3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB3.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'DB3_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB3_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO

Verify DB file info.

--Verify DB file info select * from DB1.sys.database_files; select * from DB2.sys.database_files; select * from DB3.sys.database_files;

It will look like this:

Now we do some preliminary things before moving files. We need to specify which databases we want to move. This is a very simplistic way to do it – hard coded in a temp table.

/************************************** Pre steps ***************************************/ --view databases you want to move SELECT * FROM sys.databases WHERE [name] IN('DB1','DB2','DB3'); --define databases to detach/attach SELECT [name] INTO #DBsToMove FROM sys.databases WHERE [name] IN('DB1','DB2','DB3'); --must gather the file info BEFORE detaching the database SELECT database_id, [type_desc], [name], physical_name INTO #DBfiles FROM sys.master_files WHERE database_id IN(DB_ID('DB1'),DB_ID('DB2'),DB_ID('DB3')); --check file configuration - might want to copy this elsewhere to keep records of our work SELECT * FROM #DBfiles;

Now that we have what we need we can start to detach the databases.

/***************************************************************************************** Method 1: Detach Database / Move Files / Create Database For Attach ******************************************************************************************/ --Detach the databases DECLARE @DBName varchar(500); DECLARE @sql NVARCHAR(MAX); DECLARE curDetachDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT [name] FROM #DBsToMove OPEN curDetachDBs FETCH NEXT FROM curDetachDBs INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN PRINT CHAR(13) + CHAR(10) PRINT '---------------------------------------------------------' PRINT '--' + @DBName PRINT '---------------------------------------------------------' SET @sql = 'ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' PRINT @sql EXEC sys.sp_executesql @sql SET @sql = 'EXEC sp_detach_db ' + @DBName + ', ''true'';' PRINT @sql EXEC sys.sp_executesql @sql FETCH NEXT FROM curDetachDBs INTO @DBName END CLOSE curDetachDBs DEALLOCATE curDetachDBs

Verify the changes. Notice that the DBs are totally gone. You won’t find them in the Object Explorer in SSMS or in sys tables.

--verify changes SELECT * FROM sys.databases WHERE name IN('DB1','DB2','DB3'); --DBs are gone now! that is what detach does and how it is different from setting offline

Now comes the time for moving the database files. You can use your favorite technique for moving files. I like to use robocopy in a .bat file but there are many others such as PowerShell, XCopy, or right click cut / paste.

Here is some robocopy action:

robocopy "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" "G:\MSSQL\Data" "DB1.mdf" /L robocopy "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" "G:\MSSQL\Data" "DB1_log.ldf" /L robocopy "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" "G:\MSSQL\Data" "DB2.mdf" /L robocopy "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" "G:\MSSQL\Data" "DB2_log.ldf" /L robocopy "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" "G:\MSSQL\Data" "DB3.mdf" /L robocopy "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" "G:\MSSQL\Data" "DB3_log.ldf" /L

The “/L” flag is how I first run things. It won’t actually copy anything but rather will list the files. It is a good pre-check before running a batch. Just remove the “/L” flag and run once verified.

Here is the first part of output from running a .bat file with robocopy statements:

Once the files are physically moved to the new location we can continue.

--Attach the databases DECLARE @targetMDF NVARCHAR(128); DECLARE @targetLDF NVARCHAR(128); DECLARE @DBName varchar(500); DECLARE @sql NVARCHAR(MAX); DECLARE @rowFile NVARCHAR(128); DECLARE @logFile NVARCHAR(128); DECLARE curAttachDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT name FROM #DBsToMove OPEN curAttachDBs FETCH NEXT FROM curAttachDBs INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @targetMDF = 'G:\MSSQL\Data'; SET @targetLDF = 'G:\MSSQL\Data'; PRINT CHAR(13) + CHAR(10) PRINT '---------------------------------------------------------' PRINT '--' + @DBName PRINT '---------------------------------------------------------' SELECT @targetMDF += '\' + [name] + '.mdf' FROM #DBfiles WHERE REPLACE([name],'_log','') = @DBName AND [type_desc] = 'ROWS'; SELECT @targetLDf += '\' + [name] + '.ldf' FROM #DBfiles WHERE REPLACE([name],'_log','') = @DBName AND [type_desc] = 'LOG'; --attach database SELECT @sql = 'CREATE DATABASE [' + @DBName + '] ON (FILENAME = ''' + @targetMDF + '''), (FILENAME = ''' + @targetLDF + ''') FOR ATTACH;' FROM #DBfiles; PRINT @sql; EXEC sys.sp_executesql @sql; SET @targetMDF = ''; SET @targetLDF = ''; FETCH NEXT FROM curAttachDBs INTO @DBName END CLOSE curAttachDBs DEALLOCATE curAttachDBs

Verify the new file locations.

--Verify DB file info select * from DB1.sys.database_files; select * from DB2.sys.database_files; select * from DB3.sys.database_files;

Our files used to be pointing to C: but are now on G: – the migration is complete.

Method 2: Set Database Offline / Move Files / Alter Database / Set Online

Continuing along, let’s move the 3 example databases we created and just moved back from the G: to C: drives. Here we start by defining our databases and setting a new path.

USE master; GO /************************************** Pre steps ***************************************/ --view databases you want to move SELECT * FROM sys.databases WHERE [name] IN('DB1','DB2','DB3'); --define databases to detach/attach SELECT [name] INTO #DBsToMove FROM sys.databases WHERE [name] IN('DB1','DB2','DB3'); --must gather the file info BEFORE detaching the database SELECT database_id, [type_desc], [name], physical_name INTO #DBfiles FROM sys.master_files WHERE database_id IN(DB_ID('DB1'),DB_ID('DB2'),DB_ID('DB3')); alter table #DBfiles add NewPath varchar(500); --load new paths DECLARE @targetMDF NVARCHAR(128); DECLARE @targetLDF NVARCHAR(128); SET @targetMDF = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\'; SET @targetLDF = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\'; --SET @targetMDF = 'G:\MSSQL\Data\'; --SET @targetLDF = 'G:\MSSQL\Data\'; update files set NewPath = case when [type_desc] = 'ROWS' then @targetMDF + [name] + '.mdf' when [type_desc] = 'LOG' then @targetLDF + [name] + '.ldf' end from #DBfiles files; --check file configuration - might want to copy this elsewhere to keep records of our work SELECT * FROM #DBfiles;

Here is the output from #DBFiles:

Now, instead of detaching like the previous example, let us simply take the databases offline.

/***************************************************************************************** Method 2: Set Database Offline / Move Files / Alter Database / Set Online ******************************************************************************************/ --set DBs offline DECLARE @DBName varchar(500); DECLARE @sql NVARCHAR(MAX); DECLARE curDetachDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT [name] FROM #DBsToMove OPEN curDetachDBs FETCH NEXT FROM curDetachDBs INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN PRINT CHAR(13) + CHAR(10) PRINT '---------------------------------------------------------' PRINT '--' + @DBName PRINT '---------------------------------------------------------' SET @sql = 'ALTER DATABASE [' + @DBName + '] SET OFFLINE;' PRINT @sql EXEC sys.sp_executesql @sql FETCH NEXT FROM curDetachDBs INTO @DBName END CLOSE curDetachDBs DEALLOCATE curDetachDBs --verify changes SELECT * FROM sys.databases WHERE name IN('DB1','DB2','DB3'); --DBs are still present - unlike with detach

The databases are still present but are offline. They are not able to be read or written to.

Subsequent to setting the databases offline move the data files to their new location.

Lastly we alter the databases to modify their files, point them to the new location, and then bring the databases back online.

--Alter DBs to modify files then bring online DECLARE @DBName varchar(500); DECLARE @sql NVARCHAR(MAX); DECLARE @rowFile NVARCHAR(128); DECLARE @logFile NVARCHAR(128); DECLARE @fileType varchar(4); DECLARE @DBID int; DECLARE @NewPath varchar(500); DECLARE @fileName varchar(128); DECLARE curAttachDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT database_id, [type_desc], [name], NewPath FROM #DBfiles OPEN curAttachDBs FETCH NEXT FROM curAttachDBs INTO @DBID, @fileType, @fileName, @NewPath WHILE @@FETCH_STATUS = 0 BEGIN SELECT @DBName = DB_NAME(@DBID); PRINT CHAR(13) + CHAR(10) PRINT '---------------------------------------------------------' PRINT '--' + @DBName PRINT '---------------------------------------------------------' select @DBID, @fileType, @fileName, @NewPath --change file locations SELECT @sql = 'ALTER DATABASE [' + @DBName + '] MODIFY FILE( NAME = ''' + @fileName + ''', FILENAME = ''' + @NewPath + ''')' FROM #DBfiles; PRINT @sql; EXEC sys.sp_executesql @sql; --set DB online SELECT @sql = 'ALTER DATABASE ' + @DBName + ' SET ONLINE'; print @sql; EXEC sys.sp_executesql @sql; FETCH NEXT FROM curAttachDBs INTO @DBID, @fileType, @fileName, @NewPath END CLOSE curAttachDBs DEALLOCATE curAttachDBs --Verify DB file info select database_id, DB_NAME(database_id) as 'DBName', [type_desc], [name], physical_name, state_desc from sys.master_files where DB_NAME(database_id) in('DB1','DB2','DB3');

Here is the output from the verification above. It shows the DB files in a new location and online.

Method 3: Backup / Detach and Delete / Restore Elsewhere

The backup will be like any other.

USE master; GO --Backup databases BACKUP DATABASE [DB1] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB1.bak'; GO BACKUP DATABASE [DB2] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB2.bak'; GO BACKUP DATABASE [DB3] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB3.bak'; GO

At this point we are free to detach the databases and dispose of the database files. It might be good to hold onto the files until after we have verified the restore worked. Since this is a contrived example let’s move on to the restore.

--Restore databases RESTORE DATABASE [DB1] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB1.bak' WITH REPLACE, FILE = 1, MOVE N'DB1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB1.mdf', MOVE N'DB1_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB1_log.ldf', NOUNLOAD, STATS = 5 GO RESTORE DATABASE [DB2] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB2.bak' WITH REPLACE, FILE = 1, MOVE N'DB2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB2.mdf', MOVE N'DB2_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB2_log.ldf', NOUNLOAD, STATS = 5 GO RESTORE DATABASE [DB3] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB3.bak' WITH REPLACE, FILE = 1, MOVE N'DB3' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB3.mdf', MOVE N'DB3_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB3_log.ldf', NOUNLOAD, STATS = 5 GO

Plug in the new path for the MOVE TO clause and the restore will put the files there.

Post Move Actions

After migrating some databases we want to be confident that all is well and the system is up and running. A few simple checks help put your stakeholders at ease.

It is good to do the following things after migrating database files:

Check sys.database_files to make sure they are pointed to the right place Run DBCC CHECKDB on the moved databases Moving database files can introduce concerns that a quick consistency check can alleviate

Final Thoughts

There are pros and cons to each of the methods presented above to move database files. Make sure that whatever technique you choose to employ that you first test it on a non-production server. This is not the point you want to encounter unknown surprises.

The safest method is probably #2 – setting offline, move files, alter modify file, set online. Detaching runs the risk of allowing the files to be deleted but is that is what you want then detach is the way. The backup method is probably best kept for the warm / cold standby but could work depending on the system usage and constraints.

Did you find this helpful? Please subscribe!