14

I've been researching about how to delete a specific backup file through an SQL query, but I only find results about "deleting backups older than a date". That is not what I want. I want to keep old backups, but I want to be able to delete a specific backup by its ID.

I can easily remove the entries from the msdb tables and its restore history for a given backup, but I would like to be able to delete the files as well through an SQL query (I know their full path, as it is stored in the database), so that they don't keep wasting space in the disk.

The procedure "xp_delete_file" doesn't seem to allow to delete a specific file.

I assume that if there is a procedure to delete old files, there should be some way to delete a specific file. Please don't worry about security here.

Nuno
  • 3,082
  • 5
  • 38
  • 58
  • did you have the Backup file name and Path...? – Pandian Apr 24 '13 at 09:36
  • I do. That's something pretty easy to fetch from the database. I simply would like to be able to call some "DELETE" (or equivalent) on that path. – Nuno Apr 24 '13 at 09:37

4 Answers4

34

May be old but might help someone. xp_delete_file can be used to delete specific backup file. Try the code below:

EXECUTE master.dbo.xp_delete_file 0,N'c:\backup\backup1.bak'
David Brossard
  • 13,584
  • 6
  • 55
  • 88
Vignesh Devan
  • 356
  • 3
  • 2
  • 3
    After 7 years, I'm changing the accepted answer from my own below, to this one instead. Not sure if this was available in SQL Server 2008 R2, but at this point, I think this is the answer that deserves to be accepted. Thanks very much! – Nuno Apr 07 '20 at 15:46
  • It's worth noting 1) this does not remove the device info from the database, so it would be better to use [Alexey's answer](https://stackoverflow.com/a/21067116/10243001) if the device info is still in the database, and 2) the `0` is specifying that the file to be deleted is a backup file – Ben Jasperson Sep 02 '23 at 02:24
6
--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

Alexey
  • 1,826
  • 3
  • 17
  • 20
  • 1
    i am using this method but i am not able to delete the file from server . – rahularyansharma May 02 '14 at 17:12
  • Do you get an error? Maybe you need to have enough permissions. Requires membership in the diskadmin fixed server role. http://technet.microsoft.com/en-us/library/ms188903.aspx – Alexey May 03 '14 at 07:10
4

This is what I needed.

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

It may be needed to activate the command, through:

EXEC sp_configure 'show advanced options', 1
GO

EXEC sp_configure 'xp_cmdshell', 1
GO

RECONFIGURE
GO
Nuno
  • 3,082
  • 5
  • 38
  • 58
  • As a heads-up: xp_delete_file will connect to the file you specify, VERIFY that it's a SQL Server backup file, and then ONLY delete it IF it's older than the time-stamp specified. The dos DEL command is fine as 'dumb' option - whereas xp_delete_file provides a bit of 'extra' protection/logic. – Michael K. Campbell Oct 08 '16 at 16:53
1

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, execute:

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

And your file has gone!

Physical filenames can be found in the table 'msdb..backupmediafamily'

newtrackfinder
  • 11
  • 1