Tuesday, 29 September 2015 00:00

Selecting a SQL Server backup model

Rate this item
(0 votes)

In a previous tip we discussed the different recovery models SQL Server offers and decision points on which to base your recovery model selection. After you've selected the appropriate recovery model, you need to put in place the proper backup strategy to minimize data loss and downtime in case of a database failure.

I have seen many database installations wherein the correct recovery model is in place to minimize data loss, but the correct backup options and plans are not in place. Often the Full Recovery model is selected, but the only backups occurring are full backups. Occasionally, the Simple Recovery model is selected, even though the customer base could not afford to lose all transactions since the last full backup.

Creating a full backup once a day probably makes a lot of sense to most people, but that should only be the beginning of your backup strategy. We will take a look at some of the components to help define a backup strategy as well as the different backup options that are available.

First, let's look at the backup options:

Database (Full)

  • This option creates a full backup copy of the database at the time the backup occurs.
  • Available for all recovery models.

Transaction

  • This option creates a copy of the active transaction log, which lists any transactions that have occurred since the last backup.
  • Available only for Full Recovery or Bulk-Logged Recovery models.

Differential

  • This option copies only the database pages that have been modified after the last database backup.
  • Available for all recovery models.
  • When to use Differential backups.
    • If you issue several transaction log backups throughout the day, it is helpful to also use differential backups in conjunction with database and transaction backups. The use of differential backups minimizes the number of restores that need to occur in case of a failure.
    • Here is an example: If you issue one database backup at midnight and transaction backups every 15 minutes, by 9 a.m. you will have 37 backup files. If you need to restore your database to 9 a.m., you will have to restore all 37 files. If you also issue differentials every three hours, and you need to restore your database to 9 a.m., you will only need to restore two files (the full and the differential that occurred at 9 a.m.). You can see how this could greatly decrease the time it takes to do a restore of a database and, therefore, minimize downtime.

Others

  • In addition to Database, Transaction and Differential backups, SQL Server also has options for File or Filegroup and Snapshot backups. These options will be addressed in future tips.

Unfortunately, SQL Server does not have an option for simply setting up a backup model like it does for the recovery model. It would be nice if you could just select a backup model from a drop-down list. The closest thing to that is Maintenance Plans, but the options in Maintenance Plans do not give you the choice of doing Differential backups, and you still need to set the proper backup schedule.

Based on the recovery model you have selected, you now have to choose which type of backups to run -- but more importantly, the time and frequency of the backups.

So, what is the best backup model to use for your database?

If you are using the Simple Recovery model:

  • Use only Database backups.
  • Depending on how frequently the data changes and how critical it is, you should issue at least one full backup a day.
  • This should occur during off-hours when there is minimal database use.

If you are using the Full or Bulk-Logged Recovery model:

  • Option 1 -- use Database and Transaction.
  • Option 2 -- use Database, Differential and Transaction.
  • A full backup should be created at least once a day.
  • Transaction log backups should occur every 15 minutes.
  • Differential backups should occur every three hours.
  • The full backup should occur during off-hours when there is minimal database use.
  • The transaction and differential backups should be on a set schedule based on when your full backup occurs.

How to implement your backup model

You can run backups manually, but the best approach is to schedule backups using SQL Agent. Once you set up the backup job, let SQL Agent run the backups on a set schedule. This can be done one of three ways:

  • Enterprise Manager

    • Right click on the database name.
    • Select "All Tasks."
    • Select "Backup Database."
    • Once the options are set, you can then use the schedule option to create a job.

  • T-SQL

    • Using BACKUP commands, you can create the command and then use Enterprise Manager to create a job or use T-SQL to create the job.
    • You have the ability to create Database, Differential and Transaction backups using T-SQL.
  • Maintenance plans

    • SQL Server has a maintenance plan wizard that walks you through whether you want to do Database and/or Transaction backups. The wizard also allows you to set the backup schedule.
    • To access the wizard in Enterprise Manager, under Management right click Database Maintenance Plans and select New Maintenance Plan.
    • Differential backups are not an available.
    • The default for transaction log backups is once a day, which you should change to more frequent transaction backups.

Summary

The types of backups and the schedule you use will allow you to control how much data could be lost in case of a failure as well as the time it takes to do a recovery. Using Differential backups could dramatically decrease the time it takes to do a restore of your database and get your users up and running quicker. Using Maintenance Plans is also a place to start, but take the time to get familiar with all of the backup options and then select a plan that matches your business goals.

Reference http://searchsqlserver.techtarget.com/tip/Selecting-a-SQL-Server-backup-model?ses=1090912

Last modified on Tuesday, 29 September 2015 13:38
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

31 comments

  • Comment Link Blair Sunday, 04 March 2018 00:05 posted by Blair

    Omaha, NE dog walking and cat sitting.

  • Comment Link Aimee Thursday, 01 March 2018 00:10 posted by Aimee

    Hi, i believe that i saw you visited my blog thus i got
    here to return the favor?.I am attempting to in finding issues to enhance my web
    site!I suppose its adequate to make use of a few of
    your ideas!!

  • Comment Link Anke Tuesday, 20 February 2018 16:19 posted by Anke

    Strain washers are versatile pieces of apparatus.

  • Comment Link Lisa Tuesday, 20 February 2018 07:13 posted by Lisa

    The other type of base is the coarse asphalt base.

  • Comment Link Alton Monday, 19 February 2018 16:26 posted by Alton

    Terrific article! That is the type of information that
    are meant to be shared across the internet. Shame on Google
    for now not positioning this submit upper! Come on over and discuss with
    my site . Thanks =)

  • Comment Link Mia Monday, 19 February 2018 10:18 posted by Mia

    Call in the present day for a free estimate of our providers.

  • Comment Link Dollie Sunday, 18 February 2018 16:41 posted by Dollie

    © 2013 Tree Of Life Tree Management Service.

  • Comment Link Demetrius Sunday, 18 February 2018 16:27 posted by Demetrius

    Garbage service is carried out on Monday and Tuesday.

  • Comment Link Leslee Sunday, 18 February 2018 12:45 posted by Leslee

    Asphalt driveway price are easy to estimate.

  • Comment Link Mikel Sunday, 18 February 2018 10:56 posted by Mikel

    © 2017 SelectQuote Insurance coverage Providers.

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.