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.

Our database Server is being hosted by another company. They provide a file level backup that occurs once per day. They also allow for Log Shipping. My company is taking advantage of their Logshipping option as a major part of their backup and recovery solution.

I would like to have a better backup system that would allow us to restore data up to the point in time of failure. Typicly this would be full backkup model for the database and backups of the transaction logs as well as the full and incrimentals. However, the vendor has told me that providing us with transaction log backups would cause issues with the Log Shipping.

Is there a backup strategy that will allow me to restore to the point of failure and also use logshipping?

share|improve this question
    
if the db server is hosted, what is their disaster recovery strategy? –  swasheck Jan 7 '13 at 20:52
add comment

2 Answers

up vote 7 down vote accepted

You could be utilizing the transaction log backups that are taken by the log shipping process. In fact, you can't double-up on transaction log backups.

So, as long as you have the actual transaction log backups and the ability to copy them to other storage (in order for your custom backup preservation and retention for Disaster Recovery), you should be fine on the t-log front.

But, the real problem is that you can't take full (or differential) database backups. You will need these for disaster recovery. A file level backup is not good enough for a sound DR plan, and surely not sufficient for point-in-time recovery. You must have, at one point in time, been able to take a full database backup in order to initialize your secondary database(s). Why is that no longer an option?

BOL reference on Using Log Shipping as Part of a Recovery Plan

share|improve this answer
1  
Thomas hit it right on the head. How did they setup 'log shipping' without a full sql server database level backup? –  Ali Razeghi Jan 7 '13 at 22:24
    
Thanks Thomas. It's not that we can't do a full backup. A full backup or differentials by themselves don't get me much further than the file level. Sounds like I need to talk to them about how long they store the transaction log backups for and make sure I can get a hold of them. –  Lumpy Jan 8 '13 at 12:57
add comment

As Thomas said, transaction log backups cannot be used unless you have a full database backup. But having a long chain of transaction log backups and a full database backup at the beginning of it will cause the restore to a point-in-time process to be very slow. Imagine you have to roll back several hundred of transaction log backups.

The best would be to have a full database backup once a day (or once a week, depending on how busy your database is), so the number of the transaction logs to be applied will never be huge. To quote Paul Randal "The aim of any restore operation is to restore the fewest possible backups, so the restore operation is as fast as possible". See more here: SQL Server: Recovering from Disasters Using Backups

share|improve this answer
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.