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

  • Comment Link Nelson Sunday, 19 November 2017 19:31 posted by Nelson

    I am sure this piece of writing has touched all the internet visitors, its really really fastidious piece of
    writing on building up new website.

  • Comment Link Franziska Monday, 13 November 2017 10:08 posted by Franziska

    Hello! I know this is kind of off topic but I was wondering which blog platform are you using for this site?

    I'm getting tired of Wordpress because I've had problems with hackers and
    I'm looking at options for another platform.

    I would be great if you could point me in the direction of a good platform.

  • Comment Link Josef Saturday, 11 November 2017 19:52 posted by Josef

    Hi there! I know this is kinda off topic however ,
    I'd figured I'd ask. Would you be interested in exchanging
    links or maybe guest authoring a blog post or vice-versa?
    My blog covers a lot of the same topics as yours and I
    believe we could greatly benefit from each
    other. If you're interested feel free to shoot me an e-mail.
    I look forward to hearing from you! Superb blog
    by the way!

  • Comment Link Effie Saturday, 11 November 2017 03:12 posted by Effie

    Are there sliding terrace room indoors?

  • Comment Link Leigh Friday, 03 November 2017 07:04 posted by Leigh

    I have been surfing on-line greater than 3 hours these days, yet I by no means found any
    fascinating article like yours. It is pretty worth sufficient for me.
    In my view, if all webmasters and bloggers made just
    right content material as you probably did, the net might be a lot
    more useful than ever before.

  • Comment Link Raymundo Tuesday, 31 October 2017 21:51 posted by Raymundo

    I have been surfing on-line greater than three hours as of late, yet I
    by no means discovered any fascinating article like yours.

    It's lovely value enough for me. In my opinion, if
    all web owners and bloggers made excellent content material as you did, the net can be a lot more helpful than ever

Leave a comment

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