Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Backup Plan


By:   |   Last Updated: 2007-07-12   |   Comments (6)   |   Related Tips: More > Backup

Problem

In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc...  The first item on the list was to address and implement a sound backup strategy.  This is probably one of the easiest things to take care of, but often the wrong options are selected or the wrong or no backup plan is put in place.  This tip will address some of the items you should consider when putting your backup process in place.

Solution

The following checklist is a list of items that you should consider when implementing your backup strategy.

# Item Steps
1 Determine What is Needed Before you begin implementing your backup strategy you need to better understand what you are trying to protect, how much data you need to recover and also whether the data can be recreated easily or not.

To get started you need to ask yourself a few questions such as the following: 

  1. How much data can be lost? A day, one hour, a week, none...
  2. What kind of processing occurs? Transaction, batch loading, reporting, a combination...
  3. This data be easily recreated if there is a failure or it is not the only source of this data?
  4. Do you have very big batch loads that take considerable amount of time or load a lot of data?

Based on the answers to these questions you can determine the proper recovery model for your database as well as the proper backup types and backup schedule.

2 Recovery Model Based on the answers to the questions in item #1 you can determine the recovery model for your database.
  1. How much data can be lost? A day, one hour, a week, none...
    • If your answer is less than a day or none you should consider the Full recovery model.
    • If your answer is a more than a day then you could use the Simple recovery model (this is assuming you are doing full backups every day).
  2. What kind of processing occurs? Transaction, batch load, reporting only, a combination...
    • If your answer is transaction or a combination then you should consider the Full recovery model.
    • If your answer is reporting only you could use the Simple recovery model (this is assuming this data is loaded from another source and can be easily recreated).
    • If your answer is batch load you could use the Simple recovery model (this is assuming that you can reload the batch data without loosing any data).
  3. This data be easily recreated if there is a failure or it is not the only source of this data?
    • If your answer is No than you should use the Full recovery model.
    • If your answer is Yes then you could use the Simple recovery model (assuming that it is not that hard to recreate the data).
  4. Do you have very big batch loads that take considerable amount of time as well as load a lot of data?
    • If your answer is No than you should use the Full recovery model.
    • If your answer is Yes then you could use the Bulk-Logged recovery model.  Depending on how you load your data you can minimize the logging in the transaction log and therefore have smaller transaction log backups. The only drawback is that if there is a bulk-logged activity in a transaction log backup you are not able to do a point in time recovery for the transaction log backup file that has the bulk-logged operation.

If your answers include Full for any of these questions you should use the Full recovery model.  The only difference would be the answer to the last question where you could use Bulk-Logged instead of Full.

Note: it is also possible to change your recovery model based on different processing, but you need to ensure your backup process follows any of these changes so you do not potentially loosed important data.

3 Select Backup Types SQL Server offers many different types of backups such as the following:
  • Full - backups entire database
  • Differential - backups all changes since last full backup
  • Transaction Log - backups transaction log for all changes since last transaction log backup
  • File - allows you to backup one data file
  • Filegroup - allows you to backup an entire filegroup

Based on the recovery model you selected you should follow a plan such as the following:

  • Full or Bulk-Logged Recovery - Full backups and transaction log backups
  • Simple Recovery - Full backups

In addition to doing the above, you can also introduce Differential backups in between your Full backups.  This is helpful if you do a lot of transaction log backups during the day, so that when you need to restore you can just restore the full backup, the latest differential backup and any transaction log backups after the differential backup.

Other options include doing file or filegroup backups for very large databases.

4 Backup Schedule The next thing you need to decide is when to schedule your backups.  The most intense backup is the full backup since it needs to read the entire database and write out the entire database.  Since this activity is disk I/O intensive the best time to do this is at low peak times, therefore most people run full backups during off hours.

Here is a sample schedule, again this would be based on what you determined to do in step 3:

  • Full Backups - midnight
  • Differential Backups - every 3 hours
  • Transaction Log Backups - every 1 hour
5 Backup Process SQL Server offers many built in options to backup your database such as:
  • Maintenance Plans
  • Using Enterprise Manager (2000) or Management Studio (2005)
  • Using sqlmaint.exe
  • Writing T-SQL code using the BACKUP command

In addition to these items there are also several third party tools that allow you to backup your databases.  These tools offer some additional options such as writing directly to tape, encryption, compression, etc...

The first step is using the one of the built-in options, but these third party tools are great enhancements to the process.

6 Document As with all administration activities you should document your backup procedures and the criteria you will use to determine the recovery model as well as the backup types and backup schedule.  It is much easier to have a defined plan, so when a new database is created you can just follow the steps that have been outlined instead of having to figure this out each time a new database is introduced.
7 Backup to Disk The fastest way to do SQL Server backups is disk to disk.  I guess doing the backup to memory would be faster, but this doesn't give you a permanent copy that can be restored or archived, plus the option doesn't really exist.  There are a lot of backup tools that allow you to go directly to tape, but it is better to have the latest backup on disk so the restore process is faster instead of having to pull the file from tape.
8 Archive to Tape Once the backup has been created on disk you should then archive to tape for long term storage. These archive copies are not used all that often, but they do come in handy when you are doing some research or an audit and you need to get the database back to the state it was at some point in the past.
9 Backup to Different Drives As mentioned above the backup process is a disk I/O intensive activity.  Therefore you should try to separate your disk reads from your disk writes for faster I/O throughput. In addition, it is better to have the backups on a physically separate disk therefore if one of the disks dies you don't loose both your data file and backup file.
10 Secure Backup Files In a previous tip we talked about how native SQL Server backups are written in plain text and can be opened with a text editor and therefore the data could be comprised.  Based on this you need to ensure that your backup files are written to a secure location where only the people that need to have access to the files have access. Also, this ensures that the files are not tampered with or accidentally deleted and therefore unavailable when you need them.
11 Encrypt or Password Protect Backup Files To take the security to the next level you can use passwords on the backups, so they can not be restored without the password.  In addition, you can encrypt your backup files so they can not be compromised as mentioned in item #10.  This encryption can be done using third party backup tools.
12 Compress Backup Files As databases continue to get larger and larger the need for backup compression becomes a necessity.  The native SQL Server backup process does not compress any of the data and therefore your backup files are almost as large as your data files.  With third party backups tools you have the ability to compress your backups by up to 90%. In addition, since so much less data is written out to create the backup file you can also see reduction in the time it takes to run the backup by up to 50%.
13 How Much to Keep on Disk Another question you need to think about is how many backups to keep on disk.  In most cases if you need to do a restore you are going to restore the latest backups.   Based on this you could probably get away with just keeping the last days worth of backups on disk assuming that you are then archiving these backups to tape.  To be safe you can keep two days worth of files on disk, but a lot of this depends on how large your backups are and also how much disk space you have.  Going beyond two days is probably overkill, but there are always exceptions to the rule.
14 Online Backups In addition to doing backups to your local disks there are several companies that offer online backups via the internet.  Instead of you have to manage your backup files locally these services allow you to backup your databases via the internet and therefore you have an external copy stored outside of your companies network.
15 Run Restore Verifyonly After running your backups it is a good idea to use the RESTORE VERIFYONLY option to ensure that the backup that was just created is readable.  This doesn't necessarily mean the restore will work without issue, but it does give you another level of comfort that SQL Server can at least read the backup file.
16 Offsite Storage After your backups have been archived to tape the next step is to store these tape copies offsite.  There are several companies that provide offsite tape storage.  I have also seen people take home the tapes with them, so they are not left onsite in case there is some kind of disaster, such as a fire, that could wipe out all of your data.  If you can not afford offsite storage you could also consider Online Backups mentioned in item #14.
Next Steps
  • This list should give you a good foundation for what should be done for backups.  Take a look through the list and mark off each item that you have in place.
  • Review the list to determine which items you do not have covered and how you can go about getting these implemented.
  • Stay tuned for other administration checklists.
  • Check out the DBA database management checklist


Last Updated: 2007-07-12


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, July 11, 2017 - 5:43:56 AM - Sandeep kumar Back To Top

 Hi,

Your articles are simple, straight forward and very useful

For sql knowledge, mssqltips is always my first choice

Thanks for the great work and please keep this going always

Many Thanks 

 

 


Wednesday, May 24, 2017 - 9:09:11 AM - Brijesh gupta Back To Top

 Hi Sir,

 

Your explanation is  fine and easy way to understand.

Thanks--Brijesh Gupta

 


Thursday, February 02, 2017 - 9:57:12 AM - Greg Robidoux Back To Top

Hi Stacy,

it is better to not write your backups to the same physical file.  If that file gets corrupt you could lose all of your backups.

Here is a link to a tip that creates different files for each backup. https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

-Greg


Thursday, February 02, 2017 - 9:14:03 AM - Stacy Back To Top

Hi Greg, I'm hoping you can point me in the right direction. I have an automated backup job for a server (full and differential), but the job saves the new backups to the same file. Is there any way to modify the script to create new files for every backup created?

The script is:

USE [database name];
GO
BACKUP DATABASE [database name]
TO DISK = 'FILE NAME.bak'
WITH FORMAT,
MEDIANAME = 'NAME',
NAME = 'NAME Full Backup';
GO

 

I'm not even sure if this was the best script to use. I have no background in SQL, aside from everything I've taught myself searching the internet.

Any assistance is greatly appreciated!

-Stacy


Thursday, November 22, 2012 - 2:44:28 AM - rakesh Back To Top

Hi Greg !

   Hope everything is fine from your side.

 Can u please send me a script to take a full backup of a database on every friday n8 11.55pm.and a differential backup  for every 2 hr's a day.

      please do the need full


Monday, August 20, 2012 - 8:36:27 AM - Dan Marshall Back To Top

My background is mostly in the Oracle realm. I am used to using sets of 3 online transaction logs that the RDBMS 'rolls' through and continuously backing these up as they fill, while the server goes on to using subsequent log files. This makes the previously filled files again available for writing when the server gets around the set and needs them again. So far in my research, I am unclear as to if and/or how this would be accomplished in the SQL Server realm.

Question: When using SQL Server can I allocate a fixed set of transaction log files for the server to roll through sequentially so that I can back up full files while the server is writing to others in the set?


Learn more about SQL Server tools