Backup History in EM

Question

Backup History in EM

Aaron Templeton

SSCarpal Tunnel

Points: 4655
More actions
September 16, 2004 at 4:23 pm

#109244

I thought I'd be able to find how to do this in BOL but I apparently don't know the right keywords and can't find it stumbling around EM property boxes. We have nightly full backups with LOG backups periodically during the day. These are then written off to tape every night and then overwritten the next day. Therefore, when using SQL Enterprise Manager the last 24 hours of backup history is all that is usefull when restoring a database. I am specifically referring to the "First backup to restore:" section of the "Restore database" dialog box. Should we need to go back further than that we would need to use the "Restore: From device" section instead as the other files would have been restored to some place from tape. Our system is keeping 6 months of history which makes the "First backup to restore:" selection box take about 5 minutes to populate which is very irritating and a needless delay. Does anyone know how to limit the amount of history EM retains for this and would there be a down side to reducing it to 1 - 7 days? I know I can avoid that piece of the GUI by always restoring via "From device" and by creating a bogus database whose name sorts to the top (to be the default when the dialog box opens) and is never backed up, but that's terrible.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

hoo-t SSChampion Points: 12713 More actions · Answer 1

Here's a script you can use to delete backup history. I have it set to delete history older than three months, but you could modify it for whatever length of time you wish by adjusting the DATEADD function.

Steve

DECLARE

@his_cut_date char(10),

@command varchar(128)

-- delete backup history older than 3 months.

-- set @his_cut_date = 3 months ago

SELECT @his_cut_date = CONVERT(CHAR(10), DATEADD(MONTH, -3, GETDATE()), 101)

-- set @his_cut_date to 1st of month

SELECT @his_cut_date = SUBSTRING(@his_cut_date, 1, 3) + '01' + SUBSTRING(@his_cut_date, 6, 5)

-- delete the old history

SELECT @command = 'USE msdb EXEC sp_delete_backuphistory ' + char(39) + @his_cut_date + char(39)

PRINT @command

EXEC (@command)

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 2

Thanks a lot. Although not what I was looking for, it appears that I wasn't looking for the right thing. This does the trick very nicely. I was a little off on the time. Although it seemed like 5 minutes it was actually 2 minutes (I timed it today). After purging to 6/1/2004 (almost 4 months), which took almost 25 minutes to execute, the backup dialog box came up in under 20 seconds. Purging to 8/1/2004 (almost 2 months) brings the dialog box up in about 6 seconds. Purging to 9/1/2004 (almost 1 mongth) brings the dialog box up in about 3 seconds. The problem is definately cured now. I'll just have to set up a weekly job to retain a couple weeks of backup history.

Thanks again.

Aaron

P.S. You can avoid dynamic SQL as in the following:

declare @EarliestDate datetime

set @EarliestDate = DATEADD( WEEK, -2, GETDATE() )

USE msdb

EXEC sp_delete_backuphistory @EarliestDate

debrucer1 SSCertifiable Points: 5366 More actions · Answer 3

does this purge only help when using the GUI to execute backups? (i.e., is there any speed advantage when backups are run from script?). What else is this likely to speed up? It seems like a good idea to get rid of this history; but, where are the benefits?

Thanks,

David

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 4

I can't say for certain, but I believe it only applies to the GUI in essence. It will have SOME generic other advantages since this data is ultimately being stored in a SQL server database (MSDB I think) and so cause growth in the table, index(es), and ultimately the database. Larger table/index sizes will cause minute increases in the time to perform INSERTs and the extra data storage will cause slight, though likely negligible, additional file system space consumption. It could potentially effect third party add-ins that work with the backup process as well such as accelerators, but that would be add-in specific.

I've since scheduled this purge to run every week and it has worked very well for the last 2 years.

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

I actually like having all the backup history intact. I have found it useful as an analysis tool for backup run times and database growth because that info is stored in the backup tables. Also, it's usefule for answering questions such as: "Do backups run faster on the new hardware ?" , or "How many megs/min are we getting" or "How big was the database 12 months ago" or "Do we have a backup from last March 17 ?" (I can first look to see if a backup was taken, and if so, determine if we would still have it on tape).

I always restore "from device" so I never noticed how long that took ! Perhaps copy the backup data to non-system tables for historical information, and keep the systems tables small.

I often run this script to look at backup info:

SELECT a.server_name as 'Server', a .database_name as 'Database', convert(varchar(25),a.backup_start_date,100) AS 'Start Date', convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date', DATENAME(weekday, a.backup_finish_date) AS 'Day' , datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' , cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,3))/60 as decimal (8,1)) as 'Hours' , case when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0 then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1)) else 0 end as 'Meg/Min', ceiling(a.backup_size /1048576) as 'Size Meg' ,
cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', -- div by 1073741824 to get gig a .user_name,a.backup_size as 'Raw Size' FROM msdb.dbo.backupset a join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name WHERE a.type = 'D' and b.type = 'D' AND a.backup_start_date > '2006-04-01' group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name order by a.server_name, a.database_name, a.backup_start_date desc
Are you doing a lot of restores ?