How to migrate system databases in SQL Server R2

For different scenarios, take different steps.

In the case of non-master and resource system databases, there are two different cases.

1. Non-master and resource system databases are normal.

Here, for example, migrating msdb, migrating msdb from D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ to D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\ The mssql\data\system\ directory.

First we check the path of the current msdb

?

1 2 3

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHEREdatabase_id = DB_ID(N‘msdb‘);<br>----------------------------------------------------------------

Name Currentlocation State_desc
Msdbdata D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf ONLINE
Msdblog D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf ONLINE

Then modify the path of msdb in the database to point to the new path

?

1 2 3 4

alter database msdb modify file ( name = ' msdbdata ' ,filename= " D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\system\ Msdbdata.mdf ' ) alter database msdb modify file ( name = ' Msdblog ' ,filename= ' D:\SQL2K8 _data\mssql10_50.mssqlserver\mssql\data\system\msdblog.ldf '

Step three: Stop the SQL Server service

Fourth step: Copy the msdb corresponding data files and log files to the new path from the physical level

Fifth step: Start the SQL Server service

Sixth step: Determine the migration results

?

1 2 3

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHEREdatabase_id = DB_ID(N‘msdb‘);<br>------------------------------------------------------------

Name Currentlocation State_desc
Msdbdata D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\system\MSDBData.mdf ONLINE
Msdblog D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\system\MSDBLog.ldf ONLINE

2. If a system failure results in the need to migrate the system database. So now we need to use another method

Now take the migration model database as an example

First: If SQL Server is running, first turn the service off

Second: Start SQL Server into master-only mode

If it is the default instance, execute the net start mssqlserver/f at the command line/t3608

If it is a named instance, execute the net start mssql$instancename/f at the command line/t3608

Third: Use SSMs or sqlcmd to connect to SQL Server and execute the following statement

?

1 2 3 4

alter database model modify file ( name = ' Modeldev ' ,filename= " D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\system\ Model.mdf ' ) alter database model modify file ( name = ' Modellog ' ,filename= ' D:\ Sql2k8_data\mssql10_50.mssqlserver\mssql\data\system\modellog.ldf '

IV: Exit the current SSMS or sqlcmd command

V: Stop the current SQL Server service

Sixth: Copy the corresponding data and log files from the model database to the new path

Seventh: Start the SQL Server service

Eighth: Check for changes

?

1 2 3

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHEREdatabase_id = DB_ID(N‘model‘);<br>-------------------------------------------------------

Name Currentlocation State_desc
Modeldev D:\sql2k8_data\mssql10_50.mssqlserver\mssql\data\system\model.mdf ONLINE
Modellog D:\sql2k8_data\mssql10_50.mssqlserver\mssql\data\system\modellog.ldf ONLINE

　　

The migration methods for master and resource databases are as follows:

Migrating to the d:\sql2k8_data\mssql10_50.mssqlserver\mssql\data\system\ path

First step: Open SQL Server Configuration Manager using Sqlservermanager10.msc

Step Two: Modify the startup parameters in the Advanced Options page of Configuration Manager and modify the corresponding path to the new path

-dd:\sql2k8_data\mssql10_50.mssqlserver\mssql\data\system\master.mdf;-ed:\sql2k8_data\mssql10_50.mssqlserver\ Mssql\log\errorlog;-ld:\sql2k8_data\mssql10_50.mssqlserver\mssql\data\system\mastlog.ldf

Step three: Stop the SQL Server service and migrate the data files and log files for the master and resource databases to the new path

Where the data files and log files of the resource database are located under the/data/binn path

Fourth step: Start the SQL Server service to master-only mode using the/F and/t3608

Fifth Step: Modify the path of the data files and log files of the resource database to the new directory

?

1 2 3 4

alter database mssqlsystemresource MODIFY file ( name =data, filename= "D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\system\ Mssqlsystemresource.mdf ' alter database mssqlsystemresource modify file ( name =log, FILENAME = ' D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\system\ Mssqlsystemresource.ldf '

Sixth step: Normal SQL Server service enabled

Seventh Step: Check the file path of master

?

1 2 3

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHEREdatabase_id = DB_ID(N‘master‘);<br>---------------------------------------------------------

Name Currentlocation State_desc
Masterd:\sql2k8_data\mssql10_50.mssqlserver\mssql\data\system\master.mdfonline
Mastlogd:\sql2k8_data\mssql10_50.mssqlserver\mssql\data\system\mastlog.ldfonline