Moving SQL Server System Databases to New Drives

It may become necessary to move an already installed SQL Server from one file storage location to another. It is necessary to move the system and other databases. In this case, we are moving the databases from the M:\ drive LUN to three drives, separated by function. This equates to:

• M:\sqlserver\backup to B:\
• M:\sqlserver\data to D:\
• M:\sqlserver\log to L:\

While this is entirely possible, it is necessary to follow a few steps below to cleanly move the SQL Server instance to have storage in a different location. This example uses SQL Server 2008R2, so be sure to take note of the version when clicking through the registry.

• Start the SQL Server Configuration Manager located in the programs section , under the version of SQL Server.
• Shutdown SQL Server Instance.
• In Configuration Tools for SQL Server Instance, put this in for startup parameters:

-dD:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;

-eD:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;

-lL:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

• In the registry:

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer

Set default paths to:

B:\, L:\, D:\

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Replication

Set Working Directory to:

D:\MSSQL10_50.MSSQLSERVER\MSSQL\repldata

o Change default location in registry for Data Root:

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/100/MSSQL10_50.MSSQLSERVER/Setup/

Set SQLDataRoot to:

D:\MSSQL10_50.MSSQLSERVER\MSSQL

Set FullTextDefaultPath to:

D:\MSSQL10_50.MSSQLSERVER\MSSQL\FTData

• Change default location for SQL Agent error file:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent/

Set ErrorLogFile:

M:\sqlserver\data\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT

To

D:\ MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT

• Update Dump Directory to :

D:\MSSQL10_50.MSSQLSERVER\MSSQL

• Check all Directories in the SQL Configuration manager. As an example, Dump Directory should be set to: D:\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\

• Start upSQL Server Instance
• Move Other System Databases:

USE master;

GO

--test change startup script in SQL Config MGR to point to new database/location.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

--MOVE SYSTEM DBs

--MOVE MODEL

USE master;

GO

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'model');

ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf' )

ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' )

USE master;

GO

--MOVE MSDB

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'msdb');

ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf' )

ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog , FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf' )

--MOVE TEMPDB

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'D:\templog.ldf');

GO

• Restart SQL Server Instance!
• Check locations

--Check

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'model');

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'msdb');

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

• Update Backups/Maintenance Jobs…they all point to the old location.
• Remaining Databases, such as the Report Server databases, can be detached from the old location, copied to new location and attached.