Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When restoring from a backup in SQL Server, the procedure is to restore the .bak file and then apply any .trn files since the last full backup.

Does it make a difference how many .trn files there are, if they cover the same transactions? I.e. is it faster or slower restore a 1-hour .trn file vs twelve 5-minute .trn logs?

share|improve this question
    
There would be a very small amount of overhead associated with reading the headers of the 12 files as opposed to 1 header. The actual rollback/roll-forward work performed by SQL Server would be no different. –  Max Vernon Apr 18 '13 at 14:49
    
Of course, having transaction log backups taken every 5 minutes, instead of every hour, gives you more up-to-date backups. –  Max Vernon Apr 18 '13 at 14:51
1  
@MaxVernon, I think you've answered the question by saying they're basically the same in terms of restore time. If you make your comment an answer I will accept it. –  Henry Jackson Apr 18 '13 at 16:32
add comment

2 Answers

up vote 1 down vote accepted

There is a very small amount of overhead associated with reading headers from backup files. The more backup files, the more overhead needed; however the amount of data actually read for each header is relatively small. The actual rollback/roll-forward work performed by SQL Server would be no different - SQL Server looks at the transactions in the restore file, and applies them to the database.

If your database has a lot of transactions per second, then each log backup will have a fair number of transactions within it, and the above logic applies. If, however, you have a very low number of transactions over the 5 minute period, then the amount of data in any given 5-minute log backup would be quite small, and having lots of small log backups might increase the time needed for restore.

Having transaction log backups taken every 5 minutes, instead of every hour, gives you more up-to-date backups. You could conceivably take that a bit too far, by say, doing a BACKUP LOG every 15 seconds. This would create a huge number of backup files that would result in a myriad of other issues such as time required to read the file system structures for the folder containing the backups. The key here is to use a bit of common sense, and balance the need for recent backups against the need to keep the number of files as small as you can.

share|improve this answer
add comment

Does it make a difference how many .trn files there are, if they cover the same transactions?

You need to understand that while restoring a T-log, you cannot restore a T-log backup which is out of sequence.

for e.g If you took log backup every 5 mins for 1 hr then to bring the database up-to-date, you need to restore all the 12 T-log backups in sequence after fist restoring the full backup. Keep in mind that all except last, restore should be done with norecovery. Last one would go with recovery.

As @maxVernon mentioned, taking frequent backups will help you bring the database more up-to-date and you have the flexibility of getting Point-in-time recovery in case of any disaster.

For fast or slow, obviously, a huge T-log will be slower to restore than a smaller one as it has to redo / undo all the operations that have happened since the last full backup to bring the database in a Consistent state.

Some excellent references can be found here and here.

share|improve this answer
2  
I don't know where the OP ever implied they would try to restore transaction logs out of sequence or skip any... –  Aaron Bertrand Apr 18 '13 at 15:34
    
Right, I know I need to restore them in order. Here's the hypothetical I'm asking about: suppose the database needs to be restored and the last full backup was 2 days ago. Will it be a lot slower if I have 5-minute transaction backups (meaning there would be 576 of them) vs 1-hour transaction backups (meaning there would be 48 larger files)? –  Henry Jackson Apr 18 '13 at 15:41
    
It depends on how many transactions have happened meaning if there are lots of Inserts/ Updates/ Deletes happened, then obviously it will take more time to rollback/rollforward during the restore phase. –  Kin Apr 18 '13 at 16:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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