?
Solved

Delete prior db backup in a SP

Posted on 2016-08-18
6
Medium Priority
?
70 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 1000 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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

752 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