Tuesday, 29 September 2015 00:00

Backing up SQL Server

Rate this item
(0 votes)

SQL Server backup and recovery procedures must be in place before a database crash occurs -- or precious data and company time will be lost. This comprehensive checklist will help you set up, safeguard and test your backups.

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.

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.

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

Reference: http://searchsqlserver.techtarget.com/feature/Checklist-Backing-up-SQL-Server

Last modified on Tuesday, 29 September 2015 13:51
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.