Dba30 asked what are the functions of the system dB, and how to restore the database due to damage (mainly the master database)

DBA30 asked what are the functions of the system DB, and how to restore the database due to damage (mainly the master database)

 

 

Master Database

 

Record SQL
All system-level information of the Server system. This includes metadata of the instance range (such as Logon account ),

 

Configure endpoints, linked servers, and systems. In addition, the master
The database also records

 

Exist, database file location and
SQL Server
. Therefore, if
Master Database

 

Unavailable, then
SQL Server
Cannot start. In SQL
Server, System Objects are no longer stored in
Master

 

Database, but stored in
In the mssqlsystemresource database.

 

 

The master database is critical to the system, so it is always necessary to save its current copy. Create another database,

 

Change the configuration value. operations such as modifying the Logon account will modify the master database, so it should always be completed.

 

After these operations, back up the master database. The master database itself is not large, and a backup is fast.

 

The master database is often backed up.

 

Because the master database also records the initialization information required to start the server instance

 

Location. Master database is SQL
The first database opened when the Server was started. SQL
Server is from

 

Information of other databases found in the master database. If the master database has problems, the entire SQL
Server

 

Cannot start normally.

 

If the master database is seriously damaged, you can restore the master database directly if there is a backup. If no backup

 

, You need to re-build the master database. Rebuilding the master database will restore all system databases to the original state.

 

Status. Rebuilding the master database will delete and recreate the msdb database. This will cause loss of all plan information and backup

 

And restore history. After the master database is rebuilt
Server databases are the same as those after reinstallation,

 

All user information will be lost, the user database needs to be re-appended, SQL
Server tasks and plans must be rebuilt.

 

Therefore, rebuilding the master database is a last resort.

 

After any statement or system process is executed to change the information in the master database, the master database should be backed up.

 

We recommend that you do not create user objects in the master database.

 

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

 

1. Create or delete a user database

 

2. add or delete files and file groups

 

3. Add login or other login security operations

 

4. Change the server-range configuration options or database configuration options.

 

5. Create or delete logical backup files

 

6. Configure the server used for Distributed Query and remote call, such as adding a linked server or logging on remotely.

 

 

RESTORE the master database using the RESTORE command. After the master database is restored, the SQL statement
The Server instance is automatically stopped.

 

I will write a blog about how to restore the master database.

 

 

 

Model Database

 

Used in SQL
Templates for all databases created on the Server instance. Because each start
SQL Server
All

 

Creates tempdb, so
The model database must always exist in
In the SQL Server system.

 

Create a database as a model database as an SQL
All content in the template. model database used by the SERVER will be repeat

 

Create a new database. Therefore, it is not recommended to make any modifications to this database unless it is intended to create some templates.

 

Although the content of this database will not change
Number of models used when Server is started

 

Create a new tempdb according to some settings in the database. If no tempdb, SQL
Server cannot be started. Therefore, the model database

 

SQL must exist
Server. 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.

 

 

Tempdb Database

 

The tempdb System database is a global resource that can be connected
SQL Server
And

 

It can be used to save the following items:

 

Explicitly created temporary user objects, such as global or local temporary tables, temporary stored procedures, table variables, or cursors.

 

 

SQL Server
An internal object created by the database engine, for example, a worksheet used to store intermediate results of offline or sorting.

 

 

Modifies the row version generated by a transaction from data in a database that uses committed read (using row version control isolation or Snapshot isolation transactions.

 

 

The data modification transaction is the result set (MARS) for Online indexing operations and multiple activities)
And AFTER
Trigger and other functions

 

.

 

The operation in tempdb is the minimum logging operation. This will cause the transaction to roll back. Start each time
SQL Server
Will be re-

 

Create a tempdb to keep a clean copy of the database at system startup. The temporary instance is automatically deleted when the connection is disconnected.

 

Tables and stored procedures, and no active connections after the system is closed. Therefore
Tempdb does not have any content from
SQL Server

 

The session is saved to another session. Do not allow
Use tempdb to back up and restore data.

 

 

 

 

 

Resource Database (mssqlsystemresource)

 

 

Resource Database is a hidden database. Executable system objects (stored procedures and functions in the system) are stored here. Create this number

 

The database is used for fast and secure upgrades. If no one can access this database, no one can change it. Easy to use

 

Replace the old resource database with the new resource database to upgrade it to the new one, including the object service package of the new system. Cannot use any

 

Check the database in normal mode. However, this database still requires disk space.

 

The mssqlsystemresource database is never modified, and theoretically no backup is required.

 

Msdb Database

 

By SQL
The Server proxy is used to schedule alerts and jobs, or by other features (such
Service Broker
And database mail) Use

 

SQL Server automatically maintains a complete online backup and restoration history in the msdb database. This information includes Backup 1.

 

Party name, backup time, and backup device used to store the backup. SQL
Server Management
Studio uses this information to propose

 

Plan to restore the database and apply transaction log backup.

 

Msdb uses the simple recovery mode by default. If you use the backup and restoration history records in the msdb database to restore the user database

 

Information, it is recommended to use the full recovery mode for the msdb database, and it is recommended to consider placing the msdb database transaction log in the fault tolerance storage device.

 

Restoring the msdb database is the same as restoring the complete database for the user database.