Recently someone ask this question to me – How would you move database files to new location with no downtime. Based on my knowledge, the downtime is needed as we need physical movement of the database files and they can’t be moved unless the handle is released by a process, which is sqlservr.exe in our case. So I provided various options to them and decided to choose the best one. Here is the database create script, in case you want to follow along the blog.

CREATE DATABASE [SQLServerHelp]
 ON  PRIMARY 
( NAME = N'SQLServerHelp', FILENAME = N'C:\OldLocation\SQLServerHelp.mdf' ) LOG ON 
( NAME = N'SQLServerHelp_log', FILENAME = N'C:\OldLocation\SQLServerHelp_log.ldf')
GO
 

1. Detach/Attach

This is straight forward approach where we detach the database, move physical files to new location and attach database from there. When we detach, the database would no longer be available. Here are the steps:

--Step 1) detach the database (DOWNTIME STARTS)
USE master
go
sp_detach_db 'SQLServerHelp'
go


-- Step 2)- move files physically to new location
EXEC xp_cmdshell 'COPY “C:\OldLocation\SQLServerHelp.mdf” “C:\NewLocation\SQLServerHelp.mdf”'
GO
EXEC xp_cmdshell 'COPY “C:\OldLocation\SQLServerHelp_log.ldf” “C:\NewLocation\SQLServerHelp_log.ldf”'
GO

-- If xp_cmdshell is not enabled or you can't enable then do operating system copy and paste using Ctrl+C and Ctrl+V


-- Step 3) attach the database (DOWNTIME COMPLETES)
CREATE DATABASE SQLServerHelp 
ON (FILENAME = 'C:\NewLocation\SQLServerHelp.mdf'), 
   (FILENAME = 'C:\NewLocation\SQLServerHelp_log.ldf') 
FOR ATTACH; 

Downtime: Starts at Detach and ends at attach. if it’s a big database then copying them is the actual unavailability of the database.

2. Alter Database

This is similar to detach/attach method but instead of detaching, we take database offline. Then we use ALTER DATABASE command to modify location in system catalog. Before you attempt to use this trick, save the output of below as it would be needed in step 3 

use <database_name>
go
sp_helpfile
go

Here are the steps

--Step 1) set database offline (DOWNTIME STARTS)
 
ALTER DATABASE [SQLServerHelp]
SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO

-- Step 2) move files physically to new location
EXEC xp_cmdshell 'COPY “C:\OldLocation\SQLServerHelp_log.ldf” “C:\NewLocation\SQLServerHelp_log.ldf”'
GO
EXEC xp_cmdshell 'COPY “C:\OldLocation\SQLServerHelp.mdf” “C:\NewLocation\SQLServerHelp.mdf”'
GO
-- If xp_cmdshell is not enabled or you can't enable then do operating system copy and paste using Ctrl+C and Ctrl+V

-- Step 3)- alter database to reflect the new location
-- select * from sys.sysaltfiles where dbid = db_id('SQLServerHelp') can be used to get logical name used below

ALTER DATABASE [SQLServerHelp] MODIFY FILE (
    NAME = 'SQLServerHelp' ,FILENAME = 'C:\NewLocation\SQLServerHelp.mdf'
    )
GO
ALTER DATABASE [SQLServerHelp] MODIFY FILE (
    NAME = 'SQLServerHelp_log' ,FILENAME = 'C:\NewLocation\SQLServerHelp_log.ldf'
    )
GO

-- Step 4) set database online (DOWNTIME COMPLETES)
ALTER DATABASE [SQLServerHelp]
SET ONLINE
GO

Downtime: Starts at offline and ends at online. if it’s a big database then copying them is the actual unavailability of the database.

3. Backup/Restore

This is little untraditional approach but you can notice that If the database size is big then above approaches (1 & 2) would need more downtime as we need to move all files to new location. This approach of backup/restore would help in files movement with less downtime. One big disadvantage of this approach is that we need additional disk space than earlier approaches. In this approach we need to have a full backup of the database which needs an additional space. Also note that we are going to have two copies of all data and log files as we don’t have choice to do Cut+Paste. Here comes the compromise between business downtime vs. storage requirement. Here are the steps. We need database to be in full recovery model because we are going to use log backup capability.

--Step 1) take a full backup of the database 
USE master
go
BACKUP DATABASE [SQLServerHelp] TO  
DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\SQLServerHelp.bak' 
WITH FORMAT, INIT
GO

-- Step 2)- Restore backup as different database with files at new location with norecovery
USE [master]
RESTORE DATABASE [SQLServerHelp_New] FROM  
DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\SQLServerHelp.bak' 
WITH NORECOVERY,
MOVE N'SQLServerHelp' TO N'C:\NewLocation\SQLServerHelp.mdf',  
MOVE N'SQLServerHelp_log' TO N'C:\NewLocation\SQLServerHelp_log.ldf' 
GO


-- Step 3) Take log backup with NORECOVERY (DOWNTIME STARTS)
BACKUP LOG [SQLServerHelp] 
TO  DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\SQLServerHelp_Tail.bak' 
WITH  NO_TRUNCATE , FORMAT, INIT,  
NORECOVERY 
GO

-- Step 4) Restore tail log backup with RECOVERY 
RESTORE LOG [SQLServerHelp_New] 
FROM  DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\SQLServerHelp_Tail.bak' 
WITH RECOVERY
GO

-- Step 5) Drop the old database and rename the new one (DOWNTIME ENDS)
Drop Database SQLServerHelp
go
sp_renamedb 'SQLServerHelp_New', 'SQLServerHelp'

Downtime: Starts with last log backup performed using “with norecovery” and end after rename of new database to original name.

Now you can choose option based on downtime and space availability.