[Distinct] Full SQL backup and Restoration

From: http://blog.sina.com.cn/s/blog_59e866610100dld3.html

For databases, backup and restoration are undoubtedly very important. Today, I learned and operated on my machine and summarized it.

I used 2005. Today I will summarize the complete database backup and restoration.

1. Create a backup device first

Exec sp_addumpdevice 'disk', 'myback1', 'd: \ backup \ myback1.bak'

Go

Exec sp_addumpdevice 'disk', 'myback2', 'd: \ backup \ myback2.bak'

Go

2. Complete backup database Test

Backup database Test
To myback1, myback2
With init, name = 'test _ fullbackup_20090521'

In the with option, there are two important options: init and format. Using these options will damage the existing backup on the backup device, and the two cannot be used at the same time. The option name specifies the backup name. We recommend that you use it. Otherwise, you will not know which one is to be restored. You can also use the medianame option to specify the name of the backup media, which is generally not required.

3. Differential backup database Test

After the database is changed, differential backup can be performed for the database.

Backup database Test
To myback1, myback2
With differential, name = 'test _ fulldiff_20090521'

4. Back up database test logs

Backup Log Test
To myback1, myback2
With name = 'test _ logbackup_20090521'

Logs and data can be backed up to the same backup device. However, for performance and other factors, logs and data are generally backed up to different devices.

After the last log is backed up, if you have made any wrong changes or changes, you have to restore the data. If you restore the data directly, an error will be reported,

Tip:The log tail of the Database "test" has not been backed up. If the log contains jobs you do not want to lose, use backup log with norecovery to back up the log. Use the with replace or with stopat clause of the restore statement to only overwrite the log Content.

Therefore, before restoring a log, back up the tail log first,In case of system failure, back up the tail log immediately to help restore the status to the fault point.

Backup Log Test
To myback1, myback2
With name = 'test _ log_tailbackup_20090521'

All the above backups will be recorded in the system table backupset in the system database MSDB. You can use the following statement to view them:

Use MSDB
Go
Select backup_set_id, media_set_id, position, name, type
From backupset

It is the result of my tests. Position indicates the position of the file, which is obtained during restoration. name indicates the backup name, type indicates the Backup Type, D indicates the database, and I indicates the difference, L indicates the log.

4. Restore database Test

Note that you want to restore the database status to the status before the backup log,Therefore, do not restore the tail log during restoration. Otherwise, errors may occur, or no restoration is performed.

Use master
Go
Restore database test # restore the complete database backup. The file location is 1. The specified norecovery allows further data restoration.
From myback1, myback2
With file = 1, norecovery
Go
Restore database test # restore differential backup
From myback1, myback2
With file = 2, norecovery
Go
Restore Log Test # restore the log. The number of log backups before the last log must be restored.
From myback1, myback2
With file = 3, recovery # recovery restores the database to the online state.
Go

Full database backup + differential backup + Log backup are commonly used in practical applications. The backup policy is generally as follows:

(1) perform a full backup of the database every Saturday night.

(2) Differential Database Backup every Wednesday night.

(3) Back up transaction logs every 30 minutes.

You can use the database maintenance plan Wizard to design to automatically execute this backup policy. During backup, you should ensure that the backup has several copies and are stored in different locations. In addition, you should test the backup availability to ensure that no errors occur during restoration.