Tip

Best practices for SQL Server backup maintenance

Part of your regular backup procedures should include removing old database backup history from the msdb database. That reduces the size of the msdb database and improves performance of the Enterprise Manager or Management Studio applications when you use them to restore.

This maintenance should be done via the sp_delete_backuphistory procedure in the msdb database. Microsoft provides it through the current

    Requires Free Membership to View

CTP of Microsoft SQL Server 2008. The procedure accepts a single parameter called @oldest_date, which is the oldest date's backup information to keep. Differences between full backups, differential backups and transaction log backups are not shown. So, by running the command written below, you could end up with entries in the msdb database for transaction log backups and no corresponding full backup. That's fine because the backups don't have to be listed in the msdb tables in order to be restored to the SQL Server.

The maintenance command is as follows:

exec sp_delete_backuphistory '1/1/2005'

Another handy procedure is sp_delete_database_backuphistory, shown below. This removes all backup history for the database specified and should be used when databases leave production and are dropped from the server. For example:

Note: The examples are based on a DBA who works for Northwind Traders and has a database named Northwind.

colorexec sp_delete_database_backuphistory
'Northwind'

Backup database commands vs. maintenance plans

Although many database administrators have a preference, there is no actual difference with regard to backups between writing jobs that call the BACKUP DATABASE and BACKUP LOG commands and SQL Server maintenance plans.

More on SQL Server backup and restore:
  • Speed up SQL Server backup and restore time
  • Disaster recovery features in Server SQL 2005
  • SQL Server backup encryption for improved security
  • Both provide backups that you can restore using the RESTORE DATABASE command. Either method can be easily set up. Although maintenance plans provide a nice wizard to walk you through them, they do not allow for all the available options that BACKUP DATABASE and BACKUP LOG commands provide.

    If you have worked on SQL Server 6.5, you'll remember having the ability to mirror the backup across multiple destinations to increase redundancy of the backup job. Microsoft restored this functionally in Microsoft SQL Server 2005 where it's done with the MIRROR TO clause. In using the MIRROR TO clause, the FORMAT switch must be included when creating the initial mirrored backup set. Like the regular destination, you can specify UNC paths to a network share on another server.

    BACKUP DATABASE Northwind TO DISK='C:\
    northwind.bak' MIRROR TO DISK='\\
    SecondaryServer\c$\ northwind.bak'

    When using the MIRROR TO clause, either DISK or a backup device can be specified. If any of the paths listed in the backup command are not available, the BACKUP DATABASE command will fail with an error message.

    This tip was excerpted from Selecting the right SQL Server recovery model.

    SQL Server database administrators must have a backup and recovery strategy in place and they must be certain it's the best plan for their SQL Server environment. Choosing the right recovery method is imperative for minimizing, or even preventing, data loss. In this edition of SQL Server INSIDER, expert Denny Cherry examines the backup and recovery strategies available and discusses how to make the correct decision for your SQL Server environment. Read this ezine from SearchSQLServer.com for tips on selecting the right backup method. Learn how to choose from the six main backup methods in use today:

    • File-level backups
    • Full backups only
    • Full backups with differentials
    • Full backups with transaction logs
    • Full backups with differentials and transaction logs
    • File group backups

    Download this issue

    ABOUT THE AUTHOR:   
    Denny Cherry is a DBA and database architect managing one of the largest SQL Server installations in the world, supporting over 175 million users. Denny's primary areas of expertise are system architecture, performance tuning, replication and troubleshooting.
    Copyright 2007 TechTarget


    This was first published in October 2007

    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.