Tip

SQL Server backup and restore, part II: Best practices

[Part I of this article can be found here.]

Selecting the recovery model and backup options can be simple for your SQL Server implementation. The best scenario is to select

    Requires Free Membership to View

the options that provide the most flexibility. The following are some guidelines that can be used for selecting the appropriate backup and recovery model as well as some additional considerations to institute.

Recovery Model Selection

If you are unsure what recovery model to use, the best bet is to implement the FULL recovery model. This option is the most flexible and gives you the most options. It allows recovery for the most amount of data in the event of a failure. Even if the FULL recovery model is selected, you are still free to choose the individual implementation backup options.

Backup Options

The best method is to perform full backups as often as possible depending on the size of your database, along with differential backups and lastly with a combination of transaction log backups. The frequency is dependent on your environment and the acceptable data loss for your company. If you have extremely large databases it will be difficult to execute frequent full backups, so you need to look at a combination of options.

A good starting point might be the following backup schedule:

  • Execute a full database backup on a daily basis
  • Perform transaction log backups every 15 minutes during the business day
  • Complete differential backups every 4 hours

Rationale: The differential backups will minimize the number of transaction log restores needed. If you backup the transaction logs every 15 minutes, a maximum of 15 transaction logs would need to be restored. The worse case scenario would be 18 restorations in order to bring your database online and running. The 18 restorations would be one full, one differential, the 15 transaction log restores and one last transaction log. This last log would be from your attempt to backup your active transaction log if possible, before you begin your restores.

Backup to Disk First

Backing up databases to disk first gives you the fastest means for performing database backups and restores. In the event that a database restore is needed, the most recent backups are on disk instead of having to request tapes to complete the restoration.

Disk backups give the DBA the most control. As a DBA you will have more control over the backup schedule. You know exactly when backups are going to start and exactly when they should finish. You do not need to worry about other variables outside of your database server to determine when and if good backups occurred. It is still necessary to coordinate with your Backup Operator in your organization to make sure the tape backup occurs after the database disk backup, so the latest backup is stored on tape.

When backing up the databases to disk, ensure you are backing up the database to a different physical drive on a different controller card. In the event of a disk failure, if you use the same drives for the database and backup you risk the loss of your databases and backups.

If you have the ability to backup to a different machine or a network appliance, this option allows for superior level of redundancy in the event of a failure.

Archive to Tape

Several backup vendors in the marketplace can assist with managing and verifying the success of your tape backups. These products should be part of your backup strategy, but should read the backup files from disk instead of executing backups directly from your databases. Several vendors offer external agents to read the SQL Server databases directly, but the recommendation is to write the backup to disk first and then to tape second. This method also gives you two full sets of backup files.

Test

Irregardless of the backup method, it is advantageous to periodically test the validity of the backups. It is in your best interest to randomly select databases to restore onto test servers to ensure the restore functionality works properly and is meeting your expectations. The more frequently restoration testing is conducted, the better you will prepared for a real recovery.

Verify

Take the time to verify the backup is valid. The verify option listed above allows you to have peace of mind that the backup was successful. The last thing you want is to find out that the backup will not successfully perform the restoration. Take the extra time to run the RESTORE with VERIFYONLY option to ensure the backup was successful and is available when needed.

System and User Databases

Ensure the backup procedures include all system and user databases. In the event of a complete system failure, it will be necessary to restore the system databases along with your user databases in order to recreate the SQL Server environment.

Reader Feedback

Ferenc D. writes: I found this tip useful, especially the warning on verifying the backup. Unfortunetly a lot of times I get corrupted backups. I had weeks when out of six daily backups, four were corrupted. The restore verify suggested statement acually was a good way to have immediate verification after backup. My database size is about 4Gb-6Gb. Daily backup is done, but daily restore just for testing would be to much. I restore the backup to test servers, but not every day. Thanks for the tip.

About the Author

Greg Robidoux is the founder of Edgewood Solutions, a database solutions company in the United States, and is currently the Vice Chair of the PASS DBA Special Interest Group. He has 14 years of IT experience and has been working with databases for the last 10 years with the past four years of that with SQL Server. Greg's primary areas of focus are setting standards, disaster recovery, security and change management controls. In addition to these areas he has experience with replication, storage areas networks and SQL Server upgrades. Greg can be reached at gregr@edgewoodsolutions.com.

 

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

 

This was first published in February 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.