up vote 2 down vote favorite
share [g+] share [fb]

Monday afternoon a new database is created and set to the Full Recovery Model.

During Mon - Thurs night a full back up is successfully created but no transaction log backups are ever performed. Friday afternoon the database blows up.

Am I technically "although obviously very bad practice" able to restore the database to a point in time using a combination of Thursday nights full backup, the ever growing transaction log and a tail log backup.

Or because the transaction log has never been created would I only be able to restore to the last Full back up (Thursday night)...

If I am able to restore to a point in time without performing a transaction log back up, other than maintaining the size of the transaction log. What are the other benefits of performing many small transaction logs over one huge...or a couple of huge transaction logs.

link|improve this question
feedback

migrated from stackoverflow.com May 9 '11 at 12:32

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

2 Answers

up vote 0 down vote accepted

Many small transaction log backups means that your transaction log doesn't grow out of all proportions - since you'd probably keep these backups off of your main SQL Server disk(s), you'll have less likelihood of running out of disk space. Additionally, if somehow you lose both your main data and the transaction log at the same time, you'll be glad to have log backups.

The full backup will have cleared the transaction log, so you should be able to combine a restore of the full backup with a point-in-time restore of the transaction log + log tail.

link|improve this answer
1  
I agree with the first part of what you say. Although I read on Paul Randals blog and also in a text book at hand that it’s not the full back up that sets active VLFs to truncated (obviously part of the Tran Log), but rather a transaction log backup. It was my understanding that if a Transaction log backup is never performed the Tran log will continue to grow indefinitely. Regardless of if any Full or Differential backups are completed. – FairFunk May 10 '11 at 8:54
feedback
up vote 0 down vote

So now you have full backup from Thursday, ldf file from Friday and no log backups?

You could try LiteSpeed. Here is a topic: http://dba.stackexchange.com/questions/900/how-to-restore-database-using-old-full-backup-and-current-log-file

Also, you asked:

What are the other benefits of performing many small transaction logs over one huge...or a couple of huge transaction logs.

Let say you do log backups every hour on an hour. So let's say you made log backup at 1am, and at 1:50 you db is gone, both mdf and ldf. As a result you lost 50 minutes worth of data.

So the frequency of your log backups determines for which period of time you can accept to lose data in case of disaster.

I would say that, not letting the log to grow is just a side-effect, but not the main goal of the backup policy.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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