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.
