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

A description of the content

The most important thing in sqlserver database is database backup and restore. When the database is destroyed catastrophically, it is necessary to have the best recovery plan, recovery data and recovery scheme. For databases that have been backed up, recovery drills need to be organized regularly so that they can be quickly restored when disaster failures occur.

The following describes backup strategy development, recovery planning, and restore planning.

There are mainly the following combinations (according to their actual situation):

Full Recovery Full Recovery + Differential Recovery Full Recovery + Differential Recovery + Log Transaction Recovery

II. Strategy development

2.1 No transaction log

Because the database data volume is not very large, the database is fully prepared once a day.

The database performs a differential backup every half hour

2.2 The database has transaction mode turned on.

Database Every Sunday at 1:00 a.m., fully equipped database

Database differential backup at 18:00 every day

Database performs log transaction backup every 30 minutes

Three backup steps (job creation)

1.1 full backup

Enter the name: auto_backup_sqlserver, click OK to enter the job plan.

Go to--> Maintenance Plan--> auto_backup_sqlserver --> Add Subplan

Fill in as follows

Name: km_full

Description: km_full_

Plan: You need to select a specific backup strategy. Refer to the next step.

Job schedule attributes are set as follows:

Name: backup_full

Plan type: repeated execution

Frequency: Daily, 1 day between executions

Frequency per day: once per day at 1 o'clock

When the job attribute is selected, click OK, select the subplan: km_full, drag Backup Database Task to the subplan task window, and then the dual-computer Backup Database Task task box appears, and the Backup Database Task Setting option appears.

Connection: Local Server Connection

Backup Type: Complete

Database: When you select the database you want to back up, this changes to specific database

Backup directory: Select the folder of the backup directory. Note: It is fully prepared here. You can write the word full to facilitate cleaning.

Check Verify backup integrity

1.2 record clearing

Add the cleaning history plan, select the cleaning history task, and enter it into the window, as shown below

Select the priority of clearing backup history, click Backup Database Task Select--"Add Priority Constraint"

Backup first as needed, and then delete history records, as shown below

The option of clearing history task will appear. Set it as follows to delete history after 8 weeks.

1.3 Expired Data Purge

To add a clear maintenance task, drag the clear maintenance task from the panel to the main window, as shown below:

Double machine or mail click Clear Maintenance Task, as shown below

、

The Clear Maintenance Task Properties Settings dialog box appears

Settings folder: c:\bak

File extension: full

Delete files: 4 weeks

1.4 differential backup

Click Add Subplan and the following dialog box appears:

Name: km_diff

Description: km_diff

Schedule: Need to click then calendar control settings

Click Calendar Space to open the Configuration dialog box for Differential Backup.

Plan type: repeated execution

Frequency of execution: daily

Execution time: 1:00:00

In the main window, drag the Backup Database task, and then right-click Edit, the following dialog box appears, after completing the configuration, click OK

Backup Type: Difference

Database: Select the database that needs differential backup. After selecting, the specific database will appear here.

Check Create backup file for each database

Backup file extension: diff

Check Verify backup integrity

1.5 Differential backup record cleanup

Drag the Clear History task from the toolbox, right-click it, and save it for 8 weeks according to the following configuration

After setting the backup database task successfully, clear the history, right-click the backup database task, and select Add Priority Constraint.

Set control flow

Connect From: Backup Database Task

Connect to: Clear History Task

After setting the control flow, an arrow will appear, as shown below.

1.6 Differential Backup Expired Data Purge

After setting up historical data expiration cleanup, then set up expired data cleanup, drag the cleanup maintenance task from the toolbox to the main form

Settings directory: c:\bak

Settings file extension: diff

Check Include and Subfolders

Retention: 4 weeks

To set the control flow, select Clear Maintenance Task from the Toolbox, then right-click Edit Clear History Task, in Control Flow, select

Link From: Clear History Task

Connect to: Clear Maintenance Tasks

After setting, as shown below:

1.7 transaction log backup

Click Toolbox-Backup Database Task-Right Click Edit, and the Backup Database Task Attribute Card appears

Backup Type: Transaction Log

Database: Select the data specified to be backed up

Check Create Subdirectory Per Database

File extension: trn

Check Verify backup integrity

1.8 Transaction log history cleanup

From Toolbox-Drag Clear History Task--Right click to edit, according to the following settings, keep for 10 weeks, priority is to backup database task first, then execute clean database record,(control flow priority setting, refer to backup database section)

1.9 Transaction log expiration data cleanup

Drag Clear Maintenance Task from the Toolbox, right-click Edit, set priority as follows, first clear history, then clear Maintenance Task

Folder: c:\bak

Extension: trn

Retention date: 2 weeks

Four reduction steps

2.1 total reduction

To fully restore, right-click the database task to be restored--Restore--Database/File or File Group, as shown below

After selecting Restore, the Restore Database tab interface will appear. Select the path of Source Device, as shown in the figure below. If the component is database type complete, check the record of Restore Database, as shown in the figure below:

After setting the general, select the options, here select:

Check existing database

Choose to roll back committed transactions to put the database in a usable state. Unable to restore other transaction logs (L)

Problem, when the following error occurs, it means that someone is using the database and there is no way to restore the database. At this time, you need to set the database to single user mode.

Set the database to single-user mode

Right-click "Database Properties"--"Options"

Restricted access: single_user

2.2 differential restore

When restoring the difference, you need to pay attention to restore the complete database first. When restoring the complete database, you need to pay attention to selecting "

No operations are performed on the database and uncommitted transactions are not rolled back. You can restore other transaction logs. (RESTORE WITH NORECOVERY)

"

After restoring the complete database, right-click Database, Task-Restore, select the difference data file to be restored in the source device, and click Check

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

2.3 transaction log restore

Restore transaction log and restore complete database steps are the same, only when selecting the source device, you need to select the transaction log file to restore, as follows

Select Roll back uncommitted transactions to make the database available for use. Unable to restore additional transaction logs (L). (RESTORE WITH RECOVERY)"indicates that there are no more differential or log backup files to restore. Here, since there are two transaction log files to recover later,"Do nothing to the database and do not roll back uncommitted transactions" is selected. Other transaction logs can be restored "

Note: As long as you want to restore, be sure to select "Do nothing to the database, do not roll back uncommitted transactions." Additional transaction logs can be restored,"otherwise restoring differential or log backups will result in the above error.

It should be noted that if there are log transaction files that need to be restored later, the status of the database will be: (Restoring..)

When all log transaction files have been restored, the last transaction log needs to select: "Roll back uncommitted transactions to make the database available for use." Unable to restore additional transaction logs (L).（RESTORE WITH RECOVERY）"

Then click OK and the 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.