Red Gate forums :: View topic - Restoring Transaction Log Backups...
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup Previous Versions
SQL Backup Previous Versions forum

Restoring Transaction Log Backups...

Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
freddo12345



Joined: 15 Jan 2010
Posts: 2

PostPosted: Fri Jan 15, 2010 6:25 pm    Post subject: Restoring Transaction Log Backups... Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Mon Jan 18, 2010 3:10 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
freddo12345



Joined: 15 Jan 2010
Posts: 2

PostPosted: Mon Jan 18, 2010 5:22 pm    Post subject: Re: Reply with quote

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... Smile 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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Mon Jan 18, 2010 6:05 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group