Agent Jobs

How To Delete Old Backup Files With TSQL And Powershell

The following logic allows you to delete old backup files with TSQL where the Powershell delete script is built.

Here’s how it works…
First; you supply the backup drive letter.

Second; you supply the backup retention in number of days. In this example we are using 30 days.

The process will then create a backup structure under whatever drive you supplied. In this example we are using H:SQLBACKUPS
Note: This logic assumes the existing backup path was created by the same process, and all backups will go into the respective path. H:SQLBAKUPS If the backup folder structure already exists; then nothing will happen. The process will simply continue with the rest of the process.

In order for you to execute Powershell scripts from TSQL you’ll need to use the xp_cmdshell extended stored procedure. Since the xp_delete_file sp is not supported in future version of TSQL, and Microsoft is pushing for a wider adoption of Powershell (as they should), then it’s best to incorporate Powershell into more automated processes, and until there is another official procedure released for this purpose xp_cmdshell is the recommended approach.

Next; the logic will check to see if xp_cmdshell is enabled.

If not enabled; it will enable it.

Then it will proceed to delete old backup files (older than the retention period).
The final step is it will disable xp_cmdshell.

-- this deletes old backup files based on a retention period in days.
-- make sure to supply the appropriate backup drive and retention period.

use master;
set nocount on
declare @drive			varchar(1)	= 'H'	-- <-- change drive letter to your backup drive
declare	@retention		varchar(2)	= '30'	-- <-- change retention to number of days you want to keep

-- sets backup location info
declare	@server_name		varchar(255)
declare	@backup_path		varchar(255)
declare	@removeoldfiles		varchar(510)
declare	@config			int 
set	@server_name		= (select replace(cast(serverproperty('servername') as varchar(255)), '\', '--'))
set	@backup_path		= (@drive + ':\SQLBACKUPS\' + @server_name)
set	@config			= (select cast([value_in_use] as int) from sys.configurations where [configuration_id] = '16390')
set	@removeoldfiles		= 'powershell.exe Get-ChildItem "' + @backup_path + '" -Recurse | Where {$_.creationtime -lt (Get-Date).AddDays(-' + @retention + ')} | Remove-Item -Force'
exec	master..xp_create_subdir @backup_path
		if	@config	= 0
				exec master..sp_configure 'show advanced options', 1 reconfigure 
				exec master..sp_configure 'xp_cmdshell', 1 reconfigure with override
exec	(@removeoldfiles)
waitfor delay '00:00:05'
exec	master..sp_configure 'xp_cmdshell', 0 reconfigure with override



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s