Shulou(Shulou.com)06/01 Report--

The purpose of a complete database restore is to restore the entire database. The entire database was offline during the restore. Before any part of the database becomes online, all data must be restored to the same point, that is, all parts of the database are at the same point in time and there are no uncommitted transactions.

Under the full recovery model, the database can be restored to a specific point in time. The point in time can be the latest available backup, a specific date and time, or a marked transaction.

Restore complete database steps

In general, restoring a database to the point of failure is divided into the following steps:

1. Back up the active transaction log (called the tail log). This creates a tail-log backup. If the activity log is not available, all transactions in that part of the log will be lost.

Note: under the full recovery model and bulk-logged recovery model, if you want to restore data to the point of failure, you must back up the active transaction log.

two。 Restore the latest full database backup without restoring the database. (with norecovery)

3. If there is a differential backup, restore the latest differential backup data without restoring the database. (with norecovery)

4. Starting with the first transaction log backup created after restoring the backup, use norecovery to restore the logs in turn.

5. Restore the database (RESTORE DATABASE database_name WITH RECOVERY). This step can also be used in conjunction with restoring the last log backup.

6. A database full restore can usually be restored to a point in time or marked transaction in a log backup. However, under the bulk-logged recovery model, point-in-time recovery cannot occur if the log backup contains bulk changes.

All of the operations below in this article are based on previous backups.

SQL Server Management Studio for database full backup and recovery

Back up the tail of the log

Under the full or bulk-logged recovery model, using the end of the log to capture active transaction logs that have not been backed up prevents work loss and ensures the integrity of the log chain. at the same time, you can also restore the data to the point of failure.

A tail-log backup, like any other log backup, is performed using the BACKUP LOG statement. It is recommended that you perform a tail-log backup in the following situations

If the database is online and you plan to restore the database, use WITH NORECOVERY to back up the tail of the log before starting the restore operation

BACKUP LOG database name TO WITH NORECOVERY

Note: to prevent errors, you must use the NORECOVERY option.

Note: use NORECOVERY whenever you are ready to continue with the restore operation on the database. NORECOVERY puts the database into a restored state. This ensures that the database does not change after the tail-log backup.

The log is truncated unless either the NO_TRUNCATE or COPY_ONLY option is also specified.

NO_TRUNCATE is not recommended unless the database is corrupted

2) if the database is offline and cannot be started.

Attempt to perform a tail-log backup. Since no transaction occurs at this time, WITH NORECOVERY is optional. If the database is corrupted, use WITH CONTINUE_AFTER_ERROR, as follows:

BACKUP LOG database name TO

WITH CONTINUE_AFTER_ERROR

If the database is corrupted (for example, the database cannot be started), the tail-log backup can succeed only if the log file is not damaged, the database is in a state that supports tail-log backup, and it does not contain any bulk log changes.

For tail-log backups, please refer to: https://technet.microsoft.com/zh-cn/library/ms179314(v=sql.105).aspx

Create a new target database

Start-- all programs-- Microsoft SQL Server 2008 R2--SQL Server Management Studio, open and log in to mssql

Expand the server-database, right-click the database, and select New Database (in this case, the database name of the previous backup is Pine9_2016).

Modify the database storage path and folder, it is recommended that the disk partition letter and folder name can be the same as the original database, reduce the trouble in the recovery process, of course, inconsistent is also possible. I modified it to be exactly the same as the path of the original server.

Remember that the database name here must be the same as the previous database name, otherwise, after the database is restored, the developer needs to adjust the configuration and increase the complexity.

Click the button to confirm.

At this point, you have seen the Pine9_2016, which means that the new database has been created.

Full backup recovery

Click to select Pine9_2016, right-click it, navigate to Task-restore-Database, and open

Select General-- restore Source-- Select, source device, click Browse on the right

Click the add button

Locate the location of backup files

Modify the selected path and file name. In this example, the backup device is stored on a shared server in the local area network, so the modified path is shown below:

File name, select the file that is fully backed up.

Click OK

Click OK

Select the backup set for restore, and check the file backup device for full backup, as shown below:

Switch to the options page in the upper left corner

Check the overwrite existing database and transactions that do not roll committed without performing any action on the database. You can restore other transaction logs (restore with norecovery)

Overwrite existing database

Specifies that the restore operation overwrites the database file that currently uses the database name you specified. Even if you restore to an existing database name from another database, the existing database file will be overwritten. Selecting this option is equivalent to using the REPLACE option in the RESTORE statement (Transact-SQL), and it is recommended to use it with caution because this example is a new database. I chose this option to ensure database consistency, keeping in mind that subsequent differential backup restores should not be selected.

Keep replication settin

When you restore a published database to a server other than the server where the database was created, the replication settings are retained. This option applies only if the database is replicated when the backup is created.

This option is available only if you select the rollback of uncommitted transactions to make the database available for use

Prompt before restoring each backup

Specifies that after each backup is restored, the continue restore dialog box is displayed, asking if you want to continue the restore order. It is usually used for backup pauses, changing tapes or other media.

Restrict access to the restored database

Make the restored database available only to members of db_owner, dbcreator, or sysadmin.

Selecting this option is equivalent to using the RESTRICTED_USER option in a RESTORE statement

Click the button "OK" to enter the data recovery state.

The "executing" in the lower left corner of the image above appears. At this time, we just need to wait until the following prompt appears:

At this point, the full backup and recovery of the database has been completed, does it mean that the database has been restored? some novices think that full backup is equivalent to restoring all data and logs, experienced administrators, will find that this is not enough, full backup only represents the data of a certain point in the past, not the latest data, to really completely restore the data You need to perform a differential backup restore after a full backup.

Differential backup recovery

Click on the server, expand the database, right-click Pine9_2016 (restoring), and task-restore-Database

Locate the "General" option page, select "Source device", and click the browse button on the right

Click add

The selected path is the location of your database backup; the file name is the backup file name of the differential backup. The differential backup suffix defined here is .diff, or you can define it yourself.

Remember, the differential backup file here must be the latest differential backup file.

Click "OK"

Click OK to select the backup set for restore, and check the backup device.

In the upper left corner, navigate to the options page and select "do nothing on the database and will not roll uncommitted transactions. You can restore other transaction logs (A). (Restore with norecovery)"

Click OK

Click "OK"

As described earlier, the recovery steps are followed by a transaction log backup and restore.

Transaction log backup restore

Click the server, expand the database, right-click Pine9_2016, and then task-restore-transaction log

General option page, select "from File or Tape" and click the browse icon on the right

Click add

The selected path is the path where the backup file exists

The file name is the file name of the transaction log backup. Remember, the backup file here is the first transaction log after the differential backup above, and restore it one by one.

Click OK

Click OK

Select the upper left corner, options page

Click OK

At this point, the recovery has been completed.

Note: for transaction logs, all transaction logs after the latest differential backup need to perform the above steps to ensure data integrity.

The recovery method of the tail log is the same as that of the transaction log.

Restore the database

At this point, there is only one last step left to restore the database.

Execute the following statement:

Use master

RESTORE DATABASE Pine9_2016 WITH RECOVERY

Go

Note: Pine9_2016 is the database name, according to your own actual environment, change to your own actual environment database name.

At this point, the recovery of the database is complete, and you can access the system and access the database normally.

T-SQL for database full backup and recovery

First of all, we need to create a new database, which I won't explain here.

The basic steps of restore

1) RESTORE DATABASE database FROM full database backup WITH NORECOVERY

2) RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY

3) RESTORE LOG database FROM log_backup WITH NORECOVERY

Repeat this restore log step for each other log backup. (the same is true of including the ending log)

4) RESTORE DATABASE database WITH RECOVERY

Here I will directly post the SQL statement of the database, modify it to what you actually need, and then paste it into the query manager and execute it directly.

Full backup recovery

RESTORE DATABASE [Pine9_2016] FROM DISK = N'\\ 192.168.10.78\ mssql_backup\ Pine9_2016_backup_2016_08_06_023000_9215269.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

GO

Differential backup recovery

RESTORE DATABASE [Pine9_2016] FROM DISK = N'\\ 192.168.10.78\ mssql_backup\ Pine9_2016_backup_2016_08_07_023001_2750767.diff' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

Transaction log backup recovery

RESTORE LOG [Pine9_2016] FROM DISK = N'\\ 192.168.10.78\ mssql_backup\ Pine9_2016_backup_2016_08_06_030000_6901599.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

For all transaction logs, repeat the above steps. It's the same with the end log.

Restore the database

Use master

RESTORE DATABASE Pine9_2016 WITH RECOVERY

Go

If all goes well, congratulations, the database recovery is complete.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.