From what I can tell, there are three possible ways of backing up your SQL Server database
- Full backup
- Differential backup
- Log Shipping
What are the pros and cons of each strategy and in what situations should they be employed?
Log shipping is not a backup scenario. It's a semi high availabiliy scenario.
For backups there's full, differential and transaction log backups. They should all be used together. Your SLA dictates how you use them. Most typical scenarios are full backup say at midnight, diff backup at noon and transaction log backups every 30 or 15 minutes.
And remember: you don't have a valid backup until you restore from it to test if it's ok.
Arguably there is no such concept as a backup strategy: you have a restore strategy because this determines how long until you are back in operation*.
All strategies require a full backup to base any subsequent restores of differential and/or log backups.
In practice, you can have a full backup from 6 months ago with 15 minute log backups: however you have to apply every log backup from the last full.
As a random example, one scenario could be full weekly, differential daily, log 15 minutes.
The backup interval determines how much data you'll lose in the worst case: 15 min log backups gives you a data loss between 1 second and 14 mins 59 secs, average 7.5 minutes. Is this acceptable?
Log shipping is warm standby with manual failover: it isn't backup but a high availability option.
There is no one strategy that fits every situation. But it is important to understand what you have available to you. Full backups are exactly what they sound like: a full backup of your database, minus the transaction log. Differential backups are backups of changes in the data files since the last full backup. Transaction Log backups will backup all transactions stored in the transaction log since the last Transaction Log backup. Transaction Log backups will allow you to restore to a point in time. If this is a requirement, you will need to set your recovery mode to "Full" and you will need to take regular Transaction Log backups depending on how much data you willing to lose in the event of a recovery situation.
When dealing with Transaction Log backups it is important to understand what a log chain is. In my words, a log chain is the series of backups that are required to be restored in order to restore your database to a given point in time. In order to begin restoring transaction logs, you must first restore a full backup using the WITH NORECOVERY option. If you also perform differential backups, you will want to restore the most recent differential backup prior to the point in time you wish to restore to using the same WITH NORECOVERY option. At this point, you will need to restore the Transaction Log backups, sequentially, using the WITH NORECOVERY option on all backups except the final backup. For more information on point in time restores, check out this link. http://msdn.microsoft.com/en-us/library/ms175093.aspx
As mentioned, Log Shipping is not a backup strategy, but it can cut down on restore times significantly in the event of a disaster recovery situation. One gotcha to look out for is that any replication publications will need to be scripted to the Log Shipping server and initialized in order for replication to work as it was prior to the disaster. With larger publications, this could cause a significant increase in the time it takes to restore back to a production level.
Hope this helps,
I second Mladen Prajdic. This article will help you to chose the right backup strategy depending upon the Reovery Model of the databases.
those are not backup strategies for SQL Server. Full and differential backups are types of backups that you can do to a SQL Server database, while Log Shipping is a High Availability strategy (by moving log backups at a scheduled time from a server to another and have those 2 databases in sync up to the limit of your backups).
Nice info about Disaster Recovery (backup & restore :-)) you can find on MSDN: here and here. In short, you need to chose how much data you can recover from backups in case of a failure. A sane sample of backup strategy would be a full backup every day and log backups every hour (this depends on your needs), so in this case you would be able to restore the database from the full backup + all the daily log backup.
Another nice reference about DR you can find on Simple_Talk.
Of course, not only do you need to restore your database, there's recovery in the context of the server and application the database is a part of. I haven't used it myself yet, but Data Protection Manager looks to do a more comprehensive job, if you need it.
The best way is to use all three backups types altogether. Of course, you can ignore differential backup of transaction log backup. Everything depends on your database, how fast it grows, how frequently you make changes in your database and other. Before to choose your backup plan, consider how much data are you willing to loose? How much time are you ready to spend to recover your database?
For example, If your database growth fast you can use the following SQL Server backup strategy: full backup - once a day, differential backups - every two hours and transaction log backups - every 20 minutes. In this case, if the failure occurs, you will lose not more than 19 minutes of your work. Another example, if your database growth slowly you can perform a full backup once a day, differential backup every six hours, and every hour make transaction log backup.
One more tip - to be sure that your database in safe, from time to time restore your backups on a test server.