Delete specific backup file through SQL

Question

I studied how to delete a specific backup file using an SQL query , but I only find results about deleting backups older than the date. This is not what I want. I want to keep old backups, but I want to delete a specific backup by its identifier.

I can easily delete records from msdb tables and their recovery history for this backup, but I would like to be able to delete files also through an SQL query ( I know their full path , since it is stored in the database), so that they don’t lose disk space.

The procedure "xp_delete_file" does not seem to allow the deletion of a specific file.

I assume that if there is a procedure for deleting old files, there must be some way to delete a specific file. Please do not worry about security here.

Answer 1

This is what I need.

xp_cmdshell 'del c:\backup\file.bak' 

You may need to activate the command using:

 EXEC sp_configure 'show advanced options', 1 GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO 

Answer 2

It may be old, but it may help someone. xp_delete_file can be used to delete a specific backup file. Try using the code below:

 EXECUTE master.dbo.xp_delete_file 0,N'c:\backup\backup1.bak' 

Answer 3

 --Define a backup device and physical name. USE AdventureWorks2012 ; GO EXEC sp_addumpdevice 'disk', 'mybackupdisk', 'c:\backup\backup1.bak' ; GO --Delete the backup device and the physical name. USE AdventureWorks2012 ; GO EXEC sp_dropdevice ' mybackupdisk ', 'delfile' ; GO 

http://technet.microsoft.com/en-us/library/ms188711.aspx

Answer 4

Create a backup device with a physical name that points to the backup file:

 exec master..sp_addumpdevice @devtype = 'disk', @logicalname = '<logical_name>', @physicalname = '<path + physical filename>' 

Then do:

 exec master..sp_dropdevice '<logical_name>', delfile 

And your file is gone!

Physical file names can be found in the table "msdb..backupmediafamily"