The key to a successful recovery in the event of a database crash -- or worse yet -- a system crash, is to have the key elements for SQL Server backups in place. SQL Server offers many options for performing backups and the following checklist highlights some of the more important options to think about. This list is a comprehensive starting point for getting your backups in place, safeguarding your backups and testing. Take the time to understand all of the components. It will go a long way toward safeguarding your company's most prized asset and removing the possibility of data loss from the equation.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Checklist: Backing up SQL Server |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|  |
|
|
|
|
|
Select recovery model
The first thing you need to do is figure out which recovery model you will use for your databases. This option is set for each database on your server and can be set using Enterprise Manager or T-SQL. This starting point will determine the type of backups you will run and the type of restores you can run. SQL Server has three different recovery models: Simple, Full and Bulk-Logged. To learn more about recovery models, refer to this tip: Selecting a SQL Server recovery model.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Select backup options
Next, determine what types of backup you will run. SQL Server offers four backup variations: Full, Differential, Log and File and Filegroup. Each backup option offers different levels of recoverability; the options are also based on the recovery model you select. To maximize backup and restore times, the best option to choose is a mix of Full, Differential and Transaction Log backups. This will ensure you have the ability to do point-in-time recoveries, and it minimizes the number of files to restore in case of a failure. To learn more about backup options, refer to this tip: Selecting a SQL Server backup model.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Determine backup schedule
Once you have decided on backup options to use for your databases, you can set up a schedule of when your backups will run. A good rule of thumb is to run your Full backups during off hours or very low peak times. The scheduled time of the Full backup then determine when you will run your Differential and Transaction Log Backups. If you are not sure when to run each type of backup, a good practice would be to run Full backups daily, Differentials every three hours and Transaction Log backups every 15 minutes. Each environment is different, so you should set these options based on the acceptable amount of data loss in case of a failure.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Select backup process
There are various ways you can issue a SQL Server backup. These options include:
- Maintenance Plans
- SQL Agent Jobs
- Third-Party Tools
- Enterprise Manager
- sqlmaint Utility
Each of them offer different options and each could have a use in your environment. The best approach is to schedule your backups using SQL Agent or some other scheduling tool so the backups run on a set schedule. Using the Maintenance Plan wizard will create SQL Agent jobs, or you can just schedule jobs yourself. Some of the third-party backup tools also have scheduling components built in. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Backup to disk
The fastest and best method for doing SQL Server backups is to write your backup file to a disk subsystem. In most cases, disk will always be a lot faster than tape, and you will have a recent backup copy available in case there is a need for a restore. For emergencies, usually the latest backup is what is required, so keep it close at hand. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Backup to different physical disks
In addition to backing up to disk, you should also write your backup files to other physical disks. There are two reasons for this: performance and availability. The backup process is both a read- and write-intensive process. If you spread it across different physical disks, you will see increased performance by reducing I/O as a potential bottleneck. From an availability perspective, when you store both your live database and backups on the same machine and physical disks, then if there is a machine or disk failure you can potentially lose both copies of data. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Run verify
Run the RESTORE VERIFYONLY option after the backup completes. This will validate that the backup file is a readable file and can be used for a restore. In addition, it adds a sense of security knowing your backups can be restored when needed. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Archive to tape
After you have created your backup file on disk you should then archive your backup files to tape. This ensures long-term access to your backups in case there is a need to recover to a point further in the past than what is available with the current backup on disk. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Test restores
Like all processes, testing is a key component for SQL Server backups. Even if your backups run on a set schedule, restores usually only take place in case of a failure or for refreshing dev or test environments. Often, very large databases are not restored because of lack of disk space to house another copy of the database. But, the only way to know if your backups can be restored is to periodically test restores. If you don't have the space available now, you need to find the space if the data is critical to the business. Cost of disk space continues to drop, and it's becoming more and more compact. You don't need to be concerned so much about performance, you just need to know that you can restore successfully. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Set up alerts
Another good step is to set up alerts specific to SQL Server backups. This is a good way of notifying you if there are any problems with the backup process. Here is a good article for reference along with a script for creating backup alerts on your system. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Set up operators
In order to get the benefits of alerts and notification of failures, you need to set up operators in SQL Server. More information about this can be found in SQL Server Books Online. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Safeguard backups
One of the most critical tasks for a DBA is to ensure that both online and offline copies of the databases are secure. SQL Server offers a way of securing backup files with a password that must be issued for restores. The backup file is still a text file, and if someone really wanted to get information off the file, he could do it easily with just a text editor. A better approach is to encrypt your backups as they are occurring. There are several third-party tools that offer encryption for database backups. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Compressed backups
With SQL Server databases getting larger and larger every day, there is an ever-increasing need to compress backup files. As I mentioned earlier, the cost of disks is getting cheaper, but tape costs have not changed all that much. There are several third-party tools available that compress SQL Server backups. Some claim to reduce the size of the backup file by 90%. If you have large databases, these tools can offer some great benefits. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Use history tables
How do you know what has been backed up and when? SQL Server makes this easy for you with the backup system tables in the MSDB database. You can query these tables just like any other table, and they offer a complete history of all backups that were issued.
- backupfile -- Contains one row for each data or log file that is backed up
- backupmediafamily -- Contains one row for each media family
- backupmediaset -- Contains one row for each backup media set
- backupset -- Contains a row for each backup set
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Greg Robidoux
