SQL Server: Full backup, differential backup or transaction log backup, tail-log backup

Accident background:

suddenly the database is corrupted for no apparent reason, and differential or transaction log backups 3 point will be automatically done, then how to 2 Point to 2 Point + data recovery between the ? this needs to be restored by backing up the tail log.

Environment Description:
A company has installed aSQL Serverdatabase, in order to ensure that the database can be repaired in the event of a failure, the administrator has done backup operations, such as full backup+differential backup or full backup+transaction log backups, and the time of the backup is every6hours to do a full backup, on a daily1Point,6Point, APoint, -Point,6within an hour is every1hours to make a differential backup of transaction log backups, combined with scheduled tasks.

If there is such a scene now,2Point +suddenly the database is corrupted, differential backups or transaction log backups3point will be automatically done, then how to2Point to2Point +data recovery between the?this needs to be restored by backing up the tail log. (You must ensure thatLogfile is not corrupted)

Original source:

***** 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

Scene:

--1. Backup:

A. Fullbackup @1:00am: (Every 6 hours)

111

222

--insert into [Temp]. [dbo]. [Demo] VALUES (111)

--insert into [Temp]. [dbo]. [Demo] VALUES (222)

--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)

111

222

333

444

--insert into [Temp]. [dbo]. [Demo] VALUES (333)

--insert into [Temp]. [dbo]. [Demo] VALUES (444)

--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 finished, some insertions are done

--insert into [Temp]. [dbo]. [Demo] VALUES (555)

111

222

333

444

555

c. After the data file is corrupted, make a backup tail -Log before the full backup is restored, and back up the changes after the differential backup

no_truncate [email protected]:45am:

  -- BACKUP LOG temp to DISK = ' D:\MSSQL10. Mssqlserver\mssql\bak\temp_log2.bak ' with INIT, no_truncate ;

GO

--2. Restore:

A. Deleting the original database (back up the original mdf,ldf file First )

--drop Database Temp

B. Restore full backup , norecovery mode, last recovery selection recovery

--restore Database temp from disk = ' D:\MSSQL10. Mssqlserver\mssql\bak\temp_full.bak ' WITH NORECOVERY

c. restore diff / 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 ' backup tail ' log , recovery Mode

--restore Log temp from disk = ' D:\MSSQL10. Mssqlserver\mssql\bak\temp_log2.bak ' with recovery

All data:

111

222

333

444

555

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1773115

SQL Server: Full backup, differential backup or transaction log backup, tail-log backup