Script to backup and remove files that are more than a week old

Question

question

chitrarekhasaha asked Nov 20, '18

Script to backup and remove files that are more than a week old

Can someone share a database backup script where one database exist in the client machine and we can remove the old backup with is more than 7 days old. I have used Management studio to backup and clean but it would be nice to write a script and add to the task of all servers. I have found several script on database backup but do not know how to script for the removal of old backup.

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'E:\Backup\FullBackup'

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('IMSA','IMSA Fund','MWFMattachment','MWFMData','mwfmplay','MWFMSys','NpsSqlSys','NTO','SSA','SSAMC','Student Activities') -- exclude these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

script

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-11-21T11:53:39Z

KenJ answered Nov 21, '18

Rather than rolling your own backups, I would recommend that you use either Ola Hallengren's backup solution or MinionWare backup.

This parameter in Hallengren’s script will create a 7 day cleanup window:

@CleanupTime =168

To me, Hallengren's is little simpler to get up and running while the table-driven nature of MinionWare makes it quite a bit more flexible and the central management is handy for larger SQL Server estates.

Here is what your backup would look like after installing Hallengren's solution:

EXECUTE dbo.DatabaseBackup
  @Databases = 'USER_DATABASES',-- or insert your comma-separated list of databases
  @Directory = 'E:\Backup\FullBackup',
  @BackupType = 'FULL',
  @Verify = 'Y',
  @Compress = 'Y',
  @CheckSum = 'Y',
  @CleanupTime = 168

The installation process can even set up the SQL Agent backup jobs for you - just enable the jobs when it's done and make sure the SQL Server account has read/write/delete permission to the backup location (probably not an issue when backing up to a local drive - you should consider backing up to a network share so the backup files aren't on the same system as the databases they are backing up).

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chitrarekhasaha commented · Nov 27, 2018 at 08:17 PM

The script works after I installed

Ola Hallengren's backup solution and I run the following script from Management studio. What process did you use to run the script from Task Scheduler.?

EXECUTE dbo.DatabaseBackup
  @Databases ='USER_DATABASES',-- or insert your comma-separated list of databases@Directory ='E:\Backup\FullBackup',@BackupType ='FULL',@Verify ='Y',@Compress ='Y',@CheckSum ='Y',@CleanupTime =168

0 ·

KenJ commented · Nov 28, 2018 at 12:18 AM

Just saw your comment. I replied in your follow up question.

In short, hallengren’s FAQ page has an example of how to run the commands from a cmd shell if you can’t run the commands directly in sql agent

0 ·

question