未加星标

Delete old SQL Server backup files with a Stored Procedure

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏
Problem

You want to remove old SQL Server backup files (older than X days) from the backup folder to free drive space. You want to do this using T-SQL and not by using a Maintenance Plan or a windows Scheduled Task. The advantage of using T-SQL is that you can keep the administrative tasks inside the database server and keep as part of a database back up when you backup the database. How can this be accomplished using T-SQL?

Solution

I chose a solution that creates a stored procedure called usp_DeleteOldBackupFiles . The procedure gets the backup files folder location (@BackupFolderLocation), the files suffix (@FilesSuffix either BAK or TRN which are the standard for database and log backup files) and the number of days old (@DaysToDelete) to delete the files (this is based on the files modified date).

For example: if a BAK file has a modified date of 03/09/2016 (mm/dd/yyyy format) and the current date is 03/14/2016 then if the @DaysToDelete = 6 the file will not be deleted. If @DaysToDelete = 4 the file will be deleted.

Since the procedure calls the DOS command (forfiles.exe), xp_cmdshell needs to be enabled.

Enable SQL Server xp_cmdshell use master
go
exec sp_configure 'show advanced options',1
go
reconfigure with override
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure with override
go

You should get the following messages in SSMS after running the above.

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install. Windows FORFILES command to delete SQL Server Backups

This FORFILES statement executes a command on a file or set of files.

The general syntax of the statement is:

forfiles [/p <path>] [/m <searchmask>] [/s] [/c "<command>"] [/d [{+|-}][{<date>|<days>}]]

We will use the suffix parameter as the search mask /m switch, the folder parameter as the path /p switch and the days parameter as the given value to the /d switch.

Stored Procedure to Delete Old SQL Server Database Backups CREATE PROCEDURE usp_DeleteOldBackupFiles (
@BackupFolderLocation VARCHAR(30)
,@FilesSuffix VARCHAR(3)
,@DaysToDelete SMALLINT
)
AS
BEGIN
DECLARE @delCommand VARCHAR(400)
IF UPPER (@FilesSuffix) IN ('BAK','TRN')
BEGIN
SET @delCommand = CONCAT('FORFILES /p ' ,
@BackupFolderLocation,
' /s /m ' ,
'*.' ,
@FilesSuffix ,
' /d ' ,
'-' ,
ltrim(Str(@DaysToDelete)),
' /c ' ,
'"' ,
'CMD /C del /Q /F @FILE',
'"')
PRINT @delCommand
EXEC sys.xp_cmdshell @delCommand
END
END
GO Example using procedure to delete SQL Server backup files

We want to remove all the .BAK files from folder C:\SQL\Backup and all its sub-folders. We want to remove all .BAK files older than 10 days. The modified date attribute has to be 10 days older than the current date.

use northwind
go
EXEC usp_DeleteOldBackupFiles @BackupFolderLocation='c:\SQL\Backup', @FilesSuffix='bak', @DaysToDelete=10

The result will be all files with a suffix BAK older 10 days from current date are deleted. The stored procedure also prints the command in the messages tab in SSMS and would look like this:

FORFILES /p c:\SQL\Backup /s /m *.bak /d -10 /c "CMD /C del /Q /F @FILE" Things to note: The procedure was tested with SQL Server 2012 and SQL Server 2014 Developer editions. The xp_cmdshell option needs to be enabled. If the option is not enabled you will get the following error message: 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'. If the backup folder directory does not exist then nothing occurs (nothing deleted) If no BAK or TRN files older than X days exist then nothing occurs (nothing deleted) Suffix allowed values are BAK and TRN. This protects from accidental deletion of other types of files. A good practice will be to deny execution on this procedure to all database users except the DBA (for security reasons). Next Steps Check out these otherbackup tips Review the SQL Server Backup Tutorial

Last Update: 10/13/2016


Delete old SQL Server backup files with a Stored Procedure
Delete old SQL Server backup files with a Stored Procedure
About the author
Delete old SQL Server backup files with a Stored Procedure
Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience. View all my tips

Related Resources

More SQL Server DBA Tips...

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLWindowsSQL ServerDUCUUTASB
分页:12
转载请注明
本文标题:Delete old SQL Server backup files with a Stored Procedure
本站链接:http://www.codesec.net/view/482809.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(35)