Friday, 8 May 2015

Remove previous Microsoft SQL backups automatically


Windows Server 2008R2 with SQL Server 2005


Every time backup runs it creates new .bak file, old backups are never removed.


1. Open up "Microsoft SQL Server Management Studio"
2. Edit your job
3. Go to Steps and add New...
4. Select Type: ActiveX Script, select VBScript
5. Paste the following code, make sure to change the path, you can use UNC path
Option Explicit
'Delete all SQL Server backup files more than 8 days old

Dim oFS, oSQLBackupFol, oFol, oFil
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oSQLBackupFol = oFS.GetFolder("D:\MSSQL\Backup")   'Change this as appropriate

For Each oFol IN oSQLBackupFol.SubFolders
      For Each oFil in oFol.Files
        If oFil.DateCreated < Now-8 AND Ucase(right(, 3)) = "BAK" then
        End If           

set oFol = nothing
set oSQLBackupFol = nothing
6. Make sure Start step: is correctly assigned


No comments:

Post a Comment