Restoring Transaction Log Backups...

freddo12345

Hi,

i think i know what's going on, but need to be sure. Thanks for any help here.

we do hourly full backups of our production database. we also have log shipping set up for this database, and do a transaction log backup every 10 minutes. the log shipping has been going for over a year.

a developer asked me to restore to a point in time yesterday, and i saw that we had an hourly backup, plus there were 3 transaction log backups after that. but when i asked SQL Backup to restore to that log backup, the UI took forever and we cancelled the effort.

i believe that there is a transaction log chain for the log shipping backups, and these are not related to the full backups that are done. is this right?

if so, using the log shipping backups to restore to a point in time, is not very useful, or the right way to go.

what does anyone think?

thanks!

Fred

Brian Donahue

Hi Fred,

More than likely you will need log backups to restore to a point in time -- they pretty much are the way to go. Restoring a full or a differential can only allow you to restore your database to the point in time that the backup was taken. Only a log restore will allow you to restore only part of the file up until the point in time that you want the database to reflect.

You can perform full backups of a database while log shipping is going on, and these backups do not have to figure into your restore strategy. So say you performa a seed backup, restore that, and log ship, you can take full backups of the source without the need to restore them on the target, as they're totally independent, provided you don't truncate the log somewhere along the line.

It sounds to me that the SQL Backup Console performance is the issue rather than the backup strategy.

freddo12345

Thanks for your response Brian,

so if i understand correctly, the log backups from log shipping are only useful, if i can supply the seed backup plus all the subsequent log backups from the past year or two. that would be thousands of log files... i think that was why the UI was taking so long. but this is not practical.

or can you just pick a log backup file and tell it how far back to look?

and finally, our hourly backups are apparently not useful for restoring to a point in time, but obviously they are useful for a full restore.

thanks,

Fred

Brian Donahue wrote:

Hi Fred,

More than likely you will need log backups to restore to a point in time -- they pretty much are the way to go. Restoring a full or a differential can only allow you to restore your database to the point in time that the backup was taken. Only a log restore will allow you to restore only part of the file up until the point in time that you want the database to reflect.

You can perform full backups of a database while log shipping is going on, and these backups do not have to figure into your restore strategy. So say you performa a seed backup, restore that, and log ship, you can take full backups of the source without the need to restore them on the target, as they're totally independent, provided you don't truncate the log somewhere along the line.

It sounds to me that the SQL Backup Console performance is the issue rather than the backup strategy.

Brian Donahue

Hello,

Yes, it would be impractical and potentially dangerous to rely on log backups only because a corrupt log backup in the chain will prevent you from restoring any subsequent backups after that.

You'll definitely want to incorporate full and differential backups into your strategy if not simply because, as you point out, you will have fewer files to restore. If you wanted to restore a whole database "from scratch", you will want it so that you have a full, differential, and as few log backups as possible to get up to the point in time that you want.