Move System Databases : Failure Recovery Procedure

Failure Recovery Procedure

---

If a file must be moved because of a hardware failure, follow these steps to relocate the file to a new location. This procedure applies to all system databases except the master and Resource databases.

Important
If the database cannot be started, that is it is in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file.

1. Stop the instance of SQL Server if it is started.
2. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.
   • For the default (MSSQLSERVER) instance, run the following command:
     NET START MSSQLSERVER /f /T3608
     

   • For a named instance, run the following command:
     NET START MSSQL$instancename /f /T3608
     

   For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.
3. For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.
   ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
   

   For more information about using the sqlcmd utility, see Use the sqlcmd Utility.
4. Exit the sqlcmd utility or SQL Server Management Studio.
5. Stop the instance of SQL Server. For example, run NET STOP MSSQLSERVER.
6. Move the file or files to the new location.
7. Restart the instance of SQL Server. For example, run NET START MSSQLSERVER.
8. Verify the file change by running the following query.
   SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'');