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

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

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.