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