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

Source: Internet
Author: User
Tags mssql mssqlserver

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.