Database recovery of MSSQL enterprise actual combat

The purpose of a full database restore is to restore the entire database. The entire database is 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 time and no uncommitted transactions exist.

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

To restore the full database step

Typically, recovering a database to a point of failure is divided into the following steps:

1. Back up the active transaction log (called tail-log). This action creates a tail-log backup. If the activity log is not available, all transactions in the log section are lost.

Note: Under the full and bulk-logged recovery models, if you are recovering data to a point of failure, you must back up the active transaction log.

2. Restore the most recent full database backup without recovering the database. (WITH NORECOVERY)

3. If there is a differential backup, restore the most recent differential backup data without recovering the database. (WITH NORECOVERY)

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

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

6. A full database restore can typically revert to a point in time or a marked transaction in a log backup. However, in the bulk-logged recovery model, point-in-time recovery is not possible if a log backup contains a bulk change.

All of the following actions are made in the case of a previous backup.

Database full backup Restore SQL Server Management Studio

Back up the tail of the log

Under the full or bulk-logged recovery model, the end of the log is used to capture active transaction logs that have not yet been backed up, to prevent work loss and to ensure the integrity of the log chain, and to restore data to the point of failure.

The tail-log backup is similar to any other log backup, and is executed using the BACKUP LOG statement. We recommend that you perform a tail-log backup in the following scenarios

If the database is online and you plan to perform a restore operation on the database, use the with NORECOVERY to back up the tail of the log before you begin the restore operation

Backup LOG database name to < back up device > with NORECOVERY

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

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

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

It is not recommended to use no_truncate unless the database is compromised

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

Attempt to perform a tail-log backup. Because no transactions occur at this time, with NORECOVERY is optional. If the database is compromised, use the with Continue_after_error as follows:

Backup LOG database name to < back up device > with Continue_after_error

If the database is compromised (for example, the database cannot be started), the tail-log backup succeeds only if the journal file is not compromised, the database is in a state that supports the tail-log backup, and does not contain any bulk-logged changes.

For a tail-log backup, 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 R2--sql server Management Studio, open login MSSQL

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image002[9] "border=" 0 "alt=" clip_image002[9] "src=" http://s3.51cto.com/wyfs02/M00/85 /a2/wkiol1eqxxjg8os9aackzjqfrd8896.jpg "" 530 "height=" 351 "/>

Expand the server---database, right-click the database, and select New Database (the database name for the previous backup in this example is pine9_2016).

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image004[6] "border=" 0 "alt=" clip_image004[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiom1eqxxnyyeunaac3a9cg5nc683.jpg "" 535 "height=" 327 "/>

Modify the database storage path and folder, the recommended disk partition drive letter and folder name can be consistent with the original database, reduce the recovery process of trouble, of course, the inconsistency is also possible. I changed it to the same path as the original server.

Remember the database name here, must be the same as the previous database name, otherwise, after the database recovery, development will need to adjust the configuration, increase the complexity.

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image006[6] "border=" 0 "alt=" clip_image006[6] "src=" http://s3.51cto.com/wyfs02/M02/85 /a3/wkiom1eqxxqq47ooaab4vzkbzi0838.jpg "" 554 "height=" 271 "/>

Click on the button to confirm.

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image008[6] "border=" 0 "alt=" clip_image008[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiom1eqxxugekoqaab5hfe93tw085.jpg "" 554 "height=" 279 "/>

At this point, you have seen the pine9_2016, representing the database has been newly completed.

Full backup Recovery

Click to select pine9_2016, right click on it, navigate to task--restore--database, open

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image010[6] "border=" 0 "alt=" clip_image010[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiol1eqxxvsh2r9aacrewivjaw420.jpg "" 554 "height=" 447 "/>

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

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image012[6] "border=" 0 "alt=" clip_image012[6] "src=" http://s3.51cto.com/wyfs02/M01/85 /a3/wkiol1eqxxya7ftraactwcgawz4396.jpg "" 554 "height=" 430 "/>

Click the Add button

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image014[6] "border=" 0 "alt=" clip_image014[6] "src=" http://s3.51cto.com/wyfs02/M01/85 /a3/wkiom1eqxx3cffxiaadbrpfltqw030.jpg "" 543 "height=" 421 "/>

Locate the location of the backup file,

Modify the selected path and file name, in this case the backup device is stored on a shared server in the local area network, so modify the path such as:

File name, select the full backup files.

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image016[6] "border=" 0 "alt=" clip_image016[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiom1eqxx-xmjqsaadxckc9afa328.jpg "" 546 "height=" 418 "/>

Click OK

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image018[6] "border=" 0 "alt=" clip_image018[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiol1eqxx_bjxafaabr-rqsilu319.jpg "" 554 "height=" 358 "/>

Click OK

Select the backup set for restore, tick the full backup file backup device, such as:

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image020[6] "border=" 0 "alt=" clip_image020[6] "src=" http://s3.51cto.com/wyfs02/M01/85 /a3/wkiol1eqxydgarpiaacg32ns9ps967.jpg "" 554 "height=" 444 "/>

Switch to the upper-left corner Options page

Tick overwrite existing database and do not perform any operations on the database, the committed transaction will not be rolled. Other transaction logs can be restored (restore WITH NORECOVERY)

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image022[6] "border=" 0 "alt=" clip_image022[6] "src=" http://s3.51cto.com/wyfs02/M02/85 /a3/wkiom1eqxygcv2cvaada1wpozni694.jpg "" 554 "height=" 392 "/>

Overwrite an existing database

Specifies that the restore operation overwrites the database file that is currently using the database name that you specified. Even if you restore from another database to an existing database name, the existing database files will be overwritten. Selecting this option is equivalent to using the REPLACE option in the RESTORE statement (Transact-SQL), and it is recommended that you use it sparingly, because this example is a new database, and for the consistency of the database, I select this item, remembering that subsequent differential backup recoveries should not be selected.

Keep Replication settings

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

This option is available only when you select the roll back uncommitted transactions to a state that the database is available to use option

Prompt before restoring each backup

Specifies that after each backup is restored, the Continue Restore dialog box appears, asking if you want to continue with the restore sequence. Typically used for backup pauses, replacing tapes or other media.

Restricting access to restored databases

Make the restored database available only to members of the 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 start entering data recovery status.

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image024[6] "border=" 0 "alt=" clip_image024[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiom1eqxylxbdxraacickyf9hu724.jpg "" 554 "height=" 451 "/>

Appears in the bottom left corner of the "executing", this time, we only need to wait quietly until the following prompt appears:

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image026[14] "border=" 0 "alt=" clip_image026[14] "src=" http://s3.51cto.com/wyfs02/M01/ 85/a3/wkiom1eqxypac-ssaaaqboemerq202.jpg "" 554 "height="/>

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image028[6] "border=" 0 "alt=" clip_image028[6] "src=" http://s3.51cto.com/wyfs02/M02/85 /a3/wkiom1eqxystcs7aaacbgbwptdq679.jpg "" 554 "height=" 282 "/>

To this, the database full backup recovery has been completed, is not the representative database has been restored, some novice think the full backup is equivalent to restore all the data and logs, experienced administrators, will find that this is not enough, the full backup is just a point of time in the past data, not the latest data, To truly fully recover the data, you need to perform a differential backup recovery after a full backup.

Differential backup Recovery

Click on the server, expand the database, right-click pine9_2016 (Restoring), task--Restore--database

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image030[6] "border=" 0 "alt=" clip_image030[6] "src=" http://s3.51cto.com/wyfs02/M02/85 /a3/wkiol1eqxyxjes1kaadbjp17htw263.jpg "" 553 "height=" 406 "/>

Locate the "General" Options page, select "Source Device", click on the right of the Browse small button, 650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image032[6] "border=" 0 "alt=" clip_image032[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiom1eqxywrve9uaaadg19mvyo710.jpg "" Height= "/>"

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image034[6] "border=" 0 "alt=" clip_image034[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiol1eqxyai8txaaacj0j7lxmk817.jpg "" 554 "height=" 380 "/>

Click Add

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image036[6] "border=" 0 "alt=" clip_image036[6] "src=" http://s3.51cto.com/wyfs02/M01/85 /a3/wkiom1eqxyewtjg3aadekf_s0gu731.jpg "" 549 "height=" 462 "/>

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

Remember, here's the differential backup file, be sure if the latest a differential backup file for Austria.

Click "OK"

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image038[6] "border=" 0 "alt=" clip_image038[6] "src=" http://s3.51cto.com/wyfs02/M02/85 /a3/wkiom1eqxyji2cyaaabxq_dpnxi521.jpg "" 554 "height=" 344 "/>

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

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image040[6] "border=" 0 "alt=" clip_image040[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiom1eqxymdgbmqaachi0rzmgc826.jpg "" 554 "height=" 444 "/>

Upper-left corner, navigate to the Options Options page, select Do not perform any operations on the database, and do not roll out uncommitted transactions. You can restore other transaction logs (A). (Restore with NORECOVERY) "

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image042[8] "border=" 0 "alt=" clip_image042[8] "src=" http://s3.51cto.com/wyfs02/M02/85 /a3/wkiom1eqxyrti_mgaadiblxj8oa911.jpg "" 554 "height="/>

Click OK

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image044[6] "border=" 0 "alt=" clip_image044[6] "src=" http://s3.51cto.com/wyfs02/M02/85 /a3/wkiol1eqxyuhfwnsaacol3pajyy220.jpg "" 553 "height=" 340 "/>

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image026[15] "border=" 0 "alt=" clip_image026[15] "src=" http://s3.51cto.com/wyfs02/M01/ 85/a3/wkiol1eqxyvwm6bmaaaqboemerq378.jpg "" 554 "height="/>

Click "OK"

The previous article has described, the steps to recover, next, a transaction log backup to restore.

Transaction log backup Restore

Click on the server, expand the database, right-click pine9_2016, Task-Restore--transaction log,

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image046[6] "border=" 0 "alt=" clip_image046[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiom1eqxyzaopzoaacmrly4ghc493.jpg "" 553 "height=" 332 "/>

General Options page, select "From File or tape", click the right navigation icon,

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image048[6] "border=" 0 "alt=" clip_image048[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiol1eqxy3ssd9raac7dbvjree386.jpg "" 554 "height=" 449 "/>

Click Add

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image050[7] "border=" 0 "alt=" clip_image050[7] "src=" http://s3.51cto.com/wyfs02/M01/85 /a3/wkiom1eqxy_iudjiaadhvzoicp4113.jpg "" 543 "height=" 474 "/>

The path selected, which is the path to which the backup file exists

File name, which is the name of the transaction log backup, remember, here the backup file, is the first transaction log after the differential backup above starts, one by one to recover.

Click OK

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image052[6] "border=" 0 "alt=" clip_image052[6] "src=" http://s3.51cto.com/wyfs02/M02/85 /a3/wkiom1eqxy-q8ficaabqsiccwrw217.jpg "" 554 "height=" 341 "/>

Click OK

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image054[6] "border=" 0 "alt=" clip_image054[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiol1eqxzdia0ataacj1ki9054541.jpg "" 554 "height=" 441 "/>

Select the upper-left corner, Options page

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image056[6] "border=" 0 "alt=" clip_image056[6] "src=" http://s3.51cto.com/wyfs02/M01/85 /a3/wkiol1eqxzgbmmvnaachrt5dybm166.jpg "" 553 "height=" 414 "/>

Click OK

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image058[6] "border=" 0 "alt=" clip_image058[6] "src=" http://s3.51cto.com/wyfs02/M02/85 /a3/wkiom1eqxzkzoo5kaacsfnwtjr0802.jpg "553" height= "/>"

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; margin:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image026[16] "border=" 0 "alt=" clip_image026[16] "src=" http://s3.51cto.com/wyfs02/M02/ 85/a3/wkiol1eqxzpqp_9uaaaqboemerq045.jpg "" 554 "height="/>

At this point, the recovery is complete.

Note: For transaction logs, all transaction logs after the most recent differential backup require the above steps to ensure the integrity of the data.

The end-of-log recovery method is the same as the transaction log recovery method.

Recovering a Database

Here, there is only one final step, to restore the database.

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clip_image060[6] "border=" 0 "alt=" clip_image060[6] "src=" http://s3.51cto.com/wyfs02/M00/85 /a3/wkiom1eqxzsxvp0taabsh0-vpp4963.jpg "" 553 "height=" 279 "/>

Execute the following statement:

Use master

RESTORE DATABASE pine9_2016 with RECOVERY;

Go

Note: pine9_2016 is the name of the database, based on your own actual environment, modified to your own real-world database name.

To this, the database recovery, is complete, can access the system, the normal access to the database

T-SQL for database full backup recovery

The first thing to do is to create a new database, where I no longer explain

Basic Steps for Restore

1) RESTORE database from the full database backup with NORECOVERY;

2) RESTORE 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. (same as with end-of-log)

4) RESTORE database with RECOVERY;

Here I directly paste the database SQL statements, we modify the actual needs of their own, and then paste into the query manager directly to execute.

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

Repeat the above steps for all transaction logs. Including the end log, too.

Recovering a Database

Use master

RESTORE DATABASE pine9_2016 with RECOVERY;

Go

If it goes well, congratulations, the database is back to completion.

Database recovery of MSSQL enterprise actual combat