1

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

Improve this question
1

You can do a point in time recovery to Oct 5th if you have only full backups and a last transction log backup (called a tail log backup). Your transaction log backup contains all transactions back to the last log backup. So that should be sufficient to do a point in time recovery.

That you are getting the message "transaction log does not contain the point" indicates one of the following:

  • There were other transaction log backups taken before your last transaction log backup.
  • The database was in Simple recovery mode somewhere between the 1st and the 23rd of October.

The reason your transaction log is so big is that it was not backed up regurarly. As transactions happen, the log grows. A transaction log backup truncates your transaction log, and then the empty space can be used for new transactions. The reason can also be that there was a big amount of data modified.

Recommendation: regurarly backup your database (for example every day) and your transaction logs (at least every hour or even more often, depending on the activity in the database). Keep those backups in a safe place. Then you will be able to do proper point in time recovery.

Improve this answer
6
  • 1
    The LDF is 13 GB while the backup is only 200MB. His LDF is mostly empty and space has only been reserved and set with an initial size of 13GB or it auto grew because at some point there were a lot of transactions being recorded in the LDF – Julien Vavasseur Oct 23 '15 at 12:31
  • @user2347976 If this is critical data then you definitely need to change that strategy you're using. I do at a minimum FULL backups once a day and then LOG backups hourly or more often depending on the applications they're tied to, etc., how critical they are for the business, etc. You have to plan ahead of time and ensure you understand how to restore as knowing how to backup and assuming will getcha sometimes when it's too late. . . You have to do your homework and be proactive if this is driving critical business processes or applications. Good luck! – Pimp Juice IT Oct 23 '15 at 19:02
  • @Peter Elzinga But on this thread at social.msdn.microsoft.com/Forums/sqlserver/en-US/…, MS employee David said it is possible to restore Oct 5 point. I get confused. – user2347976 Oct 24 '15 at 4:17
  • @user2347976 David is right, I never knew this was possible. I will change my answer accordingly. – Peter Elzinga Oct 26 '15 at 7:49
  • @Peter Elzinga Thank you for your modifications – user2347976 Oct 26 '15 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.