recovery of [SQL Server] Database

Database recovery is the operation that corresponds to a database backup, which is the process of reloading a database backup into the system. Database recovery can create related files that exist in the database when the backup is complete, but all database modifications after the backup are lost.

When SQL Server is recovering from a database, the system automatically checks for security to prevent accidental operation by using incomplete information or other data backups to overwrite the existing database. The system will not be able to recover the database when there are several situations.

(1) The database name in the restore operation does not match the database name recorded in the backup set.

(2) You need to automatically create one or more files through a restore operation, but there are already files with the same name.

(3) The database named in the restore operation is already on the server, but the database contained in the database backup is not the same database, for example, the database name is the same, but the database is created in a different way.

If you re-create a database, you can disable these security checks.

One. Database Recovery Model

Based on the need to save data and considerations for storage media usage, SQL Server provides 3 database recovery models: Simple recovery, full recovery, and bulk-logged recovery.

1. Simple Recovery Model

The simple recovery model restores the database to the last backup, but cannot restore the database to a point of failure or to a pending point in time. It is often used to restore the latest full database backups, differential backups.

The simple recovery model is a bit of allowing high-performance bulk copy operations, as well as the ability to reclaim log space. However, you must reorganize the latest database or changes after the differential backup.

2. Full Recovery model

The full recovery model uses database backups and transaction log backups to provide the ability to recover a database to a point of failure or to a specific instant. All operations, including bulk operations such as SELECT INTO, CREATE index, and bulk load data, are fully logged to ensure this level of recovery.

The advantage of a full recovery model is that it can be recovered to any point in time so that loss and corruption of the data file does not result in loss of work, but if the transaction log is corrupted, it must be modified after the most recent log backup.

3. Bulk-Logged Recovery model

The bulk-logged recovery model provides the best performance and minimum log usage space for some large-scale or bulk-copy operations. In this model, the data loss of the bulk copy operation is more severe than the full recovery model, because in this model, only the minimum log of operations is logged and these operations cannot be controlled individually. It only allows the database to be restored to the end of the transaction log backup and does not support instant point recovery.

The advantage of the bulk-logged recovery model is that it saves log space, but if a log is corrupted or a bulk operation occurs after a log backup, the changes that have been made since the last backup must be re-made.

Different hi-Pay models target different performance, disk and tape space, and the need to protect data loss. The recovery model determines the recovery process for the overall backup strategy, including the type of backup that can be used, that is, choosing a recovery model that determines how data is backed up and to what extent data loss can be sustained.

Two. View backup Information

Because there is often a long time lag between the recovery database and the backup database, it is difficult to remember the backup device and the backup file and the database it backs up, which needs to be viewed.

The information that needs to be viewed usually includes: data and log files in the backup set, backup header information, media header information. You can view this information using SQL Server Management platform and Transact-SQL statements.

1. Use the SQL Server Management platform to view backup information

To view all the backup media properties using SQL Server, proceed as follows:

(1) Open the SQL Server Management platform, in Object Explorer, expand the node "server tree" → "server Objects" → "backup Device", right click on a specific backup device name, select the "Properties" command on the popup shortcut menu to open the "Backup Device" Properties window.

(2) In the Backup Device Properties window, select the Media Content Selection card, open the window, and list the information about the selected backup media in the listbox.

2. Use Transact-SQL statements to view backup information

The RESTORE headeronly statement is in the following format:

RESTORE headeronly

From <backpi_device>

[With {nounload| UNLOAD}

[[,]file =file_number]

[[,]password={password| @password_var}]

[[,]mediapassword={mediapassword| @mediapassword_var}]

<backup_device>::={

{' Logical_backup_device_name ' | @logical_backup_device_name_var}

| {disk| tape}={' Physical_backup_device_name ' | @physical_backup_name_var}

}]

Each option has the following meanings:

(1) <backup_device>: Specifies the logical or physical device to use when backing up the operation.

(2) File=file_number: Identifies the backup set to be processed.

(3) password={password| @password_var}: Backup set password.

(4) mediapassword={mediapassword| @mediapassword_var}: Media set password.

The result set returned by the RESTORE HEADERONLY statement includes information such as the backup set name, the backup set type, the effective time of the backup set, the server name, the database name, the size of the backup, and so on.

Example: Use Transact-SQL statements to get BACK4 database backup information.

RESTORE Headeronly from Back4

Three. Recovering a Database

1. Recovering a database using the SQL Server Management platform

The procedure is as follows:

(1) in the SQL Server management platform, in Object Explorer, expand the Database folder, right-click the database icon you want to restore, for example, in the sales database, select the Task/restore/database option from the shortcut menu that appears, and open the Restore Database window.

(2) In the General tab of the Restore Database window, the target database drop-down list box is used to select the database to restore, the target point in time text box is used to set the restore point, you can leave the default values, or you can click the Browse button next to open the time-of-Day Restore dialog box. Select a specific date and time for a full database backup recovery, only a point in time when the full backup is complete; The source database drop-down list box in the restored sources area is used to select the name of the data inventory for the backup to restore, and the source device text box is used to set the location of the restored backup device; Select the backup set for restore grid to select the restored backup.

(3) Select the "Options" item. Settings in which to restore the options and restore the state. Where the Overwrite existing database check box is selected to indicate that the restore operation overwrites all existing databases and related files; The Preserve replication settings check box is selected to preserve replication settings when restoring a published database to a server other than the one on which the database was created; "Prompt before restoring each backup" check box is selected to require user confirmation before restoring each backup setting; the Restrict access to restored database check box is selected to indicate that the restored database is only available to members of db_owner, dbreator, or sysadmin; "Restore Database files to" The zone selects the path to the data file and the log file.

(4) When the setting is complete, click the OK button to restore the database and a message dialog box appears after the restore succeeds, asking the user to confirm that the restore completed successfully.

Restore operations for differential backups, log backups, file and filegroup backups are similar to the restore operation of a full database backup.

2. Recovering a database using Transact-SQL statement restore

In contrast to the BACKUP statement, the RESTORE statement can restore the entire database backup, data file and filegroup backup, and transaction log backups.

The syntax format for the RESTORE statement is:

RESTORE {database| LOG}

{database_name| @database_name_var}

<file_or_filegroup>[,... N]

[From <backup_device>[,... N]]

[With

[Restricted_user]

[[,]file={file_number| @file_number}]

[[,]password={password| @password_var}]

[[,]medianame={media_name| @media_name_var}]

[[,]mediapassword={mediapassword| @mediapassword_var}]

[[,]move ' logical_file_name ' to ' operating_system_file_name '] [,... N]

[[,]keep_replication]

[[,]{norecovery| recovery| Standby=undo_file_name}]

[[,]replace]

[[,]restart]

[[,]stats[=percentage]]

[[,]stopat={date_time| @date_time_var}

| [,] stopatmark= ' Mark_name ' [after DateTime]

| [,] stopbeforemark= ' Mark_name ' [after DateTime]]

]

Each option has the following meanings:

(1) Database: Specifies the databases to restore the backup.

(2) Log: Specifies that transaction log backups are resumed for the database. SQL Server checks the transaction logs that have been backed up to ensure that the transaction is restored to the correct database in the correct sequence.

(3) {database_name| @database_name_var}: The database to which the log or entire database is to be restored.

(4) <file_or_filegroup>: Specifies the name of a logical file or filegroup that is included in the database that you want to recover, and you can specify multiple files or filegroups.

(5) From<backup_device>: Specifies the backup device from which to restore the backup, with the same definition as the BACKUP statement.

(6) Restricted_user: Restricts access to the recently recovered database only to members of the db_owner, db_creator, or sysadmin roles. This option is used in conjunction with recovery.

(7) file={file_number| @file_number}: Identifies the backup set to be recovered.

(8) password={password| @password_var}: Provides the backup set password.

(9) mediapassword={mediapassword| @mediapassword_var}: Provides the password for the media.

Move ' logical_file_name ' to ' operating_system_file_name ': Specifies to move the given logical_file_name to Operating_system_ The location specified by the file parameter. By default, Logical_file_name reverts to its original location.

(one) NORECOVERY: Indicates that no uncommitted transactions are rolled back after the recovery operation.

RECOVERY: Indicates that the uncommitted transaction is rolled back after the recovery operation is performed as the default value.

Standby=undo_file_name: Specifies that the file name is revoked so that recovery can be canceled.

REPLACE: Indicates that if a database with the same name already exists, the existing database is deleted and the specified database is created.

stopat={date_time| @date_time_var}: Specifies that only the contents of the database are restored between the specified date and time. This option is only used with the restore LOG.

(+) stopatmark= ' Mark_name ' [after DateTime]: Represents a return to the specified token, including the transaction that contains the token. This option is only used with the restore log.

stopbeforemark= ' Mark_name ' [after DateTime]: Represents a restore to the specified token, but does not include the transaction that contains the token. This option is only used with the restore log.

Example: Recovering an entire database from an existing backup media back1 sales.

RESTORE DATABASE Sales

From Back1

Example: Recovering a database from backup file D:\Sales_back.bak on disk Sales.

RESTORE DATABASE Sales from disk= ' D:\Sales_back.bak '

Example: A database backup and a transaction log for database recovery operations.

RESTORE DATABASE Sales

From Back1 with NORECOVERY

RESTORE LOG Sales

From Back1 with NORECOVERY

Example: Restore the specified data file sales_data1 in the database sales.

RESTORE DATABASE Sales

File= ' Sales_data1 '

From Back4

With NORECOVERY

recovery of [SQL Server] Database