Solved

Delete prior db backup in a SP

Posted on 2016-08-18
6
44 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
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 47

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Webservices in T-SQL 3 31
Linked Server Issue with SQL2012 3 26
Query Syntax 17 34
transaction in asp.net, sql server 6 33
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

770 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