How to migrate SQL 2005 system database

This article mainly explains "how to migrate SQL 2005 system database", the explanation content in the article is simple and clear, easy to learn and understand, please follow the idea of Xiaobian slowly in-depth, together to study and learn "how to migrate SQL 2005 system database"!

Mobile Master

The master database records all system-level information about SQL Server systems. This includes instance-wide metadata (such as login accounts), endpoints, linked servers, and system configuration settings. The master database also keeps track of whether all other databases exist and the locations of those database files. In addition, master also records SQL Server initialization information. Therefore, SQL Server cannot start if the master database is unavailable. In SQL Server 2005, system objects are no longer stored in the master database, but in the Resource database.

1. Boot to single user mode

NET START MSSQLSERVER /f /T3608

Modify startup parameters (Configuration Manager-Advanced Tab-Startup Parameters)

-dC:\new\master.mdf;

-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;

-lC:\new\mastlog.ldf;

-eC:\new\ERRORLOG

2. Mobile master database

ALTER DATABASE master MODIFY FILE( NAME = master , FILENAME = 'C:\new\master.mdf')

ALTER DATABASE master MODIFY FILE( NAME = mastlog , FILENAME = 'C:\new\masterlog.ldf')

3. mobile resource database

ALTER DATABASE systemresource MODIFY FILE (NAME=data, FILENAME='C:\new\mssqlsystemresource.mdf')

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME='C:\new\mssqlsystemresource.ldf')

4. stop the SQL Server

5. Move database files to destination

6. start SQL Server

7. verification

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

Mobile model database

The model database is used as a template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist in the SQL Server system.

1. movement path

ALTER DATABASE model MODIFY FILE( NAME = modeldev , FILENAME = 'C:\new\model.mdf')

ALTER DATABASE model MODIFY FILE( NAME = modellog , FILENAME = 'C:\new\modellog.ldf')

Move msdb database

msdb database is used by SQL Server Agent to schedule alerts and jobs

1. movement path

ALTER DATABASE msdb MODIFY FILE( NAME = msdbdata , FILENAME = 'C:\new\msdbdata.mdf')

ALTER DATABASE msdb MODIFY FILE( NAME = msdblog , FILENAME = 'C:\new\msdblog.ldf')

Mobile tempdb

Because tempdb is recreated each time the MS SQL SERVER service is started, there is no need to physically move data and log files. These files will be created when the service is restarted in step 3. After the service is restarted, tempdb continues to function in its current location.

1. determine a current location

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

GO

2. Change the location of each file using ALTER DATABASE

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = c:\new\tempdb.mdf');

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'c:\new\templog.ldf');

3. Stop and restart SQL Server.

4. Verify file changes.

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

Thank you for reading, the above is "how to migrate SQL 2005 system database" content, after learning this article, I believe we have a deeper understanding of how to migrate SQL 2005 system database, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!