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