Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question on sp_delete_backuphistory Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 1:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:22 PM
Points: 55, Visits: 165
If I have the following:
SET @deleteday= dateadd(dd,-3,getdate())
EXEC msdb.dbo.sp_delete_backuphistory @deleteday


In the folder, currently have backups for 01/13/2013, 01/14/2013, 01/15/2013
So when the job runs, it'll delete 01/13/2013 backup from the folder.

Scenario
If I change the day from -3 to -4 before the clean up job runs tonight, the job won't delete 01/13/2013 because it's looking for 01/12/2013.
From @deleteday= dateadd(dd,-3,getdate())
To @deleteday= dateadd(dd,-4,getdate())


And if I change the day tomorrow back from -4 to -3
From @deleteday= dateadd(dd,-4,getdate())
To @deleteday= dateadd(dd,-3,getdate())

it'll essentially be looking for 1/14/2013.

My question is, will it delete 1/13/2013 AND 1/14/2013 backups? Or will it just delete 1/14/2013 backup and keep the 1/13/2013?

Thanks!
Post #1408051
Posted Thursday, January 17, 2013 1:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
It will delete both.

The parameter you are passing to sp_delete_backuphistory is telling it to delete ALL backup history OLDER than that date.



Post #1408204
Posted Thursday, January 17, 2013 4:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 6,365, Visits: 13,695
sp_delete_backuphistory deletes backup history from the MSDB backup history tables, it doesnt touch the filesystem!!

You're referring to xp_delete_file to achieve this


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1408325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse