SQL Server [backup recovery]: full backup, differential backup or transaction log backup, tail log backup
Background of the accident:
All of a sudden, the database is damaged for no reason, differential backup or transaction log backup will not be done automatically until 3: 00, so how to restore the data between 2: 00 and 2: 40? This needs to be restored by backing up the tail log.
Environment description:
A company installed a SQL Server database, in order to ensure that the database can be repaired in time in case of failure, the administrator did backup operations, such as full backup + differential backup or full backup + transaction log backup, and the backup time is to do a full backup every 6 hours, at 1: 00, 6: 00, 12:00, 18:00 every day The differential backup transaction log backup is done every 1 hour within 6 hours, and is combined with the scheduled task.
If there is a scenario where, at around 02:40, the database is suddenly corrupted for no reason, and differential or transaction log backups are done automatically at 3: 00, how do you recover the data between 2: 00 and 2: 40? This needs to be restored by backing up the tail log. (you must ensure that the log file is not corrupted)
Source of the original text:
* http://blog.csdn.net/misterliwei/article/details/5884656****
Http://www.w2bc.com/Article/44855
Http://www.jb51.net/article/18613.htm
Tail log:
Http://blog.csdn.net/misterliwei/article/details/5884656
Http://blog.csdn.net/dba_huangzj/article/details/8491327
SQL Server backup Basics:
Http://blog.csdn.net/dba_huangzj/article/details/22683687
Scene:
-1. Backup:
A. Fullbackup @ 1:00AM: (Every 6 hours)
one hundred and eleven
two hundred and twenty two
-- insert into [Temp]. [dbo]. [demo] values
-- insert into [Temp]. [dbo]. [demo] values
-- alter databasetemp set recovery full
-- backup databasetemp todisk='D:\ MSSQL10.MSSQLSERVER\ MSSQL\ Bak\ Temp_full.bak' with INIT
B. DiffBackup or Logback @ 2:00AM: (Every 1 hour)
one hundred and eleven
two hundred and twenty two
three hundred and thirty three
four hundred and forty four
-- insert into [Temp]. [dbo]. [demo] values
-- insert into [Temp]. [dbo]. [demo] values
-- backup databasetemp todisk='D:\ MSSQL10.MSSQLSERVER\ MSSQL\ Bak\ Temp_diff.bak' with INIT, differential
Or
-- backup logtemp todisk='D:\ MSSQL10.MSSQLSERVER\ MSSQL\ Bak\ Temp_log.bak' with INIT
After the differential backup or log backup is over, some insertions are made
-- insert into [Temp]. [dbo]. [demo] values
one hundred and eleven
two hundred and twenty two
three hundred and thirty three
four hundred and forty four
five hundred and fifty five
c. After the data file is corrupted, before the full backup is restored, make a backup tail log to back up the changes after the differential backup
NO_TRUNCATE LogBackup@2:45AM:
-- BACKUP LOG temp TO DISK ='D:\ MSSQL10.MSSQLSERVER\ MSSQL\ Bak\ Temp_log2.bak' WITH INIT, NO_TRUNCATE
GO
-- 2. Restore:
a. Delete the original database (back up the original mdf,ldf file first)
-- drop database temp
b. Restore full backup, norecovery mode, and select recovery for the last restore
-- restore database temp from disk ='D:\ MSSQL10.MSSQLSERVER\ MSSQL\ Bak\ Temp_full.bak' with norecovery
c. Restore differential / log backup, norecovery mode
-- restore database temp from disk ='D:\ MSSQL10.MSSQLSERVER\ MSSQL\ Bak\ Temp_diff.bak' with norecovery
Or
-- restore log temp from disk ='D:\ MSSQL10.MSSQLSERVER\ MSSQL\ Bak\ Temp_log.bak' with norecovery
d. Restore the 'backup tail' log, recovery mode
-- restore log temp from disk ='D:\ MSSQL10.MSSQLSERVER\ MSSQL\ Bak\ Temp_log2.bak' with recovery
All data:
one hundred and eleven
two hundred and twenty two
three hundred and thirty three
four hundred and forty four
five hundred and fifty five
