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:
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.
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:
Based on the recovery model you selected you should follow a plan such as the following:
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:
|
| 5 | Backup Process | SQL Server offers many built in options to backup your database such as:
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
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
