How to back up and restore and back up system databases in SQL Server

Backing up the SQL Server database, running the test restore process on a backup, and storing backup copies in another secure location prevent possible catastrophic data loss.

With a valid database backup, you can recover data from a variety of failures, such as:

Media failure.

User error (for example, a table was accidentally deleted).

Hardware failure (for example, disk drive corruption or server scrap).

Natural disaster.

Backup mode also has three types in SQL Server (it determines which data to back up)

1. Full backup

Backups include this database, part of the transaction log, database structure, and file structure. A full backup represents the database state at the time the backup was completed

2. Differential backup

It backs up data that has changed since the last full backup

3. Transaction log Backups

Backing up data in the transaction log

The recovery model is a feature of the database that controls the basic behavior of database backup and restore.

SQL Server provides three recovery models that determine how much and what data can be backed up.

1. Simple Recovery Model

Mainly used in small databases and infrequently changed data

2. Full Recovery model

You can prevent loss of data in the largest possible range, including database backups and food log backups, and provides comprehensive protection. This mode uses a backup of the database and all logs to restore the database

3. Bulk-Logged Recovery model

Under the full recovery model, all large volumes of data import and export operations produce fewer logs, and he logs minimal records, and some transactions are not recorded.

The full recovery model is the default recovery model.

There are four system databases for Microsoft SQL Server systems:

master database

The master database records all system-level information for the SQL Server system. It records all login accounts and system configuration settings. The master database is a database that records all other databases, including the location of the database files. The master database records initialization information for SQL Server, and it always has an up-to-date master database backup available.

tempdb database

The tempdb database holds all temporary tables and temporary stored procedures. It also meets any other temporary storage requirements, such as storing worksheets generated by SQL Server. The tempdb database is a global resource, and all temporary tables and stored procedures are stored in the database for all users connected to the system. The tempdb database is re-created every time SQL Server is started, so the database is always clean when the system starts. Temporary tables and stored procedures are automatically dropped when a connection is disconnected and no connections are active when the system shuts down, so nothing in the tempdb database is saved from one session of SQL Server to another session. By default, the tempdb database grows automatically as needed at run time at SQL Server. However, unlike other databases, each time the database engine is started, it resets to its initial size. If the size defined for the tempdb database is small, each time you restart SQL Server, the size of the tempdb database is automatically increased to the size required to support the workload, which may become part of the system's processing load. To avoid this overhead, you can use ALTER database to increase the size of the tempdb database.

Model database

The model database is used as a template for all databases created on the system. When the CREATE DATABASE statement is issued, the first part of the new database is created by copying the contents of the model database, and the remainder is populated by an empty page. Because SQL Server creates the tempdb database each time it starts, the model database must always exist in the SQL Server system.

msdb database

The msdb database is used by the SQL Server Agent to schedule alerts and jobs and to record operators. such as replication settings.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/