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 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.
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.
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.
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.
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