Database backup and recovery-5 System database backup and recovery

The backup and recovery mentioned above are all focused on the user database. SQLServer also maintains a group of system-level databases (referred to as "system databases") that are critical to the running of server instances. After each system update, you must back up multiple system databases. The system databases that must be backed up include msdb, master, and mod.

The backup and recovery mentioned above are all focused on the user database. SQLServer also maintains a group of system-level databases (referred to as "system databases") that are critical to the running of server instances. After each system update, you must back up multiple system databases. The system databases that must be backed up include msdb, master, and mod.

The backup and recovery mentioned above are all focused on the user database. SQLServer also maintains a group of system-level databases (referred to as "system databases") that are critical to the running of server instances. After each system update, you must back up multiple system databases. The system databases that must be backed up include msdb, master, and model. If any database uses replication on the server instance, you must back up the distribution system database. Back up these system databases to restore and restore the SQLServer system in the event of system faults (such as hard disk loss.

Database

The master database records all system-level information of the SQL Server system, such as the Logon account, system configuration settings, endpoints and creden。, and the information required to access other database servers. The master database also records the initialization information required to start the server instance, the location of the master file of each other. The master database is the first database opened when SQL Server is started. SQLServer finds information about other databases from this database. If there is a problem with the master database, the entire SQL Server cannot be started normally.

The master database itself is not large, and a backup is fast. We recommend that you always back up the complete master database to fully protect your data. If the master database is damaged, you can restore the latest full database backup of the master database to easily repair the damaged database.

If the server instance cannot be started because the master database is seriously damaged and there is no backup, it must be rebuilt. Rebuilding the master database restores all system databases to their original state. For example, recreating the master database will delete and recreate the msdb database. This will lead to loss of all plan information and backup and restoration history. Therefore, after the master database is re-built, SQL Server will be reinstalled, and all user records will be lost. The user database needs to be appended again, and the SQL Server task plan must be re-installed. This is a tough process. Rebuilding the master database is a last resort.

After you execute any statements or system procedures to modify the information in the master database (for example, after you change the configuration options for the server range), you should back up the master database. If no backup is performed after the master database is changed, all changes made since the last backup will be lost when the backup is restored.

We recommend that you do not create user objects in the master database. However, if a user object is created in the master database, the backup plan should be executed frequently to protect user data.

Operations that cause updates to the master database and require backup include:

· Create or delete a user database.

When the user database automatically grows to accommodate new data, the master database is not affected.

· Add or delete files and file groups.

· Add logon or Other Logon security-related operations.

Database-level security operations (such as adding users to a database) have no impact on the master database.

· Change the configuration options for the server range or database configuration options.

· Create or delete a logical backup device.

· Configure a server for Distributed Query and Remote Procedure Call (RPC), such as adding a linked server or logging on remotely.

RESTORE the master database using the RESTORE command. However, after the master database is restored, the SQL Server instance stops automatically. As we have mentioned earlier, the master database records the addresses of the master files of all other databases. SQL Server uses this address to find these databases. If we restore the master database to a new server, the file address will inevitably change. In this case, SQL Server cannot find other databases. You need to start SQLServer in single-user mode and modify the information in the master database to a new address.

If you decide to restart the Server in single-user mode, stop all SQLServer services (except the Server instance itself) and all SQL Server utilities (such as SQLServer proxy and Report Server ). Stopping services and utilities prevents them from attempting to access server instances. Otherwise, after a single user is started, they will occupy the user connection, but the Administrator will not be connected.

There will be an example later to introduce how to start SQLServer in single-user mode and a complete set of system database recovery methods.

9.4.2 model database

When creating a user database, the model database is a template used by SQL Server. All contents of the model database (including database options) will be copied to the new database. Therefore, this database is not recommended for any modifications. Do not modify the database or store any user data in the database unless you have created some templates.

Although the content in this database does not change, when starting SQL Server, you must use some settings of the model database to create a new tempdb. SQL Server cannot be started without tempdb. Therefore, the model database must always exist in the SQLServer system. This database must also be backed up.

The restoration of the model database is the same as the restoration of the complete database for the user database.

Database

SQL Server, SQL Server Management Studio, and SQL Server proxy use the msdb database to store data, including plan information and backup and restoration history information.

SQL Server automatically maintains a complete online backup and restoration history in the msdb database. This information includes the name of the backup party, the backup time, and the device or file used to store the backup. SQLServer Management Studio uses this information to propose a plan to restore the database and apply transaction log backup. Backup events related to all databases will be recorded, even if they are created by custom applications or third-party tools. For example, if you use the Microsoft Visual Basic application that calls the SMO management object (SMO) to perform backup operations, the events are recorded in the msdb system table, Windows Application log, and SQLServer error log. All plan information used by the SQL Server Agent, including Plan Definition, execution schedule, and history logs, are stored in the msdb database.

By default, the msdb database uses the simple recovery mode. If you use the backup and restoration history information in the msdb database when restoring the user database, we recommend that you use the full recovery mode for the msdb database, we recommend that you store msdb Database Transaction logs on a fault-tolerant storage device.

The restoration of the msdb database is the same as the restoration of the complete database for the user database. For details, refer to the instance in section 9.7.

And resource database (ResourceDatabase)

SQL Server has two System databases, tempdb and resource database. Neither of these databases can be started normally, but neither of them can be used for database backup.

The tempdb System database is a global resource that can be used by all users connected to the SQL Server instance. Every time SQLServer is started, the tempdb database will be re-created to maintain a clean database copy at system startup. After the connection is disconnected, SQL Server automatically deletes the temporary table and stored procedure. Therefore, no content in the tempdb database needs to be saved when the service is closed. SQL Server can create a tempdb database as long as there is a clean model database. The Tempdb database itself does not need to be backed up. SQL Server does not provide backup and restoration operations for the tempdb database. Protect the model database, and you will be able to get a good tempdb database at the next startup. Therefore, the protection for the tempdb database is switched to protection for the model database.

A Resource database is a read-only database that contains all System Objects in SQL Server. System objects (such as sys. objects) exist physically in the Resource database, but logically they appear in the sys architecture of each database. The Resource database does not contain any user data or user metadata. For SQL server of the same version, their Resource databases should be the same. The physical file names of the Resource database are mssqlsystemresource. mdf and mssqlsystemresource. ldf. Each SQL Server instance has one (and only one) associated mssqlsystemresource. mdf file, which is not shared between instances.

Because the Resource database is never modified, SQL Server theoretically does not need to back up the Resource database. However, administrators should not only consider the SQL server level, but also the entire system level. It is inevitable that the physical disk will be damaged and the file will be damaged. Therefore, you must use the mssqlsystemresource. mdf file as a binary file for file-based backup or disk-based backup. When restoring, stop the SQL Server service and manually restore the backup copy of mssqlsystemresource. mdf. Exercise caution that different versions of the SQL Server Resource database may be different. Do not overwrite the current Resource database with an expired or insecure version.

Table 9-5 lists all system databases.

Table 9-5 System Database Backup policies

System Database	Description	Supported or not Backup	Restore Mode	Note
Master	Database that records all system-level information of the SQL Server System	Supported	Simple	The master database must be backed up frequently to fully protect data based on business needs. We recommend that you use a regular backup schedule so that more backups can be added after a large number of updates.
Model	Templates created for all databases on the SQL Server instance	Supported	User Configurable	Back up the model database only when the business needs it. For example, back up immediately after customizing its database options. Best Practice: We recommend that you only create a full database backup for the model database as needed. Because the model database is small and rarely changed, you do not need to back up logs.
Msdb	The SQL Server Agent is used to schedule alarms and jobs, and to record the database for operator information. The msdb database also contains a history table, such as a backup and restoration history table.	Supported	Simple (Default)	Back up msdb database during update
Resource (RDB)	Read-Only databases that contain copies of all system objects attached to SQL Server	Not Supported	-	The Resource database is located in the mssqlsy-stemresource. mdf file, which only contains code. Therefore, SQL Server cannot back up the Resource database.
Tempdb	Workspace used to save temporary or intermediate result sets. This database is re-created every time you start an SQL Server instance. When the server instance is disabled, all data in the tempdb database will be permanently deleted.	Not Supported	Simple	Unable to back up tempdb System Database
Distribution	This database exists only when the server is configured as a replication distributor. This database stores metadata, copies of historical data, and transactions used for transaction replication.	Supported	Simple	Depends on your specific copy configuration