| 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:
- How much data can be lost? A day, one hour, a week, none...
- What kind of processing occurs? Transaction, batch loading, reporting, a combination...
This data be easily recreated if there is a failure or it is not the only source of this data?
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.
- 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).
- 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).
- 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).
- 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. |
