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

USE master;

/**************************************

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 )

--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 )

--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 )

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;

--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;

/**************************************

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

/*****************************************************************************************

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

--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

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

--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;

--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;

USE master;

/**************************************

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

/*****************************************************************************************

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');

--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

USE master;

--Backup databases

BACKUP DATABASE [DB1] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB1.bak';

BACKUP DATABASE [DB2] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB2.bak';

BACKUP DATABASE [DB3] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB3.bak';

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

--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

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

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

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

2 thoughts on “Migration – How To Move SQL Server Database Files to a New Location”

Migrating Database Files – Curated SQL says:

March 1, 2018 at 8:01 am

[…] Jeff Mlakar gives us three methods for migrating database files from one location to another: […]

T-SQL Tuesday #104 - Code You Would Hate to Live Without – MlakarTechTalk says:

July 10, 2018 at 7:01 am

[…] we need to loop through all the user databases and take some action. For example – moving database files or migrating databases. The basic logic can be used to loop through a list of databases on a SQL instance and perform some […]