Delete backups using SQL stored procedure xp_delete_file

Delete Backups using stored procedure

Delete backups using SQL stored procedure xp_delete_file 

There are several methods to delete older backups, Maintenance plan, stored procedures etc...
Now we will use a stored procedure to delete older backups in SQL server, In later posts we will see how to delete backups using Maintenance plan,

xp_delete_file is an extended stored procedure which purges the old backup files and we will check which backups to be retained based on history we can check the backups history from msdb backupset and backupmedia family which we will discuss in later articles.

 First check backups on which Local disk or drive they exists, check which type of backups you want to delete either .BAK or .TRN exetensions,

Xp_delete_file take five parameters:
  1. File Type = 0 for backup files or 1 for report files.
  2. Folder Path = The folder to delete files. The path must end with a backslash "\".
  3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
  4. Date = The cutoff date for what files need to be deleted.
  5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

Syntax for Delete backups using stored procedure SQL Server

TYPE 1: If you know path of backup and backup name then you can use following command

EXEC XP_CMDSHELL 'DEL <PATHNAME\BAKUP.BAK>'

Example : EXEC XP_CMDSHELL 'DEL F:\LOG.TRN'

TYPE 2: If you know path and backup name then you can delete with below command

EXEC XP_DELETE_FILE 0,<N'PATHNAME\BAKUP.BAK'>

Example : EXEC XP_DELETE_FILE 0,N'F:\BACKUPNAME.BAK'

TYPE 3: If you don't know backup name and you want to delete backups in a folder or directory use below stored procedure

EXEC XP_DELETE_FILE 0,N'D:\',N'BAK',N'MM/DD/YYYY HH:MM',1
EXEC XP_DELETE_FILE 0,N'D:\',N'TRN',N'MM/DD/YYYY HH:MM',1

Example : EXEC XP_DELETE_FILE 0,N'D:\',N'BAK',N'12/16/2015 10:15',1

Above query deletes all backups older than specified date and time with exetension .bak 

For any questions feel free to comment, Sharing is caring.


Newest
Previous
Next Post »