October 23, 2015 at 5:44 am
Hi,
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past.
2. Restore the server with Oct 1 full backup with NORECOVERY option.
3.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
Thanks
October 23, 2015 at 5:53 am
A full backup is a copy of the database as it was at the time of backup. It can be restored ONLY to the time of the backup.
To do point-in-time restores, you need transaction log backups, and you need a complete, unbroken log chain from the time of the full backup. You didn't have that.
The DB had probably been switched to simple recovery after the 1 Oct full backup and hence the log chain was broken.
Take a read through http://www.sqlservercentral.com/articles/books/94938/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2015 at 10:01 am
GilaMonster is right, look at recovery mode and whether it has changed, lack of Tlog backup restricts your options.
...
October 23, 2015 at 10:10 pm
Hi,
Let me explain the case in more details.
The database comes from a customer. The customer sent the full backups on Oct 1(Oct_1.bak) and Oct 10(Oct_10.bak) and want to restore to the point at Oct 5. Both full backups are NOT copy-only backups. And he also sent the CustomerDB_Oct_23.MDF and CustomerDB_Oct_23.LDF on Oct 23.
After getting his files, I try to do as follows:
1.Start SQL Server 2008 on my local computer.
2.Attach the CustomerDB_Oct_23.MDF & CustomerDB_Oct_23.LDF files to the SQL Server. The LDF file is 13GB.
3.Invoke “BACKUP LOG CustomerDB TO DISK ‘CustomerDB_Log.bkf’ WITH FORMAT” to backup the transaction log of the database. The CustomerDB_Log.bkf is only 200MB. I can confirm the database on both customer server and my server is in “Full Recovery Mode” and have not switched to “Simple Recovery Mode” on customer server during the database lifetime.
4.Restore the database to a new database CustomerDB_Restore with Oct_1.bak and NORECOVERY option.
5.Try to restore to the point at Oct 5 12:00, with the transaction log CustomerDB_Log.bkf generated in step 3. But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early.
It seems the problem comes from step 3 but I cannot figure out why the backup of the log does not contain the point on Oct 5.
October 24, 2015 at 3:55 am
Somewhere between Oct 1 and Oct 23, one or more log backups have been taken, or the db has been switched to simple recovery and back to full.
Either one would truncate the log, hence the time you want to restore to is not in the log backup you have (and the 200mb size of the log backup is more indication that the log has been truncated and doesn't contain a month of transactions).
Check the error log, recovery model changes will be listed there, look at the msdn backup tables, log backups will be recorded there.
Copy only on the full backups is irrelevant, that only applies when you have differential backups in the mix.
Basically, something has truncated the log between the full backup you have and the log backup you have, and hence it is not possible to restore to the point you want to.
See the book I referenced earlier for lots of detail on log management.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply