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


  • Comment Link Earl Saturday, 03 March 2018 22:22 posted by Earl

    Start looking for petting sitting, free.

  • Comment Link Petra Thursday, 01 March 2018 00:14 posted by Petra

    Hmm it seems like your website ate my first comment (it was extremely long) so I guess I'll
    just sum it up what I wrote and say, I'm thoroughly enjoying your blog.
    I as well am an aspiring blog blogger but I'm still new to the whole thing.
    Do you have any recommendations for newbie blog writers?
    I'd genuinely appreciate it.

  • Comment Link Janie Saturday, 17 February 2018 16:28 posted by Janie

    Hiring an organization like Schulhoff Tree & Lawn Care, Inc.

  • Comment Link Jarrod Thursday, 15 February 2018 00:26 posted by Jarrod

    Depend on us for skilled, safe towing service.

  • Comment Link Karin Friday, 09 February 2018 19:17 posted by Karin

    It's difficult to find well-informed people in this particular topic, but you seem
    like you know what you're talking about! Thanks

  • Comment Link Cathern Monday, 05 February 2018 19:40 posted by Cathern

    Asbestos publicity could happen while on the job.

  • Comment Link Mary Sunday, 14 January 2018 06:34 posted by Mary

    Touche. Great arguments. Keep up the great spirit.

  • Comment Link Gennie Thursday, 11 January 2018 11:00 posted by Gennie

    Can I simply just say what a relief to find somebody that genuinely understands what they are talking
    about on the net. You certainly realize how to bring a
    problem to light and make it important. More and more
    people have to check this out and understand
    this side of your story. I was surprised that you're not more popular since
    you most certainly possess the gift.

  • Comment Link Rashad Friday, 15 December 2017 06:32 posted by Rashad

    This design is incredible! You definitely know how to keep a reader amused.

    Between your wit and your videos, I was almost moved to start my own blog (well, almost...HaHa!) Wonderful job.
    I really loved what you had to say, and more than that,
    how you presented it. Too cool!

  • Comment Link daily pretox Friday, 15 December 2017 05:41 posted by daily pretox

    My spouse and I nearly failed to check this site out but I'm just glad I have. It's actually pretty good when compared to numerous others I've found. I'm going definately be back.

Leave a comment

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