master database restore new Problem __ Database

master database Restore New problem

--1. Version issues

Problem Description:

Restore the master database for the old server on the new server:
RESTORE DATABASE MASTER
From disk= ' c:/tmp/sqlsys20050516. BAK '
With file=1

The following error occurred:
Server: Message 3168, Level 16, State 1, line 1
Unable to restore device c:/tmp/sqlsys20050516. A system database backup on BAK because the server version (134218488) that created the database differs from the version of this server (134217922).
Server: Message 3013, Level 16, State 1, line 1
The RESTORE DATABASE operation terminated abnormally.

Problem Reason:
You must ensure that the new server version is exactly the same as the version of the master database to be restored, including the version of the patch

--2. System Database Directory problem

Problem Description:

The restore process is finished normally, and the system prompts:
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

Then start the MSSQLServer service, prompting you that the MSSQLServer service cannot be started on the local computer. The service did not return an error. This could be a Windows internal error or a service internal error.

Problem Analysis:
There are many possibilities that cannot be started, because the prompt for the restore is successful, so there is nothing wrong with estimating the database backup, and you should start by analyzing the problem from the startup process. The following step-by-step validation guesses and find the cause of the problem

--A. Verifying the correctness of master backup files

Restore the master backup file to normal data as a result:
RESTORE DATABASE A
From disk= ' c:/tmp/sqlsys20050516. BAK '
With File=1,
Move ' master ' to ' C:/a.mdf ',
Move ' mastlog ' to ' c:/a.ldf ',
REPLACE
Go

Use a
Go

EXEC sp_msforeachtable ' select * from? '
Go

DROP DATABASE A

--B. Check startup information for SQL Server service

Execute at a command prompt:
Sqlservr-c-M

The error messages that are associated with the inability to start the SQL Server service are in the received startup information:
2005-05-26 14:51:47.11 SPID5 Start the database "model".
2005-05-26 14:51:47.11 SPID5 udopen: Operating system error occurred while creating/opening physical device C:/Program Files/microsoft SQL server/mssql$erp/data/model.mdf Error 3 (the system cannot find the specified path.) )。
2005-05-26 14:51:47.16 spid5 FCB::Open failed: Failed to open device VDN C:/Program SQL files/microsoft for virtual device number (SERVER/MSSQL$ERP/D) 1 Ata/model.mdf.

You can see that starting with the database installation path of the old server to open the database, and the new server's database installation path is different from the old server, this is the master restore after the failure to start SQL Server

--C. Analysis of Solution methods

Model database data file information stored in the master database system tables sysdatabases and sysdevices, only need to find ways to modify the directory information in these two tables to meet the new server directory requirements.

--D. Resolve the example

-- ===========================================================
--1. Restore the backup of master database to normal database
-- ===========================================================
RESTORE DATABASE A
From disk= ' c:/tmp/sqlsys20050516. BAK '
With File=1,
Move ' master ' to ' C:/a.mdf ',
Move ' mastlog ' to ' c:/a.ldf ',
REPLACE
Go

-- ===========================================================
--2. Modify the directory of the system databases for the sysdatabases and sysdevices tables directory for the new server
--In My Computer, SQL database directory (d:/program files/microsoft SQL server/mssql/data--If necessary, other system databases make similar modifications (before modifying, you can query the values in both tables to determine how to modify )
-- ===========================================================
EXEC sp_configure ' allow updates ', 1
Reconfigure with OVERRIDE
Go

UPDATE a.dbo.sysdatabases SET
Filename= ' D:/program files/microsoft SQL server/mssql/data/model.mdf '
WHERE name= ' model '

UPDATE a.dbo.sysdevices SET
Phyname= ' D:/program files/microsoft SQL server/mssql/data/modellog.ldf '
WHERE name= ' Modellog '
Go

EXEC sp_configure ' allow updates ', 0
Reconfigure with OVERRIDE
Go

-- ===========================================================
--3. A modified Database
-- ===========================================================
BACKUP DATABASE A
To Disk= ' c:/tmp/sqlsys20050516. BAK '
With INIT
Go

-- ===========================================================
--4. On the new server, start SQL in Single-user mode, and then execute a recovery statement similar to the following to restore the master database
-- ===========================================================
RESTORE DATABASE MASTER
From disk= ' c:/tmp/sqlsys20050516. BAK '
With file=1

CSDN Community Post Address