patternsqlMinor
Change drive letter (which contains system dbs)
Viewed 0 times
dbswhichsystemlettercontainsdrivechange
Problem
Is it possible to safely change the drive letter for a volume which holds only system databases?
What precautions should be taken and how should it be done? I know I can just go to computer management > storage and change drive letter but can have negative consequences on SQL Server operation?
What precautions should be taken and how should it be done? I know I can just go to computer management > storage and change drive letter but can have negative consequences on SQL Server operation?
Solution
Thomas Pointed excellent article.
Below is the summarized version of what I follow when performing the move :
-
Change the System dbs file location ( except Master Db):
-
Move all the .mdf and .ldf files to the new location
-
Detach Model, MSDB and Tempdb
-
follow procedure described in Moving the master Database Procedure
-
Stop SQL Server instance
-
Move Model, MSDB, Tempdb and Master data and log files to the new location
-
Restart the instance of SQL Server
-
Make sure to enable service broker if DB Mail is already configured.
-
Change the SQL Server Agent log path as well as the database default location path.
Below is the summarized version of what I follow when performing the move :
-
Change the System dbs file location ( except Master Db):
ALTER DATABASE Model MODIFY FILE ( NAME = modeldev, FILENAME = 'Drive Letter:\Path\model.mdf' )
ALTER DATABASE Model MODIFY FILE ( NAME = modellog , FILENAME = 'Drive Letter:\Path\modellog.ldf' )
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = 'Drive Letter:\Path\MSDBData.mdf' )
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'Drive Letter:\Path\MSDBLog.ldf' )
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev , FILENAME = 'Drive Letter:\PathL\tempdb.mdf' )
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog , FILENAME = 'Drive Letter:\PathR\emplog.ldf' )-
Move all the .mdf and .ldf files to the new location
-
Detach Model, MSDB and Tempdb
SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''
+ Name + ''''FROM sys.sysdatabases
WHERE dbid in (2,3,4) -- Only system dbs except MASTER DB-
follow procedure described in Moving the master Database Procedure
-
Stop SQL Server instance
-
Move Model, MSDB, Tempdb and Master data and log files to the new location
-
Restart the instance of SQL Server
-
Make sure to enable service broker if DB Mail is already configured.
-
Change the SQL Server Agent log path as well as the database default location path.
Code Snippets
ALTER DATABASE Model MODIFY FILE ( NAME = modeldev, FILENAME = 'Drive Letter:\Path\model.mdf' )
ALTER DATABASE Model MODIFY FILE ( NAME = modellog , FILENAME = 'Drive Letter:\Path\modellog.ldf' )
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = 'Drive Letter:\Path\MSDBData.mdf' )
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'Drive Letter:\Path\MSDBLog.ldf' )
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev , FILENAME = 'Drive Letter:\PathL\tempdb.mdf' )
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog , FILENAME = 'Drive Letter:\PathR\emplog.ldf' )SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''
+ Name + ''''FROM sys.sysdatabases
WHERE dbid in (2,3,4) -- Only system dbs except MASTER DBContext
StackExchange Database Administrators Q#42497, answer score: 3
Revisions (0)
No revisions yet.