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