SQL Server System Database recovery

Original: SQL Server system Database recovery

Tags: SQL server/mssql server/database/dba/failback/master

Overview

SQL Server maintains a set of system-level databases, called system databases, that are critical to the operation of the server instance. You must back up multiple system databases after a large number of updates. The system databases that must be backed up include msdb,master , and model. If any databases are using replication on the server instance, you must also back up the distribution system database. By backing up these system databases, you can restore and restore the SQL Server system in the event of a system failure, such as a lost hard disk.

Directory

• Overview
• Body
  • System Database Description
  • single-User launch instance
  • Restore the master database
  • Restore the msdb database
  • Restore model Database
  • Backup Restore replication Database
• Summary

body
System Database Description

single-User launch instance

Method 1: In Configuration Manager, right-click the instance to add-m (note the lowercase m) in the startup parameters, restart the service, and then remember to remove the-m parameter to restart the service.

Method 2: In cmd, using the net start mssqlserver/m, first stop all related services.

In the case of this window, the DB instance is unable to log in, all of them need to restart the service again, and the/m parameter is removed when restarting.

Method 3: In CMD First locate the Database installation directory "Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\binn", enter Sqlservr.exe–c–m, Then open the MSMs direct point to the new query using the Administrator user entry.

Restore the master database

The database that restores master must start the instance on a single user and then restore it using an administrator user. This is also the purpose of the article before the single-user launch instance

After starting the instance using method 1 and Method 3, do not log in as usual login SQL management tool, but click on the new query or click on the File menu-new-Database engine query, and then enter the user administrator permissions, the next is to restore the database, the RESTORE statement is simple such as:

RESTORE DATABASE [Master]  from  DISK =N'D:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\backup\master.bak' GOIf it fails, add the withREPLACERESTORE DATABASE [Master]  from  DISK =N'D:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\backup\master.bak'  with REPLACE

One thing to note in Method 2 is that after a single user launches an instance into the SQLCMD tool, every step after executing use master is added to go, otherwise the cursor flashes, and Method 2 can only restore the master database and cannot restore other databases.

It is good to restart the service in the normal way after the restore.

Note: If you regenerate the master database (where the rebuild and restore backups are not the same, if you are restoring the most recent backup without re-restoring the msdb and model databases), be sure to re-restore the msdb and model databases.

Restore the msdb database   

Restoring the model or msdb database is the same as performing a full database restore of the user database. The database that the user is accessing cannot be restored. If SQL Server agent is running, it can access the msdb database. Therefore, before you restore msdb , stop SQL Server agent first.

The msdb database needs to be restored in single-user mode, where the single user is not the same as the single-user launch instance of the previous master, where a single user simply acquires individual access to the msdb database, so you can use the statement to set the database to single-user mode and then perform the restore.

 Use [Master]GOALTER DATABASE [msdb] SETSingle_user withno_waitGORESTORE DATABASE [msdb]  from  DISK =N'D:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\backup\msdb.bak'ALTER DATABASE [msdb] SETMulti_user withNo_wait

Restore model Database

There is no introduction to restoring the model database, as is the way to restore the user database.

Backup Restore replication Database

Replication supports restoring a replicated database to the same server and database from which the backup was created. If you restore a backup of a replicated database to a different server or database, you cannot preserve replication settings. In this case, you must re-create all publications and subscriptions after restoring the backup ( if you are using log shipping, you can restore the replicated database to the standby server ).

The replication database and its associated system databases should be backed up regularly. Back up the following databases:

• Publication database at the publisher
  
  
• distribution database on the distribution server
  
  
• Subscription databases at the individual subscribers
  
  
• The Master and msdb system databases at the Publisher, Distributor, and all Subscribers. When you back up one of these databases or the associated replication database, you should back up the databases at the same time. For example, you should back up the publishing database while backing up the master and msdb databases at the publisher. If you restore the publication database, make sure that the master and msdb databases are consistent with the publication database in terms of replication configuration and settings.

can refer to: https://msdn.microsoft.com/zh-cn/library/ms152560.aspx

Summary

In summary the system database is very important for the instance, so the backup plan must not be less than the system database backup.

If the article is helpful to everyone, I hope you can give a recommendation, thank you!!!

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

SQL Server System Database recovery