Cleaning Backup and Restore History

Every time you do backup and/or restore on your SQL server, entries are made to several tables in the msdb database. These things can really add up really quickly, especially if you do a lot of backups (like backing up the transaction log every 15 minutes) or restores (hopefully those restores were done on your development servers, instead of production servers). Obviously, this will cause your msdb database to grow over time. In addition, overtime, if you are using backup/restore UI on SQL Server Management Studio, you might notice slower performance when trying to retrieve the backup history.

By default, SQL Server would maintain the backup and restore history indefinitely. It is a good idea to clean up these backup and restore history periodically. There are a couple of ways of doing this.

Stored Procedures

There are a couple of stored procedures in msdb database that can be use to clean the tables in msdb database related to backup and restore.

sp_delete_backuphistory

This stored procedure accepts one parameter, which is the oldest_date (date time data type). This stored procedure would delete backup and restore history that are older than the date time value that you’ve specified on the oldest_date parameter.

EXEC msdb.dbo.sp_delete_backuphistory [@oldest_date =] ‘oldest_date’

For example, let say that you want to delete backup and restore history that are older than April 1, 2010, you can use the following query:

EXEC msdb.dbo.sp_delete_backuphistory ‘4/1/2010’

sp_delete_database_backuphistory

This stored procedure can be use to delete backup and restore history for a given database. It accepts one parameter, which is the database_name.

EXEC msdb.dbo.sp_delete_database_backuphistory [@database_name=] ‘database_name’

For example, let say that you want to delete backup and restore history related to AdventureWorks database, you can use the following query:

EXEC msdb.dbo.sp_delete_database_backuphistory ‘AdventureWorks’

Both stored procedures would delete entries from the following tables:

• backupfile
• backupfilegroup
• restorefile
• restorefilegroup
• restorehistory
• backupset
• backupmediafamily
• backupmediaset
• logmarkhistory (by way of delete trigger on backupset table).

For more information on what these tables are used for, you can go here (http://msdn.microsoft.com/en-us/library/aa260604(SQL.80).aspx).

Maintenance Plans

Alternatively, you can create maintenance plan to delete backup and restore history. All you need to do is to create maintenance plan that include History Cleanup Task. One thing to note is that History Cleanup Task does not only delete the backup and restore history, but also delete SQL job history and maintenance plan log. It executes these three stored procedures:

• sp_delete_backuphistory
• sp_purge_jobhistory
• sp_maintplan_delete_log

Take Away

It is a good idea to clean backup and restore history periodically. You can either create and schedule maintenance plan to run on regular basis or create and schedule SQL job using sp_delete_backuphistory stored procedure.

Backup with Copy_Only Option

Copy_Only option was introduced as one of the backup option starting with SQL Server 2005. It is a pretty useful backup option that I have been using it for a while now. When you back up your database or log with Copy_Only option, it will back up the database or log, and more importantly it will not disturb the backup sequence/routine that you might already have.

How is this useful?

Let say that you have established a routine in which you do a full backup on your database every once a week on Monday morning at 7:00 AM. You also have set up a differential backup to be done once a day at 8:00 PM. Then come Wednesday morning, your application vendor/developer manager is asking for the latest backup of your database to help to troubleshoot an application problem.

What to do?

There are several things that you can do. First you can immediately take a differential backup, and send the Monday’s full database backup and the newly created differential backup to your application vendor/developer manager.

Alternatively, you can also immediately take a full database backup, and send that full database backup to your application vendor/developer manager. However, one thing that you need to do is you need to make sure that you keep that full database backup. It is because the subsequent differential backups will depend on that full database backup that you’ve just take. (I guess a little bit of refresher is in order here. Differential backup contains changes that happen since the last full backup.) It’s just one more thing to worry about.

This is where the Copy_Only option can come in handy. In this scenario, you can use the Copy_Only option when taking the full database backup. The advantage of this is that this will not affect the subsequent differential backups. Those differential backups will still be based on the Monday’s full backup that you have. Let say that on Friday morning, you encountered a data corruption on that database. And you want to restore up to the Thursday differential backup. You can just restore the database from the Monday’s full backup and then restore the Thursday differential backup, and you should be golden.

Demonstration (Without Copy_Only option)

For this demonstration I am using AdventureWorks database. Let say that I have the following backup routine:

• Full Backup #1
• Differential Backup #1
• Differential Backup #2
• Full Backup #2
• Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_3.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

As expected, we will get the following error:

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Now, let’s look at why this is happening. Try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

The result is as follow (you might get a different set of result):

name	type	is_copy_only	first_lsn	differential_base_lsn
Full Backup #1	D	0	260000000007500037	NULL
Differential Backup #1	I	0	260000000009700034	260000000007500037
Differential Backup #2	I	0	260000000011200001	260000000007500037
Full Backup #2	D	0	260000000011600037	NULL
Differential Backup #3	I	0	260000000013700034	260000000011600037

You will notice that the Full Backup #1 has first_lsn of 260000000007500037. (LSN is Log Sequence Number). And if you noticed on the differential_base_lsn, the Differential Backup #1 and Differential Backup #2 are also having the same LSN of 260000000007500037. This would indicate that the Differential Backup #1 and Differential Backup #2 are based on Full Backup #1. You will need Full Backup #1 in order to restore from Differential Backup #1 or Differential Backup #2.

Notice also that once you take the Full Backup #2, the subsequent differential back up (Differential Backup #3), has a different base LSN than the Differential Backup #1 or Differential Backup #2. In order to restore from Differential Backup #3, you need Full Backup #2.

Demonstration (With Copy_Only Option)

Let say that I have the following backup routine:

• Full Backup #1
• Differential Backup #1
• Differential Backup #2
• Full Backup #2 (With Copy_Only Option)
• Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2 (With Copy_Only)', COPY_ONLY;

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_3.bak
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

It went successfully. So what happen? Let’s try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

Here’s the result:

name	type	is_copy_only	first_lsn	differential_base_lsn
Full Backup #1	D	0	260000000015600037	NULL
Differential Backup #1	I	0	260000000017800034	260000000015600037
Differential Backup #2	I	0	260000000019300001	260000000015600037
Full Backup #2 (With Copy_Only)	D	1	260000000019500001	NULL
Differential Backup #3	I	0	260000000019700001	260000000015600037

As you can see, unlike a regular full database backup, when we use the Copy_Only option, the subsequent differential (Differential Backup #3) is still based on the Full Backup #1. Looking at the differential_base_lsn column, you can see that.

Take Away

You can use Copy_Only option with your backup for that “one off” situation, where you need to take a backup without disturbing your routine backup strategy.

Some Notes

• You can use Copy_Only option when you are doing full database backup or transaction log backup. You can not do it with the differential backup. If you put the Copy_Only option when you do the differential backup, SQL Server will ignore the Copy_Only option.
• You can not use the full backup that you take with the Copy_Only option as the base to restore subsequent differential backup.
• However, you can use the full backup that you take with the Copy_Only option as the base to restore subsequent log backup(s).

Setting Environment Path for Log Parser

Microsoft Log Parser is a nifty little command line tool that can be use to parse and query text-based log files, such as IIS log, windows event viewer, SQL Server error log and others. I’ve mentioned this before, one thing that I like about Microsoft Log Parser is the use of SQL language that we can use to query the log files (I guess I am a tad biased).

After installing Microsoft Log Parser, in order to use it, you need to go to command line application and go to the C:\Program Files\Log Parser 2.2 (Version 2.2 is the latest version of Microsoft Log Parser). If you do it from any other directory, you will get the following message:

‘LogParser.exe’ is not recognized as an internal or external command, operable program or batch file.

To get around this, in order for you to be able to run Log Parser to run from any directory that you have open when using command line, you can set the Log Parser directory as one of the environment path. To do that in Windows Vista system:

• Go to Start menu and then Control Panel.
• In Control Panel, select System and Maintenance.
• Under System and Maintenance, click on System.
• On the left side, there should be Advanced System Settings. Click on that one and the User Account Control window might pop-up. Click on Continue on this.
• On the System Properties window, under the Advanced tab, click on the Environment Variables button.
• On the Environment Variables window, under the System variables section, look and click on Path. Once Path is selected, click on the Edit button.
• You should see a window something like the one below. At this point go to the very end of the variable values field and type in semicolon and the C:\Program Files\Log Parser 2.2.

• Once done, click on the OK button. Then click on the OK button on the Environment Variables window. Then click on the OK button again on the System Properties window.
• If you have any command line open you might want to close them, or start a new one and by now you should be able to use the Log Parser tool from any directory other than the C:\Program Files\Log Parser 2.2 directory.

Related Posts:

• Querying SQL Server Agent Error Log with Microsoft LogParser
• Querying SQL Error Log with Microsoft LogParser