Delete prior db backup in a SP
Posted on 2016-08-18
Hello All - I am trying to create a SP that will delete a DB backup before making a new backup.
I already have the code to create the new DB backup with a timestamp...what I am having a hard time with is the code to delete the prior backup before making a new one.
Anyone have a code sample on how to delete an external DB backup?
Here is the code I have tried so far. I use a wild card character so I do not need to reference the exact db name to be deleted, as the file does have a timestamp...
EXEC xp_cmdshell 'DELETE H:\TESTING\BeforeProcess_*.BAK'
The error I receive states "Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
Is it just a matter of ensuring I have execute permission to 'xp_cmdshell'? (I have already sent an email to my DBA to see if they will grant that to me?).
Just wanted to see if anyone had words of wisdom or another method/snippet of code to try?