First.
As mentioned earlier, the “SQL Server Backup and recovery series” is focused on the user database. SQL server also maintains a set of system databases that are critical to the running of server instances. After each system update, multiple systems must be backed up.According to the library. The system database that must be backed up includes: MSDB, master, model. If you use replication, you need to back up the distribution library. Backup is used to restore the system when it fails to prevent system failure.
1.masterDatabase introduction
masterThe database records all system-level information, such as login accounts, system configuration, endpoints and credentials, and information needed to access other database servers. There is also the initialization information needed to start the instance. Other database master file locations are the first when the SQL server startsAn open database. If the master library has problems, the entire SQL server cannot start properly.
masterThe database is not large enough to recommend full database backup. If the master library fails to start the server instance and has no backup, it must be rebuilt. After rebuilding, the MSDB library is deleted and recreated, resulting in the loss of all planning information and the backup and restore of history. SQL seRver was also reinstalled. The user records are missing, the database is appended again, and the task plan is rebuilt. It is a very frustrating process.
Restore the master database to use restore instructions. After restoring the master database, the instance of SQL server will stop automatically. Because master records all database master file addresses, looking for these databases through these addresses. If mastWhen the ER database is restored to a new server, the file address will inevitably change, and then the address can not be found, you need to start SQL Server in single-user mode, and change the information in the master database to a new address.
To start single-user mode, stop all SQL server services (except the server instance itself) and stop all SQL Server utilities. Otherwise, after the single user starts, they occupy the user connection, and the administrator will not connect.
2.modeldata base
When creating user databases, the model database is the template used by SQL server. It is not recommended for the library to modify the task. Although the contents of the library generally do not change, SQL server needs to create a new TEM at startup using some of the settings of the model libraryPDB. If there is no tempdb, the instance cannot be started.
3. msdbdata base
msdbPlan information, backup and restore historical information, etc. By default, the MSDB database uses a simple recovery mode. If you use the backup and restore history information in the MSDB database when restoring the user database, you are advised to use the full restore mode for the MSDB database, and you are advised tomsdbDatabase transaction logIt is placed in a fault tolerant storage device.
4. tempdbDatabase and resource database
sql serverThere are two more systematic databases, especially tempdb and resource database Resource Database. Without these two databases, sqlserver can’t start, but they can’t do database backup. Tempdb system database is a global resource.It can be used by all users connected to the sqlserver instance. Every time you start sqlserver, you will rebuild the tempdb library and keep a clean copy. After the session connection is disconnected, the temporary table of the session is automatically deleted. So tempdb will not have anything to wear.When the service is closed, save it. Tempdb doesn’t need backup and restore operations. Protecting the model database ensures a good tempdb database at the next boot.
Resource The database is a read-only database, which contains all the system objects in SQL server. For example, sys.objects. Resource database is never modified, so SQL server does not need to backup Resource database theoretically.In view of physical magnetic damage, file backup or disk based backup can be made.
Two. Backup strategy for system database
-- View default recovery mode of system database SELECT name, recovery_model_desc FROM sys.databases
| System database | Explain | Do you support backup? | Recovery mode | Notes |
| master | Record all system level of sqlserver system. Information database |
Support | simple | It is necessary to backup regularly, regularly backup plans, and add backup after massive updates. |
| model | Template created for all databases | Support | User configurable | Create a full database backup as required, because the database is small. There are few changes, so there is no need to backup logs. |
| msdb | sqlserverAgents are used to arrange alerts and operations. And record the database of operator information. MSDB The database also contains history tables, such as backup and Restore history table |
Support | simple (Default value) |
Backup MSDB database when updating. |
| Resource | A read-only database for all system object copies | I won’t support it | The Resource database cannot be backed up. | |
| tempdb | Used to save workspaces for temporary or intermediate collections. When you start the instance, you re create the library and close the service. Permanently delete all data in tempdb database |
I won’t support it | simple | The tempdb database cannot be backed up. |
| distribution |
This database is not available when replicating distributor. |
Support | simple | According to the specific replication configuration decision. |