Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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
add comment

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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