Solved

Delete prior db backup in a SP

Posted on 2016-08-18
6
54 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 40

Accepted Solution

by:
lcohan earned 250 total points
ID: 41761420
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 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41762145
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
ID: 41762366
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41762462
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
ID: 41762494
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
ID: 41762499
Thanks all for your assistance!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

713 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