Learning Notes (13)--knowledge points and considerations for database backup and restore

Learning Notes (13)--knowledge points and considerations for database backup and restore

First, the basic concept of backup and restore

1. Full backup: Full backup because the amount of data that needs to be backed up is large, it needs to be done in idle time and on a regular basis.

2, log backup: Log backup data volume is small, backup time for the last backup to this sub-period of data, every day can be backed up, or every hour can be backed up, according to the required backup.

3. Incremental backup (differential backup): Only the modified data is backed up and used in conjunction with log backups per hour, making it more efficient.

Second, backup equipment

1, in the storage of backup data, need to input the file path is very long, and every time to input, it is unavoidable trouble, so we first take the file path alias, that is, backup device, in order to write code later convenient.

2. Related statements

IF EXISTS (SELECT1From sys.backup_devices as BD WHERE bd.name ='Dp_edubase_fullbackup') EXEC sp_dropdevice'Dp_edubase_fullbackup'; EXEC sp_addumpdevice'DISK','Dp_edubase_fullbackup','C:\EduBase\Backup\Full\dp_EduBase_FullBackup.bak'; IF EXISTS (SELECT1From sys.backup_devices as BD WHERE bd.name ='Dp_edubase_diffbackup') EXEC sp_dropdevice'Dp_edubase_diffbackup'; EXEC sp_addumpdevice'DISK','Dp_edubase_diffbackup','C:\EduBase\Backup\Diff\dp_EduBase_DiffBackup.bak'; IF EXISTS (SELECT1From sys.backup_devices as BD WHERE bd.name ='Dp_edubase_logbackup') EXEC sp_dropdevice'Dp_edubase_logbackup'; EXEC sp_addumpdevice'DISK','Dp_edubase_logbackup','C:\EduBase\Backup\Log\dp_EduBase_LogBackup.bak';

First, to make a backup of the database

1. Full backup

Use edubase;        DECLARE @FullBkDesc VARCHAR (MAX); //declaring backup DevicesSELECT @FullBkDesc='Weekly full backup for'+db_name () +' at'+datename (Year,getdate ()) +', Week'+datename (Week,getdate ()) +'. ('+convert (Varchar,getdate (), -)+')'; BACKUP DATABASE edubase to dp_edubase_fullbackup with INIT//The init operation can initialize the file, which can overwrite the previous backup and keep the backup after the Operation, Name ='Edubase_fullbackup', DESCRIPTION= @FullBkDesc;

2. Log backup

DECLARE @LogBkDesc VARCHAR (MAX); SELECT @LogBkDesc='Hourly log backup for'+db_name () +' at'+datename (Year,getdate ()) +', Week'+datename (Week,getdate ()) +', Day'+convert (Varchar,datepart (W,getdate ())-1)+', hour'+convert (Varchar,datepart (Hour,getdate ())) +'. ('+convert (Varchar,getdate (), -)+')'; BACKUP LOG edubase to Dp_edubase_logbackup with INIT, Name='Edubase_logbackup', DESCRIPTION= @LogBkDesc;

4. Add description

Add a description of the time to the log file for later viewing and operation on restore

SELECT @DiffBkDesc='Daily differential backup for'+db_name () +' at'+datename (Year,getdate ()) +', Week'+datename (Week,getdate ()) +', Day'+convert (Varchar,datepart (W,getdate ())-1)+'. ('+convert (Varchar,getdate (), -)+')'; //Use the GETDATE () function to obtain the year, month, and day, respectively, using the concatenation string method to assign values to the parameters

1. Attention

After the first use of incremental and log backups, the init operation cannot be used in subsequent code, otherwise the previous data will be overwritten and the restoration and repair of all data cannot be achieved.

First, data restoration

(a) Operating procedures

1. Using RESTORE HEADERONLY

From (file name) to view data backup files. After viewing determine which log backup files should be recovered (except for the log backups required during the last two modifications, the remaining restore incremental backups will be available)

2. Restore Incremental Backups

3. Restore the log backup of the previous operation from the end log

4. Restore the backup of the tail log

(ii) Implementation code

1. View backup Information

DECLARE             @TailLogBkPath VARCHAR (MAX);    SELECT            'C:\EduBase\Backup\Log\EduBase_TailLogBackup_'+convert (VARCHAR (  (), GETDATE (),+ +'. bak'    RESTORE headeronly from        DP _edubase_fullbackup; RESTORE headeronly from        dp_edubase_diffbackup; RESTORE headeronly from        dp_edubase_logbackup; RESTORE headeronly        = @TailLogBkPath;

2. Restore data

DECLARE @TailLogBkPath VARCHAR (MAX); SELECT @TailLogBkPath='C:\EduBase\Backup\Log\EduBase_TailLogBackup_'+convert (VARCHAR (Ten), GETDATE (), -)+'. bak'RESTORE DATABASE edubase from Dp_edubase_fullbackup with FILE=1   //Specify the file location within the backup device, REPLACE//If you specify overwrite does not check whether the current database backup and backup database name, GUID, and so on are consistent, do not check whether the tail log has been backed up, NORECOVERY;//The database is not restored, the uncommitted thing will not be rolled back, and if the subsequent restore is concluded, the commit is rolled backRESTORE DATABASE edubase from Dp_edubase_logbackup with FILE=1, NORECOVERY; RESTORE DATABASE edubase from Dp_edubase_logbackup with FILE=2, NORECOVERY; RESTORE DATABASE edubase from DISK=@TailLogBkPath with FILE=1, RECOVERY;//Database Complete Restore

3. Differential backup

DECLARE @DiffBkDesc VARCHAR (MAX); SELECT @DiffBkDesc =‘Daily differential backup for' +db_name () +‘At' +datename (Year,getdate ()) +‘, week "+datename (Week,getdate ()) +,day  +convert (Varchar,datepart (W,getdate ())-1) + '  "+convert (Varchar,getdate (), 120) + ' 

Learning Notes (13)--knowledge points and considerations for database backup and restore