HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Change drive letter (which contains system dbs)

Submitted by: @import:stackexchange-dba··
0
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?

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

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 DB

Context

StackExchange Database Administrators Q#42497, answer score: 3

Revisions (0)

No revisions yet.