I have

  1. Full-Backup-A
  2. Transaction-Log-Backup-A
  3. Transaction-Log-Backup-B (*) - I have to restore this point
  4. Full-Backup-B

How to do it? It seems that the only way is

  1. Full-Backup-A
  2. Transaction-Log-Backup-A
  3. Transaction-Log-Backup-B
  4. Shut-off client access
  5. Transaction-Log-C
  6. Full-Backup-B
  7. Allow client access

Are there any other ways to guarantee that nothing did happen with the database between last transaction log and the next full backup. I was thinking about

a. Starting transaction log backup simultaneously with full backup. b. Using differential back up while clients are connected and making full backup during maintenance window only c. Run replication and back-up the replica, stopping and restoring duplication services in points 4 and 7

and feel that it is actually hopeless.

migrated from stackoverflow.com Mar 12 '11 at 23:51

This question came from our site for professional and enthusiast programmers.


Here's a link about restoring SQL 2005 to a point in time: http://msdn.microsoft.com/en-us/library/ms190982(v=sql.90).aspx

You can only restore to points that you have backups of, of course. If you need data that happened after the Transaction-Log-Backup-B, you'd need to restore the Full-Backup-B. Generally when setting up a backup scheme, you need to determine what the minimal period of time you want to 'lose' is. So if you need backups every 15 minutes, you want to run the transaction log backup that often so you can make it as easy as possible for future recovery.

  • So, as I suspected "full backup" does not include the last chunk of transaction log no matter what options you use - yet another "you are in the helicopter" Microsoft moment. Sorry for venting my frustrations - this sql-thingy should not be used in production environment (plain and simple) if it has an unavoidable window for data loss. And how small is this window does not really matter - it may cost dearly in real $$ – zzz777 Mar 11 '11 at 16:46
  • I don't know what your environment is like, but you could do transaction log backups every hour, then at 3 AM (or sometime when things are mostly quiet) do the full backup. Hopefully you would then be able to play back whatever transactions you need, and not have to worry about many people in the system when the full backup occurs. – Thyamine Mar 11 '11 at 16:59
  • @Thyamine If you have maintenance window every US night then yes you can do it, but modern production environments do not have this kind of luxury. – zzz777 Mar 11 '11 at 19:17

You restore the full backup, and then the two transaction log backups - but you use a command to STOPAT to stop just before whatever event happened. The GUI Management Studio can do this too.

So if you're transaction log backups were at 3pm and 4pm, you'd restore the 3pm transaction log backup with norecovery, and then start the restore of the 4pm transaction log backup file, but with a STOPAT.

Ideally, do it all on a test system first, and get verification from whoever you can!

If you are unsure about when the event happened, you can use the standby to bring the database into a readonly mode for testing.

See http://msdn.microsoft.com/en-us/library/ms191468.aspx

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.