Problem
One
task that you may need to do as a DBA is to move the system databases from one
location to another. The documentation that is found on the Microsoft site
is helpful, but the steps are more geared toward moving one database at a
time. In this tip we look at a streamlined process of moving all system
databases at the same time.
Solution
There
are few Microsoft KB articles that show you step by step on how to move the SQL
Server system databases from one location to another location. The steps
for moving SQL Server 2000 system databases is much more convoluted than moving
SQL Server 2005, 2008 or 2008R2 databases.
The
following KB articles show you step by step how to move the system databases,
but the process does not need to be as long as the details show.
Moving
all SQL Server 2005, SQL Server 2008 or SQL Server 2008R2 System
Databases
Once
you have reviewed the KB articles above, you can follow these steps to move all
system databases at once.
- Update the -d and -l startup parameters for SQL Server for the new
location of the master data and log file
- Issue ALTER DATABASE commands to change the file location for the model,
msdb and tempdb database files
- Stop SQL Server
- Move the MDF and LDF files to the new locations specified in steps 1 and 2
for the master, model and msdb databases
- Start SQL Server
- Delete the old tempdb files
In
addition to the master, model, msdb and tempdb databases SQL Server 2005
introduces the mssqlsystemresourcedatabase.
Microsoft recommends not moving this database, but if you do want to move this
database as well you will follow these steps. Note you cannot
move the
mssqlsystemresource database for SQL Server 2008 or SQL Server 2008R2.
- Update the -d and -l registry startup parameters for SQL Server for the
new location of the master data and log file
- Issue ALTER DATABASE commands to change the file location for the model,
msdb and tempdb database files
- Stop SQL Server
- Move the MDF and LDF files to the new locations specified in steps 1 and 2
for the master, model and msdb databases
- Put SQL Server in minimal configuration mode by adding these two startup
parameters -f and -T3608 and then start SQL Server
- Issue ALTER DATABASE commands for the mssqlsystemresource MDF and LDF
files using same path as the master database
- Move the MDF and LDF files to the location specified in step 6 for the
mssqlsystemresource database
- Stop SQL Server
- Remove the startup options added in step 5
- Start SQL Server
- Delete the old tempdb files
Moving
all SQL Server 2000 System Databases
Once
you have reviewed the KB articles above, you can follow these steps to move all
system databases at once.
- Update the -d and -l startup parameters for SQL Server for the new
location of the master data and log file
- Issue ALTER DATABASE commands to change the file location for the tempdb
database files
- Stop SQL Server
- Move the MDF and LDF files to the new location specified in steps 1 for
the master database
- Put SQL Server in single user mode by adding these three startup
parameters -c, -m and -T3608 and then start SQL Server
- Detach the msdb and model databases
- Move the MDF and LDF files to the new location for the model and msdb
databases
- Attach the model database from its new location
- Stop SQL Server
- Remove the startup options added in step 5
- Start SQL Server
- Attach the msdb database
- Delete the old tempdb files
Although
it still seems like a lot of steps this will cut down on the need to stop and
start SQL Server as much if you move one database at a time. As you can
see moving all of the databases for SQL Server 2005, 2008 and 2008R2 is much
easier than moving the databases for SQL Server 2000 especially if you do not
move the mssqlsystemresource database.
Next
Steps
- Before you move your system databases, make sure you understand the steps
as outlined in these documents.
- Even though one of these documents shows how to move the SQL Server 2005
model and msdb databases by using detach and attach the ALTER DATABASE option
is much simpler
转:Move all SQL Server system databases at one time
原文:http://www.cnblogs.com/weaver1/p/3535282.html