asked on

Delete prior db backup in a SP

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?

Thanks!

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cynthia Hill

ASKER

Hey Guys - Thanks for your comments so far!

The backups I am making are in addition to the backups made by the maintenance plan. The automated backups for the maintenance plan do not keep history for the end of the prior month, which I feel I need to fully ensure I will always be able to get to the backup I need (my databases are updated with new data once a month).

Because the date can vary on the day I need to make the monthly backup (the day the database is fully updated)...I have worked it into my SP's to make my month end backups separately (so they will always be made on the day I need them made and so the maintenance plan will not delete them before I am ready to release them).

The three people who would have access to run the SP, should have the same level of access as I do. They already have access to the shared drive the backup is made to and has the same global profile for SQL Server as I do (highest security profile the DBA will allow us to have), so if they can grant it to me...granting to the two others is fine as well. No other users are allowed to and do not have proper security to run SPs.

If you guys do not have any thoughts on code that could help me delete the backup in a SP, then maybe having the DBA set up a new maintenance plan for my additional monthly backups might be the way to go?

I was just hoping there was a snippet of code I could use to make things easier for me to get set up(?)

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cynthia Hill

ASKER

Would probably need to be a separate plan...these backups would be saved to a separate drive and I would probably want to call the job from my SP so I can still run the job when I am ready (once the DB is all set to go for the month)...as the date for this varies just depending on the flow of data loads /audits for the month.

I guess that looks like the way to go since no one has offered up any code options. Thanks

Cynthia Hill

ASKER

Thanks all for your assistance!