SQL Server 2008 Backup Recovery combat

A description of the content

The most important thing in SQL Server database is database backup and restore, and when the database is catastrophic, you need to have the best recovery plan, recover the data and restore the solution. For databases that have completed database backups, you need to periodically organize recovery walkthroughs so that you can recover quickly when a disaster fails.

The following describes the backup strategy development, recovery plan, and restore plan.

There are several combinations to restore (choose according to your actual situation):

1. Full recovery
2. Full recovery + differential recovery
3. Full recovery + differential recovery + log Transaction Recovery

Two-strategy development

2.1 Condition without transaction log

The database is fully prepared once a day because the amount of database data is not very large

The database performs a differential backup every half hour

2.2 Database opens Transaction Mode

Database weekly Sunday 1 o'clock in the morning, full database

Database 18-point differential backup per day

Database performs a log transaction backup every 30 minutes

Three backup steps (Job creation)

1.1 Full-scale backup

Enter first Name: Auto_backup_sqlserver, click Confirm, enter the job plan inside.

650) this.width=650; "title=" clip_image001 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M02/8F/10/ Wkiol1jskqmqigdlaacincob_ui800.png "" 563 "height=" 505 "/>

Access--Maintenance plan--auto_backup_sqlserver "add sub-plan"

Fill in the following ways

Name: Km_full

Description: Fully prepared Km_full_

Plan: You need to select a specific backup strategy, refer to Next

650) this.width=650; "title=" clip_image002 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M00/8F/10/ Wkiol1jskq7i9dthaaejdwru_zc179.png "" 949 "height=" 715 "/>

The job schedule properties are set as follows:

Name: Backup_full

Schedule type: Repeat execution

Frequency: daily, execution interval 1 days

Daily frequency: Executes once, time is 1 points

650) this.width=650; "title=" clip_image003 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M01/8F/10/ Wkiol1jskrdduatcaacwqgxc0uu775.png "" "height=" 735 "/>

When the job properties, click Confirm, select Sub-schedule: km_full, drag the "Backup Database task" to the sub-scheduled task form, and then the "Back Up Database Task" task box, the Backup Database task setting option appears

Connection: Local Server connection

Backup type: Full

Database: After selecting the database to be backed up, this will change to a specific database typeface

Backup directory: Select the folder of the backup directory, note: This is fully prepared, can write full words, easy to clean up

Tick Verify backup integrity

650) this.width=650; "title=" clip_image004 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M02/8F/10/ Wkiol1jskrsycyf1aagnsxi1sra403.png "" 1373 "height=" 774 "/>

1.2 Record cleanup

To add a cleanup history plan, select Cleanup history tasks into the form, such as

650) this.width=650; "title=" clip_image005 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M00/8F/10/wKioL1jSkRfhsyC_ Aaeq2bbwrfq741.png "" 1159 "height=" 737 "/>

Choose to clear the priority of the backup history, click Backup Database Task Selection--"Add precedence constraint (P)"

650) this.width=650; "title=" clip_image006 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M01/8F/10/ Wkiol1jskrjzbok4aabtpdjtgmy468.png "" 810 "height=" 433 "/>

Back up as needed, and then delete the history, as

650) this.width=650; "title=" clip_image007 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image007 "src=" http://s3.51cto.com/wyfs02/M02/8F/10/ Wkiol1jskrna9v2oaabd7joxq1s666.png "" 854 "height=" 489 "/>

Two-machine purge history task, the option to clear the history task appears, and the history deletion after 8 weeks is set as follows

650) this.width=650; "title=" clip_image008 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image008 "src=" http://s3.51cto.com/wyfs02/M00/8F/10/ Wkiol1jskrvbj3o2aacgumr-sva473.png "" 1085 "height=" 572 "/>

1.3 Expiration Data cleanup

Add Cleanup maintenance tasks, drag and remove maintenance tasks from the panel inside the main form, such as:

650) this.width=650; "title=" clip_image009 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image009 "src=" http://s3.51cto.com/wyfs02/M01/8F/10/ Wkiol1jskr3hv-riaabehzwnld0626.png "" 977 "height=" 494 "/>

Double or mail Click to clear maintenance tasks, such as

650) this.width=650; "title=" clip_image010 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image010 "src=" http://s3.51cto.com/wyfs02/M01/8F/12/ Wkiom1jsksdtzxkhaackcegwakm231.png "" 963 "height=" 654 "/>

、

The Purge Maintenance Task Property Settings dialog box appears

Settings folder: C:\bak

File extension: Full

Delete Files: 4 weeks

650) this.width=650; "title=" clip_image011 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image011 "src=" http://s3.51cto.com/wyfs02/M02/8F/12/ Wkiom1jsksgjhrxxaaceyexhln0323.png "" 982 "height="/>

1.4 Differential backup

Click Add Sub-plan, the following dialog box appears:

Name: Km_diff

Description: Km_diff

Schedule: Need to click then Calendar control settings

650) this.width=650; "title=" clip_image012 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image012 "src=" http://s3.51cto.com/wyfs02/M02/8F/10/ Wkiol1jsksljifryaacrygjppwa108.png "" 842 "height=" 651 "/>

Click Calendar Space, the Configuration dialog box for differential backup appears

Schedule type: Repeat execution

Execution frequency: Daily

Execution Time: 1:00:00

650) this.width=650; "title=" clip_image013 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image013 "src=" http://s3.51cto.com/wyfs02/M00/8F/10/ Wkiol1jsksaywreeaaebr0zyj2c316.png "" 1160 "height=" 765 "/>

In the main form, drag the Backup database task, then right-click on Edit, the following dialog box appears, after the configuration is completed, click OK

Backup Type: diff

Database: Select the database that requires a differential backup, and when you are finished, a specific database appears here

Tick create backup file for each database

backup file extension: diff

Tick Verify backup integrity

650) this.width=650; "title=" clip_image014 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image014 "src=" http://s3.51cto.com/wyfs02/M01/8F/12/ Wkiom1jsktech1jjaagazvbohbu054.png "" 1290 "height=" 740 "/>

1.5 Differential backup record cleanup

Drag the Clear History task from the Toolbox, right-click Edit, save for 8 weeks as follows

650) this.width=650; "title=" clip_image015 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image015 "src=" http://s3.51cto.com/wyfs02/M02/8F/12/ Wkiom1jsktrxwmo8aaftqadczkk771.png "" 1284 "height=" 745 "/>

After you set the backup database task to successful, then clear the history, right-click the Backup Database task, select Add Precedence constraint

650) this.width=650; "title=" clip_image016 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image016 "src=" http://s3.51cto.com/wyfs02/M01/8F/12/ Wkiom1jsktvcl4gcaabu2wnntay368.png "" 913 "height=" 631 "/>

Set up control flow

Connect from: Back Up Database tasks

Connect to: Clear History task

650) this.width=650; "title=" clip_image017 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image017 "src=" http://s3.51cto.com/wyfs02/M00/8F/12/wKiom1jSkT2TS_ Cyaaekong3biy362.png "" 1147 "height=" 767 "/>

After you set the control flow to complete, an arrow appears pointing to the

650) this.width=650; "title=" clip_image018 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image018 "src=" http://s3.51cto.com/wyfs02/M01/8F/12/ Wkiom1jskucze91laaecrqpakp4362.png "" 1063 "height=" 714 "/>

1.6 Differential backup expired data cleanup

After you set the history data expiration purge, and then set the expiration data cleanup, drag the Purge maintenance task from the toolbox to the main form

Settings directory: C:\bak

Set File extension: diff

Tick the Include and sub-folders

Reserved: 4 weeks

650) this.width=650; "title=" clip_image019 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image019 "src=" http://s3.51cto.com/wyfs02/M02/8F/12/ Wkiom1jskubqrr3paagdasglioy900.png "" 1318 "height=" 772 "/>

Set the control flow, select clear maintenance tasks from the toolbox, then right-click Edit Purge History task, in control flow, select

Connect from: Clear History task

Connect to: Clear maintenance tasks

650) this.width=650; "title=" clip_image020 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image020 "src=" http://s3.51cto.com/wyfs02/M02/8F/10/ Wkiol1jskujhsmtfaafhw0q_tb4430.png "" 1317 "height=" 785 "/>

After the setup is complete, such as:

650) this.width=650; "title=" clip_image021 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image021 "src=" http://s3.51cto.com/wyfs02/M02/8F/10/ Wkiol1jskuqgljtraafjsyvb6jq619.png "" 1334 "height=" 766 "/>

1.7 Transaction Log Backups

Click the Toolbox--Backup Database task--Right click Edit, the Backup Database task property card appears

Backup Type: Transaction log

Database: Select the data you want to back up

Tick create sub-directory for each database

File extension: trn

Tick Verify backup integrity

650) this.width=650; "title=" clip_image022 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image022 "src=" Http://s3.51cto.com/wyfs02/M00/8F/12/wKiom1jSkU7RslVeAAFp_ K-okue952.png "" 1060 "height=" 810 "/>

1.8 Transaction Log History cleanup

From the Toolbox-Drag Clear History task-right-click Edit, keep 10 weeks according to the following settings, priority is to back up the database task first, then perform cleanup database records (Control flow priority setting, reference Backup Database section)

650) this.width=650; "title=" clip_image023 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image023 "src=" http://s3.51cto.com/wyfs02/M01/8F/10/ Wkiol1jskvyaca77aafrrazjiuw756.png "" 1195 "height=" 775 "/>

1.9 Transaction log Expiration data cleanup

From the Toolbox, drag the Purge maintenance task, right-click Edit, set priority after the following settings, first history cleanup, and then clear maintenance tasks

Folder: C:\bak

Extension: trn

Retention Date: 2 weeks

650) this.width=650; "title=" clip_image024 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image024 "src=" http://s3.51cto.com/wyfs02/M02/8F/10/ Wkiol1jskv-gyp1haaf6362x8da366.png "" 1243 "height=" 742 "/>

Four-Step Restore

2.1 Full-Volume restore

When full recovery, right-click the database task that needs to be restored--restore--database/file or filegroup, as

650) this.width=650; "title=" clip_image025 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image025 "src=" http://s3.51cto.com/wyfs02/M00/8F/10/ Wkiol1jskwhxwdjqaadidpnqvmw316.png "" 528 "height=" 708 "/>

After the restore is selected, the Restore Database tab interface appears, select the "Source Device" path, for example, the component is the database type is complete, tick this restore database record, such as:

650) this.width=650; "title=" clip_image026 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image026 "src=" http://s3.51cto.com/wyfs02/M01/8F/12/ Wkiom1jskwsxm6qjaae2zqm8zdy092.png "" 1033 "height=" 822 "/>

After setting up the general, select the option, here:

Tick existing database

Choose to roll back the committed transaction so that the database is in a ready-to-use state. Unable to restore other transaction log (L)

650) this.width=650; "title=" clip_image027 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image027 "src=" http://s3.51cto.com/wyfs02/M01/8F/12/wKiom1jSkWfgJb6gAAFD_ 3il70a939.png "" 1025 "height=" 844 "/>

Problem, when the following error occurs, indicating that someone is using the database, there is no way to restore the database, this time, the database needs to be set to single-user mode.

650) this.width=650; "title=" clip_image028 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image028 "src=" http://s3.51cto.com/wyfs02/M02/8F/12/ Wkiom1jskwmrkco8aadjbqdsidm891.png "" 1043 "height=" 722 "/>

Setting the database to single-user mode

Right-click on "Database Properties"--"options"

Restricted access: Single_user

650) this.width=650; "title=" clip_image029 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image029 "src=" http://s3.51cto.com/wyfs02/M02/8F/10/ Wkiol1jskwuhtfa0aafecxfv8nu856.png "" 1190 "height=" 839 "/>

2.2 Differential Restore

When you restore a differential, you need to be aware that when you restore a complete database and restore the full database, you need to be careful to select "

Do not perform any operations on the database and do not roll back uncommitted transactions. You can restore other transaction logs. (RESTORE with NORECOVERY)

"

650) this.width=650; "title=" clip_image030 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image030 "src=" http://s3.51cto.com/wyfs02/M00/8F/12/ Wkiom1jskzxx53maaaeuqc3mg9e616.png "" 1033 "height=" 820 "/>

After the complete database is restored, then right-click on the database, task-restore, in the source device, select the difference data file to be recovered, click the check

650) this.width=650; "title=" clip_image031 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image031 "src=" Http://s3.51cto.com/wyfs02/M00/8F/10/wKioL1jSkZiwn_e1AAEg_ Ucj4zm264.png "" 1061 "height=" 818 "/>

Click on the tab, select according to the following requirements, finally click OK, you can complete the difference recovery.

650) this.width=650; "title=" clip_image032 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image032 "src=" http://s3.51cto.com/wyfs02/M01/8F/10/ Wkiol1jskaxcggn8aaesqipun1c197.png "" 1037 "height=" 812 "/>

2.3 Transaction Log Restore

The same steps as restoring the transaction log and restoring the full database, but when selecting the source device, you need to select the transaction log files that need to be recovered, as follows

650) this.width=650; "title=" clip_image033 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image033 "src=" http://s3.51cto.com/wyfs02/M01/8F/10/wKioL1jSkanxMyv_ Aaeizz6acwu596.png "" 1016 "height=" 828 "/>

Select Roll back uncommitted transactions so that the database is in a ready-to-use state. Other transaction logs (L) cannot be restored. (Restore with RECOVERY) ", indicating that there are no other differences or log backup files that need to be recovered. Here, because there are 2 more transaction log files to recover, the "do not perform any operations on the database and do not roll back uncommitted transactions." Other transaction logs can be restored "

Note: As long as you want to restore, be sure to select "Do not perform any operations on the database, do not roll back uncommitted transactions." Other transaction logs can be restored, or the above error will occur if you restore a differential or log backup.

650) this.width=650; "title=" clip_image034 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image034 "src=" http://s3.51cto.com/wyfs02/M02/8F/10/ Wkiol1jskayyqogbaaevwzxxjky365.png "" 1029 "height=" 830 "/>

It is necessary to note that if there is a log transaction file that needs to be recovered later, the state of the database will be: (Restoring ...)

650) this.width=650; "title=" clip_image035 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; margin:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image035 "src=" http://s3.51cto.com/wyfs02/M00/8F/12/ Wkiom1jska3buu-3aabfr7mcps4839.png "" 334 "height=" 329 "/>

When all the log transaction files are restored, the last transaction log needs to be selected: "Roll back uncommitted transactions so that the database is in a ready-to-use state." Other transaction logs (L) cannot be restored. (RESTORE with RECOVERY) "

Then click on confirm and the recovery is complete.

650) this.width=650; "title=" clip_image036 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image036 "src=" http://s3.51cto.com/wyfs02/M01/8F/12/ Wkiom1jskb3szb6taaext7rrriu459.png "" 1025 "height=" 818 "/>

SQL Server 2008 Backup Recovery combat