SQL DBA learning curve

issue :

some times the maintance plans do not delete old Backups files (based on their age) ,that we have intended it to do

also the delete maintance task (subtask) may also skip files .. based on their creation date or AGE ..

manually deleting the old .BAK or any other files is not really inspiring after a certain time ..

Solution : 

we can use Powershell (PS) to help us acheive the same result .. with few lines ..

SETUP :: im backing up the databases into a test folder .. but the PS script can used to do the same tasks for any storage filer server .. that you must be using to store backups — JUST MAKE SURE YOU PASS the CORRECT PATH ..

use below script to backup all database to a sepcfic path

USE master

GO

DECLARE @int varchar(15)

declare @Rand varchar(15)

DECLARE @SQL varchar(200)

declare @path varchar(200)

DECLARE @Cur varchar(20)

declare curr CURSOR FOR

SELECT name from sys.databases where database_id <> 2

OPEN curr

FETCH NEXT

FROM curr INTO @cur

WHILE @@FETCH_STATUS = 0

BEGIN

–declare @var char(20)

PRINT @cur
–set @var = @cur

set @int = convert(varchar(20),getdate(),112)

set @rand = substring(convert(varchar(10),rand()*1000,112),0,4)

set @path = ‘D:\POWERSHELL\TEST\New Folder\’+@cur+’_’+@int+’_’+@rand+’.BAK’

–print @path

backup database @cur to disk = @path
FETCH NEXT

FROM curr INTO @cur

END

CLOSE curr

DEALLOCATE curr

GO

Above script is send all full backups to path –> D:\POWERSHELL\TEST\New Folder\    (choose any path .. that works for you !!!)

once we have backups in place ..  (or we know which Backups are to deleted according to their age ) .. start you powershell ..

START –> RUN –> POWERSHELL.EXE    (will work on all machines above windows XP,Server 2003 ) .. for this 2 .. you need to download and install

you do not have to change you path .. to the folder .. from where you want to delete files .. (example in case of external storage location which is accessed like \\DATADOMAINSERVER01\SQLBACKUP\DAILY_Backups\) .. you can run the powershell script ..  from wherever your current path ..

in my case im switching to the location which holds the path .. because i need to show few options ..

the -Recurse option present in DIR or get-CHILDITEM is able to traverse through all sub folders under the parent folder ..

hence with this option you will be able to delete old files in a external storage path like —

\\DATADOMAINSERVER01\SQLBACKUP\DAILY_Backups\

below results show .. that DIR – Recurse is able to trverse through D:\POWERSHELL\TEST\ and a subfolder named New folder

(we excluded the BAK files .. so as to minimize the outcome)

below command will give you backups created 3 hours back .. you will be looking for much older backups and use below command

which works with days instead of hours  (i have used below .. as a DEMO )

 USE addhours(-30)   (to get a count of files and folders which creation date older then 30 days )

get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse | Where-Object {$_.creationdate-lt ($(getdate).adddays(-30))} | measure

NOW :  we only want to work with .BAK files and possibly may like to exlcude some files from deletion command … so we will use -include *.BAK (or *.SAFE) and -Exlcude  (to exclude any type of files example *.PS1 )

if you use SQL SAFe for backups or any other Backup utility use the required extention

use below command

basically .. we are using -lt (less then comparision) to filter out files older then 30 days .. (in example its older then 2 hours)

below command will give .. only 2 files .. of type *.BAK ..

 get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK -exclu
de *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | select -First 2

use below command to get a exact count of *.BAK files .. you will be deleting

 get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK

-exclude *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | measure 

if you trying to get a count of files older then 30 days .. on large file server .. above command will take some time to complete ..

———-command to delete files older then certains Days ————————

********** take care of below points **************

1> mention correct path from where you want to delete backup files (even sub-folder will be taken into account)

2> mention -include *.BAK .. to make sure only backup files are deleted .. also if needed exclude any application or config file

if its possibly on the mentioned path ..

3> make sure to mention correct AGE or no. of days you want to delete files  adddays(-30)) .. means you want to delete a month old files or 2 months old files ETC 

****************************************************************

 get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK -exclude *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | remove-item -force

****************************************************************

 

Above command will delete the backup files of type (*.BAK) and exclude the files of type (*.PS1)  older then 30 days

under path D:\POWERSHELL\TEST\New Folder and any sub folder .. it may have .. 

try out this command .. and let me know your thoughts ..

ENJOY 🙂