Recovering the database system on a new server

Example: Recovering a database system on a new server
Here are the reference steps. Note that instructions that run under the command line are case-sensitive.
1. Verify that the SQL Server version of the standby server is the same as the original server.
Because we need to restore the system database, we need to ensure that the master and msdb we recover are consistent with the resource database of the standby machine. Otherwise, SQL Server will not work correctly. The so-called version is consistent, referring to the "SELECT @ @version" The number returned must be exactly the same.

2. In the command-line window of the standby server, start the SQL Server service in single-user mode with instructions.
NET START mssqlserver/m

If the command executes successfully, the following information should be returned:

The SQL Server (DR) service is starting.
The SQL Server (DR) service was started successfully.

3. In the command-line window, use the SQLCMD command-line tool to connect to SQL Server.
SQLCMD-E-S sql2005pc

If the connection is successfully established, the following information should be returned.

1>

4. First restore the master database.
4.1 In the connection to SQLCMD, run the following recovery statement (the fake device file is ' C:\lab\master.bak ').
Restore Database Master from disk = ' C:\lab\master.bak '
Go

It should return information similar to the following:

Processed pages for database ' master ', file ' master ' on file 1.
Processed 4 pages for database ' master ', file ' Mastlog ' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating the this process.

The SQL Server service stopped automatically.

4.2 Restarting SQL Server fails because the path to the other databases documented in the recovered master database is inconsistent with the current path. Must be started with trace flag 3608来.
net start mssqlserver/f/m/t3608

If normal, the following information should be returned.
The SQL Server (DR) service is starting.
The SQL Server (DR) service was started successfully.

4.3 Use sqlcmd connection to modify the file path of the other database to the existing correct path (' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\ ').
In the command-line window, make a connection again using SQLCMD.

SQLCMD-E-S sql2005pc

Use the following statement to modify the file paths for each system database.

ALTER DATABASE Mssqlsystemresource modify file (name =data, filename= ' C:\Program Files\Microsoft SQL Server\mssql.4\ Mssql\data\mssqlsystemresource.mdf ');
Go

If normal, the following information should be returned.
The file "Data" has a been modified in the system catalog. The new path would be used the next time, the database is started.

ALTER DATABASE Mssqlsystemresource modify file (name =log, filename= ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql \data\mssqlsystemresource.ldf ');
Go

If normal, the following information should be returned.
The file "Log" has a been modified in the system catalog. The new path would be used the next time, the database is started.

ALTER DATABASE msdb modify file (name =msdbdata, filename= ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\ Msdbdata.mdf ');
Go

If normal, the following information should be returned.
The file "Msdbdata" has a been modified in the system catalog. The new path would be used the next time, the database is started.

ALTER DATABASE msdb modify file (name =msdblog, filename= ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\ Msdblog.ldf '); Go

If normal, the following information should be returned.
The file "Msdblog" has a been modified in the system catalog. The new path would be used the next time, the database is started.

ALTER DATABASE model modify file (name =modeldev, filename= ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\ Model.mdf ');
Go

If normal, the following information should be returned.
The file "Modeldev" has a been modified in the system catalog. The new path would be used the next time, the database is started.

ALTER DATABASE model modify file (name =modellog, filename= ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\ Modellog.ldf ');
Go

If normal, the following information should be returned.
The file "Modellog" has a been modified in the system catalog. The new path would be used the next time, the database is started.

ALTER DATABASE tempdb modify file (name =tempdev, filename= ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\ Tempdb.mdf ');
Go

If normal, the following information should be returned.
The file "Tempdev" has a been modified in the system catalog. The new path would be used the next time, the database is started.

ALTER DATABASE tempdb modify file (name =templog, filename= ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\ Templog.ldf ');
Go

If normal, the following information should be returned.
The file "Templog" has a been modified in the system catalog. The new path would be used the next time, the database is started.

After all modifications are complete, run the "exit" command to exit the SQLCMD connection.

4.4 Close SQL Server.
net stop MSSQLSERVER

If normal, the following information should be returned.
The SQL Server (DR) service is stopping.
The SQL Server (DR) service was stopped successfully.

4.5 Start SQL Server in normal mode.
net start MSSQLSERVER
At this point, SQL Server can start normally. However, it uses a system database other than master, which is the original standby server itself. We will replace them with backups on the production server.

5. Restore msdb.
Before you run the following command, close the SQL Server Agent service first. Then use the Restore command to restore the MDSB and point it to the new file path.
Restore DATABASE msdb from disk = ' C:\lab\msdb.bak '
With move ' msdbdata ' to ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\msdbdata.mdf ',
Move ' msdblog ' to ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\msdblog.ldf ', replace

If normal, the following information should be returned.
Processed pages for database ' msdb ', file ' msdbdata ' on file 1.
Processed 7 pages for database ' msdb ', file ' Msdblog ' on file 1.
RESTORE DATABASE successfully processed 607 pages in 0.841 seconds (5.907 mb/sec).

6. Restore model.
Restore Database model from disk = ' C:\lab\model.bak '
With move ' Modeldev ' to ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\model.mdf ',
Move ' modellog ' to ' C:\Program Files\Microsoft SQL Server\mssql.4\mssql\data\modellog.ldf ', replace

If normal, the following information should be returned.
Processed pages for database ' model ', file ' Modeldev ' on file 1.
Processed 3 pages for the database ' model ', file ' Modellog ' on file 1.
RESTORE DATABASE successfully processed 155 pages in 0.174 seconds (7.273 mb/sec).

7. Modify the server name
7.1 Run the following statement and you will find that the original server name is returned. This is because master came from that machine.
Select @ @servername

7.2 Run the following statement to modify the server name.

Sp_dropserver ' < original server name > '
Go
sp_addserver ' sql2005pc ', ' local '
Go

7.3 Restart the SQL service and run the following statement to see the name of the server that is now returned.
Select @ @servername
Go

After you have done this, all of the previous SQL Server configurations can be restored to the new server. Just the user database is in question because there are no files on the new server. You can then restore the user database in turn by using the recovery method described in the previous article.

Recovering the database system on a new server