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,
EXEC XP_CMDSHELL 'DEL <PATHNAME\BAKUP.BAK>'
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'>
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
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:
- File Type = 0 for backup files or 1 for report files.
- Folder Path = The folder to delete files. The path must end with a backslash "\".
- File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
- Date = The cutoff date for what files need to be deleted.
- 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 commandEXEC 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.
This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.
ConversionConversion EmoticonEmoticon