SQL Server backup and restore system database

A system database is a necessary database for managing and maintaining SQL Server, which is critical to the operation of the server instance. Multiple system databases must be backed up after a large number of updates, including master, model, msdb, and backup of these system databases to restore and restore the SQL Server system in the event of a system failure, such as a hard drive loss

system databases that need to be backed up

1. Backing Up the system database

2. Simulating database corruption

(1) First, stop the SQL Server service

(2) Then, delete the system database file

(3) Turn on SQL Server service again, find the system error, SQL Server database system is paralyzed

3. Start rebuilding the database now

(1) First, locate the installation command C:\Program Files\Microsoft SQL Server\100\setup Bootstrap\release\setup.exe and copy four rows to the C drive

(2) Enter the character interface to locate and execute the installation command

In the case of Windows Authentication mode, the following syntax is required:

setup/action=rebuilddatabase/quiet/instancename=< DB instance name >/sqlsysadminaccounts=< login name;?

In the case of mixed authentication mode, you also need to use the/SAPWD parameter to provide the SA password with the following syntax:

setup/action=rebuilddatabase/quiet/instancename=< DB instance name >/sqlsysadminaccounts=< login >/sapwd=<sa Password >

My database is mixed authentication mode, so enter the following command:

4. Restart the service now, open the database and discover that the database is initialized, so you need to restore the master database

(1) Single-user mode enable SQL Server service

Netstart mssqlserver/m/S

(2) Credit to connect to SQL Server instance

SQLCMD-E-S ServerName

(3) After connecting, restore the master database

Restore Database Master from disk= ' master backup file path '

Go

5. Start the service now, open the database, and find that the database is back to normal

6. Restore the msdb database

7. Restore the Model database

SQL Server backup and restore system database