Move SQL Database from one drive to another

This is a common task most of DBA’s perform regularly. You face this situation when your data file drive is full and windows team cannot add disk space or you cannot clear any space in drive.

Task:

We have data file located in C:\SQL_2K14\M\MSSQL_DATA\Audit.mdf

Above data file should be moved to G:\MSSQL_DATA

This task takes database offline; make sure you have all the appropriate approvals needed for this Change. (This task needs change ticket)

Step 1: Check the current location of database files.

USE master

GO

SELECT name AS LogicalFileName, physical_name AS FileLocation

, state_desc AS Status

FROM sys.master_files

WHERE database_id = DB_ID('Audit');

Step 2: Make database offline.

USE master

GO

ALTER DATABASE Audit SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

*** All incomplete transactions will be rolled back and any other connections to the Audit database will be immediately disconnected. ***

Step 3: Move the database file from C drive to G drive physically.

Step 4: Modify the logical name and path of data file (new path).

USE master

GO

ALTER DATABASE [Audit]

MODIFY FILE

( NAME = [Audit], -- Logical name you can find in step 1

FILENAME = 'G:\MSSQL_DATA\Audit.mdf'); -- New file path

To Move log files: Apply Step 3 & 4 for moving log files too.

Step 5: Bring database online.

USE master

GO

ALTER DATABASE Audit SET ONLINE;

Step 6: Perform consistency checks (CheckDB).