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 WHERE database_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 WHERE database_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 WHERE database_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 WHERE database_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