Question on sp_delete

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Question on sp_delete_backuphistory

Question on sp_delete_backuphistory

Rate Topic

Display Mode

Topic Options

Author

Message

MSSQL_NOOB

Posted Wednesday, January 16, 2013 1:01 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 38, Visits: 111

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

Ian Scarlett

Posted Thursday, January 17, 2013 1:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:12 AM
Points: 1,322, Visits: 4,400

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

Perry Whittle

Posted Thursday, January 17, 2013 4:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:53 AM
Points: 5,204, Visits: 11,158

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" Wink

Post #1408325

« Prev Topic | Next Topic »

Permissions