How to define backup interval for Full, Differential, TLog

  • Article

Designing an effective SQL Server backup/Restore strategy

Designing an affective backup and restore strategy is required to bring database online when you encounter these situations:

1. Database gets corrupted due to I/O issues

2. Need to roll back the database to a previous timeframe because of user error

3. Meet audit compliances

4. Power failure (resulting in corrupted data)

5. Systems software failure

6. Malicious deletion or modification of data

7. Viruses

8. Natural disasters (that is, fire, flood, earthquake, and so on)

9. Theft or sabotage

To design backup strategy all you need is Service Level Agreement (SLA). I’m not going to talk about a highly in-depth tutorial on backup strategy because there are already lot out there but in this post, let me keep it simple for organizations who started using SQL Server and for accident DBA’s who are new to SQL Server.

Now let’s start designing backup strategy for database AdventureWorks used by a company called AdventureSports. AdventureSports need to conclude the duration of data loss which they can afford or in other way how much data they can afford to lose.

Let’s say that they arrive at a conclusion of 2 hrs of acceptable data loss then SLA can be calculated as:

SLA = 2 hours + time required to restore the database + time required to release the db to production.

So a backup strategy has to be designed in such a way that at any of the above 9 scenarios, AdventureSports will lose maximum of 2 hours of data.

To arrive at a <2 hrs data loss, here is a sample plan

è Full back up every day at 12:00 am

è Differential backup every 8 hours

è Log backup every 2 hours

So if there is something wrong at 2:51 in the afternoon on Wednesday then AdventureSports can restore Wednesday morning 12:00 am full backup à 8:00 am Differential backup à 10:00 am log backup à 12:00 am log backup à 2:00 pm log backup à 2:50 pm tail log backup (if possible)

So above plan guarantees that AdventureWorks database can be brought to a point as it was at 2:00 PM and if system is available for a tail log backup then database can even be brought to a point as it was at 2:50 pm.

To design this strategy you need to a thorough understanding, advantage, dis-advantages and usage of Full, Differential, and Transaction Log backups.

Some Considerations to help you:

è Also in your organization, if you are taking backup to TAPE then consider having at least the most recent backup available in disk because time taken to restore will be quick compared to restoring from TAPE.

è Take backup with CHECKSUM option enabled which helps in identifying whether backup is corrupted by running RESTORE VERIFYONLY from the backup.

è Simulate regular disaster recovery scenario by performing database restore and see whether database becomes accessible within expected SLA time.

è There are various options available to take regular SQL Server backups

o Configure backup through Maintenance plans - https://technet.microsoft.com/en-us/library/ms189647.aspx

o Configure backup through SQL Server Agent jobs - https://support.microsoft.com/kb/930615

o Using 3rd party SQL Server backup solutions which used SQL VDI technology to take backup of SQL Server databases. Ex: Idera, Litespeed, BackupExec….