Solved

Delete prior db backup in a SP

Posted on 2016-08-18
6
33 Views
Last Modified: 2016-08-19
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!
0
Comment
Question by:CyHill
  • 3
  • 2
6 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
Comment Utility
I believe is more than that as the user running it will also need folder(s)/file(s) level permissions where these backups are.

I think this is more like a DBA task as you just mentioned that you sent an email to the DBA and they should have a Maintenance plan rather than SQL SP where there are Backup FULL/TLOG and Cleanup tasks to do just this so you don't have to do it via xp_cmdshell.

If you want to get backup info via SQL query you can use this:

SELECT          physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
--WHERE database_name = 'YourDB'
ORDER BY backup_finish_date DESC
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
I am trying to create a SP that will delete a DB backup before making a new backup.
Why before and not after?
Imagine that you delete the last backup and the new one for some reason you couldn't make it. You'll stay with no backups at all.
I highly recommend you to review your backup process since there is a big risk with this one.
0
 

Author Comment

by:CyHill
Comment Utility
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(?)
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
maybe having the DBA set up a new maintenance plan for my additional monthly backups might be the way to go?
If you already have a maintenance plan for the backups I think you can just add another schedule for the additional monthly backups (a job can have more than one schedule).
0
 

Author Comment

by:CyHill
Comment Utility
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
0
 

Author Closing Comment

by:CyHill
Comment Utility
Thanks all for your assistance!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now