#MSSQL #DBA_TASK Move a database in SQL Server

MoveDB.sql

/******************************************************************************
	Task: Move a DB to another location
	Resources:  https://msdn.microsoft.com/en-us/library/ms345483.aspx
*******************************************************************************/

Use master;
GO

/** -------
	Step 1
**/ -------

--Get current directory of data/log files
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'DatabaseName')
GO

/** -------
	Step 2
**/ -------

--Take database offline
--'WITH ROLLBACK IMMEDIATE' Tells SQL Server to cancel any pending transactions and to rollback immediately, 
--in order to take the DB offline.  Without this termination clause, the SET OFFLINE will wait until all tasks are completed. 
ALTER DATABASE DatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

/** -------
	Step 3
**/ -------

--Point Data/Log files to the new location
ALTER DATABASE DatabaseName
MODIFY FILE (NAME= [date_file], FILENAME = 'path\to\point\to\data.mdf');
ALTER DATABASE DatabaseName
MODIFY FILE (NAME= [log_file], FILENAME = 'path\to\point\to\log.ldf');

/** -------
	Step 4
**/ -------

--After physical files have been moved, put database back online
ALTER DATABASE DatabaseName SET ONLINE;


-- **** Re-run Step 1- ensure currentlocation points to new location ****