4

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?

  • 2
    Once you change the drive letter, your SQL instance will not be able to start. You have to give the new location to your system DBs and then start the SQL instance. – StanleyJohns May 18 '13 at 19:37
5

You should follow the procedure outlined in the MSDN article "Moving System Databases"

2

Thomas Pointed excellent article.

Below is the summarized version of what I follow when performing the move :

  1. 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' )
    
  2. Move all the .mdf and .ldf files to the new location

  3. 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
    
  4. follow procedure described in Moving the master Database Procedure

  5. Stop SQL Server instance

  6. Move Model, MSDB, Tempdb and Master data and log files to the new location

  7. Restart the instance of SQL Server

  8. Make sure to enable service broker if DB Mail is already configured.

  9. Change the SQL Server Agent log path as well as the database default location path.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.