You might need to move system databases as part of a planned relocation or scheduled maintenance
Operation. If you move a system database and later rebuild the
MasterDatabase, you must move that
System database again because the Rebuild operation installs all system databases to their default
Location. The steps for moving
tempdb,
Model, and
msdbis slightly different than for moving the
MasterDatabase.
Note
in SQL Server, the
Mssqlsystemresource
Database can ' t be moved. If you move
the fies for this database, won ' t is able to restart your SQL Server service.
Here is the steps for moving a undamaged system database (other than the master database):
1.For each of the fie in the database to being moved, use the
ALTER DATABASECommand with the
MODIFY FILEoption to specify the new physical location.
2. stop the SQL Server instance.
3 physically move the fies.
4 restart the SQL Server instance.
5 verify The change by running the following query:
select name, physical_name as Currentlocation, State_desc
FROM Sys.master_files
where database_id = db_id (N ' <database_name> ');
If the system database needs to is moved because of a
Hardware FailureThe solution is a bit more
Problematic because you might not has access to the server to run the
ALTER DATABASECommand.
Follow these steps to move a damaged system database (other than the
MasterDatabase or the
Resource database):
1.Stop the instance of SQL Server if it has been started.
2.Start the instance of SQL Server in
Master-
onlyRecovery mode (by specifying
TRACEFLG 3608)
By entering one of the following commands at the command prompt:
--If The instance is the default instance:
NET START mssqlserver/f/t3608
--For a named instance:
NET START mssql$instancename/f/t3608
/ FIndicates the instance of SQL Server starts with minimal confiuration.
CHAPTER 4Special databases
143
3.For each of the fie in the database to being moved, use the
ALTER DATABASECommand with the
MODIFY FILEoption to specify the new physical location. You can use either ManagementStudio or the
SQLCMDUtility.
4.Exit Management Studio or the
SQLCMDUtility.
5.Stop the instance of SQL Server.
6.physically move the fie or fies to the new location.
7 restart The instance of SQL Server without TRACEFLG 3608. For example, run
NET start
MSSQLSERVER.
8 verify The change by running the following query:
select name, physical_name as Currentlocation, State_desc
FROM Sys.master_files
where database_id = db_id (N ' <database_name> ');
- Moving the master database
Full details on moving the
MasterDatabase can be found in
SQL Server Books Online,But the steps is
summarized here. The biggest difference between moving this database and moving other system
Databases is so you must go through the SQL Server confiuration Manager.
To move the
MasterDatabase, Followw these steps.
1.Open the SQL Server confiuration Manager. Right-click the desired instance of SQL Server,
Choose Properties, and then click the Startup Parameters tab.
2.Edit the Startup Parameters values to the new directory
Master
Database data and log fies and click Update. If you want, you can also move the SQL Server
Error log fies. The parameter value for the data fie must follow the
–dparameter, the value
For the log fie must follow the
–lparameter, and the value for the error log must follow the
–e
parameter, as shown here:
-de:\data\master.mdf
-le:\data\mastlog.ldf
-ee:\data\log\errorlog
3.Stop the instance of SQL Server and physically move the fies for the new location.
4.Restart the instance of SQL Server.
5.Verify the fie change for the
MasterDatabase by running the following query:
SELECT name, physical_name as Currentlocation, State_desc
From Sys.master_files
WHERE database_id = db_id (' master ');
144 Microsoft SQL Server Internals
Finally, if you is also changing your drive letters or folders, you need to verify and perhaps
The location of the SQL Server Agent error log fie and the confiured database default
Locations setting. The Database default locations setting is defied on Server Properties | Database
Settings.
SQL SERVER Moving SYSTEM DATABASES
