sqlserver2005 Master and msdb database backup recovery process _mssql2005

Because the system database is particularly important for SQL Server, to ensure that the SQL Server system is functioning properly, we need to back up the system database in addition to the daily backup of the user's database, such as a full backup of master,model,msdb (tempdb without backup)

1. Restore master Database

If the system configuration is missing or the master is having problems, you can restore in Single-user mode, and the corrupted master database must be rebuilt if the following conditions occur:
The current backup of a. master database is not available.
B. There is a master database backup, but the backup cannot be restored because the Microsoft SQL Server instance cannot be started.
1. Regenerate master database:

Attention:

The Rebuildm.exe program has been revoked in SQL Server 2005. To regenerate the master database, use Setup.exe.

1, start/wait setup.exe/qn instancename=<instancename> reinstall=sql_engine REBUILDDATABASE=1 SAPWD=< Newstrongpassword>

Example: start/wait e: "setup.exe/qn instancename=mssqlserver reinstall=sql_engine rebuilddatabase=1 SAPWD=abc123@!@
Note: InstanceName: Specifies the instance name, and the default instance is represented by MSSQLServer
REINSTALL: Specify engine
SAPWD: Strong password
Setup.exe: Specify files in the root directory in Disc 1
The/qn switch cancels all setup dialog boxes and error messages. If you specify the/qn switch, all setup messages, including error messages, are written to the installer log file. For more information about log files, see How to view the SQL Server 2005 installation log files.
Specifies that the/qb switch will display the Basic Installer dialog box. An error message is also displayed.

2. Restore Master Backup (Note When restoring Master's backup: Must be in single user mode) a. Ways to enter Single-user mode:

' 1. Enter Sqlservr-c-f-m or enter SQLSERVR-M in command line mode
' Where:-C can shorten startup time, SQL Server does not start as Windows NT service '
'-F start SQL Server with minimal configuration
'-m single user mode start SQL Server '

2. System default does not set path, first into the cmd, into the "C:" program Files Microsoft SQL Server "MSSQL.1" MSSQL "Binn", Executive Sqlservr.exe–m

Open the SSMs tool, disconnect first, then create a new query, and perform the following named restore
Use master
Go
RESTORE DATABASE Master
From disk= ' C: "Master.bak '
Go
Start the Experiment ~ ~ ~
Master Regeneration
To simulate the master database being broken, we delete the master database (to stop the SQL Server service from being removed) on my computer, C: "Program FILES" MICROSOFT SQL SERVER mssql.1 "MSSQL" DATE Delete Master.mdf in
Regenerate Master
1. First enter in CMD
Start/wait setup.exe/qn instancename=<instancename> reinstall=sql_engine REBUILDDATABASE=1 SAPWD=< Newstrongpassword>
One of the SETUP. EXE for the boot CD path, I have here is E: "SETUP." exe/qn
I'm here for the default instance so the <INSTANCENAME> with MSSQLServer instead
The final <NewStrongPassword> for the password I set the password for abc123@!@, so use abc123@!@ instead of <NewStrongPassword>
If we don't return the error, we can go to my computer. C: "Program FILES" MICROSOFT SQL SERVER "MSSQL.1" date there's another master.mdf.
Next into the directory
Single user mode
Start the SSMs tool and create a new query
Enter command
Start Service (MSSQL SERVER)
Open SSMs
When you connect to the database, you can see that the data in the database is back.
The following is my command line, I copied it all down in Notepad, in order not to pass the attachment, I paste directly to the following

Copy Code code as follows:

Microsoft Windows [version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C: "Documents and Settings" Administrator>start/wait e: "setup.exe/qn instancename=mssqlserver Reinstall=sql_engine Rebuilddatabase=1 sapwd=abc123@!@
C: "Documents and Settings" ADMINISTRATOR&GT;CD C: "Program Files" Microsoft SQL Server mssql.1 "MSSQL" Binn
C: "Program Files" Microsoft SQL Server mssql.1 "MSSQL" binn>sqlservr.exe-m
2008-04-18 15:47:46.56 Server authentication mode is MIXED.
2008-04-18 15:47:46.59 server Microsoft SQL Server 2005-9.00.1399.06 (Intel X86)
OCT 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (build 3790:service Pack 1)
2008-04-18 15:47:46.59 Server (c) is Microsoft Corporation.
2008-04-18 15:47:46.59 Server All rights reserved.
2008-04-18 15:47:46.59 Server server process ID is 3984.
2008-04-18 15:47:46.59 Server Logging SQL Server messages in file ' C: program Files Microsoft SQL server mssql.1 ' MSSQL ' LOG "ERRORLOG '."
2008-04-18 15:47:46.59 Server This instance the SQL Server last reported using a process ID of 764 at 2008-4-18 15:46:40 (l ocal) 2008-4-18 7:46:40 (UTC). This is a informational message only; No user action is required.
2008-04-18 15:47:46.60 Server Registry Startup parameters:
2008-04-18 15:47:46.60 server-d C: "Program Files" Microsoft SQL Server "MSSQL.1" MSSQL "DATA" master.mdf
2008-04-18 15:47:46.60 server-e C: "Program Files" Microsoft SQL Server "MSSQL.1" MSSQL "LOG" ERRORLOG
2008-04-18 15:47:46.60 server-l C: "Program Files" Microsoft SQL Server "MSSQL.1" MSSQL "DATA" Mastlog.ldf
2008-04-18 15:47:46.62 Server Command line Startup Parameters:
2008-04-18 15:47:46.62 server-m2008-04-18 15:47:46.64 Servers SQL server is starting at normal priority base (=7). This is a informational message only. No user action is required.
2008-04-18 15:47:46.67 Server detected 1 CPUs. This is a informational message; No user action is required.
2008-04-18 15:47:46.78 Server Using dynamic lock allocation. Initial allocation of 2500 lock blocks and 5000 lock Owner blocks per node. This is a informational message only. No user action is required.
2008-04-18 15:47:46.84 Server attempting to initialize Microsoft distributed Transaction Coordinator (MS DTC). This is a informational message only. No user action is required.
2008-04-18 15:47:47.95 Server attempting to recover In-doubt distributed transactions involving Microsoft distributed TRANSAC tion Oordinator (MS DTC).
This is a informational message only. No user action is required.
2008-04-18 15:47:47.96 Server Database Mirroring transport is disabled the endpoint configuration.
2008-04-18 15:47:47.96 spid5s Warning ******************
2008-04-18 15:47:47.96 spid5s SQL Server started in Single-user mode. This is informational. No user action is required.
2008-04-18 15:47:47.99 spid5s starting up database ' master '.
2008-04-18 15:47:48.17 spid5s Recovery is writing a checkpoint in database ' master ' (1). This is a informational message only. No user action is required.
2008-04-18 15:47:48.28 spid5s SQL Trace ID 1 is started by login "sa".
2008-04-18 15:47:48.32 spid5s starting up Database Ssqlsystemresource '.
2008-04-18 15:47:48.76 spid5s Server name is ' VM01 '. This is a informational message only. No user action is required.
2008-04-18 15:47:48.78 spid8s starting up database ' model '.
2008-04-18 15:47:49.20 Server A self-generated certificate is successfully loaded for encryption.
2008-04-18 15:47:49.23 Server is listening on [' any ' <ipv4> 1433].
2008-04-18 15:47:49.24 Servers Server local connection provider are ready to accept connection on ["".] Pipe "sqllocal" MSSQLServer].
2008-04-18 15:47:49.24 Servers Server local connection provider are ready to accept connection on ["".] Pipe "SQL" query.
2008-04-18 15:47:49.28 Server server is listening on [127.0.0.1 <ipv4>1434].
2008-04-18 15:47:49.28 Server Dedicated admin connection support is established for listening to port 1434.
2008-04-18 15:47:49.29 Server The SQL network Interface library could not register the Service Principal Name (SPN) for the S QL Server Service. error:0x54b. Failure to register a SPN cause integrated authentication to fall back to NTLM instead of Kerberos. This is a informational message. Further action is only required if Kerberos authentication be required by authentication policies.
2008-04-18 15:47:49.31 Server SQL server is now ready for client connections. This is a informational message; No user action is required.
2008-04-18 15:47:49.40 spid5s starting up database ' msdb '.
2008-04-18 15:47:49.68 spid8s clearing tempdb database.
2008-04-18 15:47:49.93 spid5s Recovery of any in-doubt distributed transactions involving Microsoft distributed Transactio N Coordinator (MS DTC) has completed. This is a informational message only. No user action is required.
2008-04-18 15:47:50.57 spid8s starting up database ' tempdb '.
2008-04-18 15:47:50.76 spid5s Recovery is complete. This is a informational message only. No user action is required.
2008-04-18 15:56:22.34 spid51 SQL Trace is stopped due to server shutdown. Trace ID = ' 1 '. This is a informational message only; No user action is required.
2008-04-18 15:56:22.40 Server The SQL network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server Service. error:0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.
C: "Program Files" Microsoft SQL Server mssql.1 "MSSQL" binn>

Open the SSMs tool, disconnect first, then create a new query, and perform the following named restore

Copy Code code as follows:

Use master
Go
RESTORE DATABASE Master
From disk= ' C: "program Files" Microsoft SQL Server mssql.1 "MSSQL" Backup "Master.bak"
Go

The message displayed in the database should be:

376 pages have been processed for database ' master ', file ' master ' (located on file 1).
6 pages have been processed for database ' master ', file ' Mastlog ' (located on file 1).
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
Indicates that the master database was restored successfully and the master database is visible when you start the service and enter SSMS

Recover master Database

Method One: (Operation steps)

1 Stop MSSQLServer service
2 Command Line Input:

Copy Code code as follows:

net start SQL Server (MSSQLServer)/M

The following information is normally displayed:

Copy Code code as follows:

SQL Server (MSSQLSERVER) service is starting ...
SQL Server (MSSQLSERVER) service has started successfully.

3 Command Line Input:

Copy Code code as follows:

sqlcmd
1>use master;
2>go

Display at this time:
The database context has been changed to ' master '.

Copy Code code as follows:

1>restore db Master from disk= ' database backup file path ';
2>go

If the recovery succeeds, the following information is displayed:

376 pages have been processed for database ' master ', file ' master ' (located on file 1).
3 pages have been processed for database ' master ', file ' Mastlog ' (located on file 1).
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
[/code]

The failure shows:

Copy Code code as follows:

Message 3154, Level 16, State 4, Server HC, line 1th
Database backups in the backup set are different from the existing ' master ' databases.
Message 3013, Level 16, State 1, Server HC, line 1th
The RESTORE DATABASE is terminating abnormally.

[/code] is required to add: with REPLACE after the recovery statement. Such as:

1>restore DB Master from disk= database backup file path ' with REPLACE;
2>go
[/code]

4 Restart MSSQLServer service

Method Two: (similar to the method one)

1 Open SQL Server Configuration Manager, click SQL Server Service
2 in the right pane, right-click SQL Server (< instance name >), and then click Properties
3 On the Advanced tab, in the Startup parameters box, type the semicolon ";" The delimited parameter. (for example, to start in Single-user mode, insert "-M;" Before the existing startup options, click OK, and then the warning box pops up, click OK)
4 Restart the database engine
5 (same method one step 3)
6 after a successful recovery of the master database, to restart the server instance in normal multiuser mode, you must first remove the "-m;" That you just entered from the Startup parameters box, and then restart the SQL Server service

Restoring the msdb database

The msdb database stores more of the content in the SQL agent, such as jobs, schedules, operators, warnings, and other information about SQL Server Integration Service (SSIS). The recovery process is similar to the normal database recovery process, and the difference is that it needs to be restored under Single-user mode.

1 Open SQL Server Management Studio and connect to the associated database server
2 Right-click the msdb database, click Properties to open the Database Properties window, click Options, specify "Single_user" on the right, status/restrict access, and then click OK to start in single-user mode, where msdb is displayed as "msdb (single user)"
3 (same as restoring a normal database ...) ）
4 after the recovery, don't forget to change "restricted access" to the state "Multi_user"