Advice about Backup Solution

Question

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?

Answer 1

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

Answer 2

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