Agent Jobs

Delete Old Backup Files With TSQL And Powershell

If you’re a DBA like me you’ll probably have a local database instance on your workstation so that you can run a variety of tests quickly on whatever project you’re working on. In some cases you might even have a quick backup script so you can save some of your databases safely to your drive.

You’ll probably need to run a process that deletes the old database files based on a certain date. Here’s some quick SQL Logic which drops the old files from your backup folder based on a retention date. It’s based on a Powershell script, and the script will automatically add your backup path and retention date based on the supplied variables.

In this example the backup_path is C:SQLBACKUPS

Here’s the quick view before the Powershell script runs from xp_cmdshell, and after…

use master;
set nocount on

declare	@backup_path		varchar(255)	= 'C:\SQLBACKUPS\'
declare	@retention		varchar(3)	= '0'
declare	@config0		int
declare	@config1		int
declare	@config2		int
declare @removeoldfiles		varchar(555)
set	@config0		= (select cast([value_in_use] as int) from sys.configurations where [configuration_id]	= '518')
set	@config1		= (select cast([value_in_use] as int) from sys.configurations where [configuration_id]	= '16390')
set	@config2		= (select cast([value_in_use] as int) from sys.configurations where [configuration_id]	= '1579')
if	@config0		= 0 begin exec master..sp_configure 'show advanced options', '1'	reconfigure end;
if	@config1		= 0 begin exec master..sp_configure 'xp_cmdshell', '1'			reconfigure end;
if	@config2		= 0 begin exec master..sp_configure 'backup compression default', '1'	reconfigure end;
set	@removeoldfiles		= 
	'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command "&{Get-ChildItem ''' 
	+ @backup_path	+ ''' -Recurse | Where {$_.creationtime -lt (Get-Date).AddDays(-' 
	+ @retention	+ ')} | Remove-Item -Force}"'

-- check files before deletion
exec	master..xp_dirtree	@backup_path, 1, 1

-- run file deletion with powershell
exec	master..xp_cmdshell @removeoldfiles;

-- check files after deletion
exec	master..xp_dirtree	@backup_path, 1, 1
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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