SQL Server Enterprise Platform Management Practice book notes-backup and recovery of several system libraries

Original: SQL Server Enterprise Platform Management Practice book notes-backup and recovery of several system libraries

master database

Master acts as the primary database for the database, documenting all system-level information for the SQL Server system, such as logged-on users, System configuration settings, endpoints and credentials, and the information needed to access other data servers. The master database also records the initialization information required to start the server instance, and the primary file location for each additional database. The master database is the first database that is opened when SQL Server starts. SQL Server is the information that finds other data from this database. If there is a problem with the master database, the entire SQL Server will fail to start.

The master database itself is not big enough to make a backup soon. It is recommended that you always do a full database backup of master to fully protect your data. If the master database is corrupted, you can easily repair the damaged database by restoring the most recent full database backup of the master database.

If you cannot start the server instance because the master database is severely damaged, there is no backup, it can only be rebuilt at this time. Rebuilding the master data restores all of the system databases to their original state. For example, rebuilding the master database deletes and re-creates the msdb database. This will result in the loss of all scheduling information as well as backup and restore history. So after rebuilding the master database, SQL Server is like being re-installed. All user records are lost, the user database needs to be attached again, and the SQL Server task record is rebuilt. This is a very frustrating process. Rebuilding the master database is a last resort option.

After you have executed any statements or system procedures to change the information in the master database (for example, after you change the server-wide configuration options), you should back up the master database. If you do not make a backup after you change the master database, the changes since the last backup are lost when you restore the backup.

Behavior changes the meta data in master:

1, create or delete user database, this does not include the user database automatically grow to accommodate the new data, the master data is not affected.

2. Add or remove files and filegroups

3, add login or other login security related operations, this does not include database-level operations, such as adding users

4. Change server-wide configuration options or database configuration options

5. Create or delete a logical backup device

6. Configure a server for distributed queries and remote procedure call (RPC), such as adding a linked server or Telnet.

Restores the master database using or restore instructions. However, after you restore the master database, the SQL Server instance automatically stops. As we said before, master records the address of all other database master files. 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. SQL Sever will not find another database at this point. You need to use single-user mode to start SQL Server and modify the information in the master database to a new address.

When you turn on single-user mode, you need to stop all SQL Server services (except the server instance itself) and stop all SQL Server utilities (SQL Server Agent, Report server, and so on). Stopping services and utilities can prevent them from trying to access the server instance. Otherwise, after the single user starts, they will occupy this user connection, but the administrator cannot connect.

Model database

When you create a user database, the model database is the template that SQL Server uses. The entire contents of the model database (including database options) are copied to the new database. So this database is recommended to make any changes. Do not change the database, or put any user data in the database, unless you create some templates for the purpose.

Although the contents of this database generally do not change, when SQL Server starts, a new tempdb is created using some of the settings of the model database. If no tempdb,sql server will fail to start. Therefore, the model database must always exist in the SQL Server system. So this database also needs to be backed up.

Restoring the model database is the same as performing a full database restore of user data.

msdb database

The msdb database is used to store scheduling information and information about backup and restore history, so SQL Server itself, SSMS and agentd, use it.

SQL Server automatically maintains a full in-place backup and restore history in the msdb database. This information includes the name of the party performing the backup, the backup time, and the device or file used to store the backup. SSMS uses this information to propose a plan to restore the database and apply transaction log backups. All database backup events will be logged, even if they were created by a custom program or by a third party. For example, if you use a C # program to call SQL Server to perform a backup operation, all events are logged in the msdb system table, the Windows application log, and the SQL Server error log. And all the scheduling information that SQL Server Agent uses. Includes the definition of the plan, scheduling, history log and other information.

By default, the msdb database uses the simple recovery model. If you are using all of the backup and restore history information from the msdb database when you want to recover a user database, we recommend that you use the full recovery model for the msdb database, and that you consider placing the msdb database transaction log on a fault-tolerant storage device.

Tempdb staging Library and resource database (Resource databse)

SQL Server also has two system databases that are more specific. They are the tempdb and the resource database. Missing this two database SQL Server page will fail to start. However, these two libraries do not make database backups .

The TEMDP system database is a global resource. Available to all users who connect to an instance of SQL Server.

The tempdb database is rebuilt each time SQL Server is started, so that a clean database copy is always maintained at system startup. When the connection is broken, SQL Server automatically deletes the staging tables and stored procedures. So there is nothing in the tempdb database that needs to be saved when the service is closed. As long as you have a clean model database. SQL Server is able to create a tempdb database. TEMDP data itself is not backed up. SQL Server also does not provide backup and restore operations to the tempdb database. Protecting the model database ensures that a good tempdb database will be available at the next boot. As a result, the tempdb database is protected against the model database.

A read-only database when resource data. It contains all of the system objects in SQL Server. System objects, such as sys.objects, are physically present in the resource database. But logically, they appear in the SYS structure of each database. The resource database does not contain any user or user metadata. For the same version of SQL Server. Their resource database should be the same.

By default, the resource pool is not visible under the instance, but the physical existence of the resource database, the physical file name is Mssqlsystemresource.mdf and Mssqlsystemresource.ldf. By default, these files are stored in the directory: \program Files\Microsoft SQL Server\mssql10. Sqlexpress\mssql

In the cluster, the resource database is located in the Data folder on the shared disk. In fact, the resource database relies on the master database location. If the administrator has moved the master database, you must also move the resource database to the same location.

Because the resource database is never modified, SQL Server is theoretically not backed up. But administrators should not only consider SQL Server-level issues. Also consider the whole system-level problem. It is inevitable that physical disk corruption will occur, causing the file to become corrupted. So you do a file-based backup or disk-based backup by using the Mssqlsystemresource.mdf file as a binary file. The SQL service should be stopped when recovering. means to restore the backup copy of the Mssqlsystemresource.mdf file. And be cautious, different versions of the resource database will not be the same. is to overwrite the current resource database with an outdated version or a potentially unsafe version.

Let's focus on the list

System Database	Description	Backup is supported	Recovery mode	Comments
Master	Database that records all system-level information for a SQL Server system	Support	Simple	The master database must be backed up frequently to be fully Protect data. It is recommended that you use a regular backup schedule Then you can add more backups.
Model	Templates created for all databases on an instance of SQL Server	Support	User Configurable	Back up the model database only when business needs, such as customizing its data Back up immediately after the library option. The best way to do this is to recommend creating Model database Integrity database backup. Because the model database is small And rarely changed, so there's no need to back up logs
Msdb	SQL Server Agent is used to schedule alarms and jobs and to record operators Database of information. The msdb database also contains history tables, such as Backup and restore history tables	Support	Simple (default value)	Back up the msdb database when updating
Resource (RDB)	Contains read-only copies of all system objects included with SQL Server Database	Not supported		The resource database is located in the Mssqlsystemresource.mdf file, The file contains nearly the code. Therefore, SQL Server cannot back up the resource database. However, it can be done through file backup and backup to disk, and not through SQL Server when restoring. To restore these backups, you can only manually restore the backup copy of Mssqlsystemresource.mdf. And be careful not to overwrite the current resource database with an outdated version or potentially unsafe version
Tempdb	A workspace for saving historical or intermediate result sets, The library is rebuilt each time the instance of SQL Server is started, and the service Permanently delete all data from the tempdb database when the instance is closed	Not supported	Simple	Unable to back up the tempdb system database
Distribution	Only exists if the server is configured as a replication Distributor This database. This database stores the metabase, various replicated history data, and transactions for transactional replication	Support	Simple	Depending on your specific replication configuration decision

SQL Server Enterprise Platform Management Practices book notes-backup and recovery of several system libraries