SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Managing Your Backup Files

I recently posted a couple of  scripts that backup all databases on your SQL Server instance to disk with a date and time stamp, so you know from the file name when it was taken and hopefully helps you identify how useful those backup are to you. The second script is a script that runs through the databases in full recovery mode and takes a transaction log backup of each one.

You can then combine these scripts with the SQL Agent to create and schedule some jobs to ensure your backup schedule meets the restore requirements of your business.A ‘standard’ backup schedule, that I have seen many shops implement unless there is a specific requirement not met by this process:

Fullback – All databases daily

Transaction Log backup – Hourly throughout the day. All copied or mirrored to another server.

All of these backups are to disk are written off to tape on nightly basis and then become part of the tape cycle.

Which brings us onto the next issue, with at least 25 files per database being created per database daily, how long should they be kept on disk after they have been safely written off to tape? Well that answer, like a lot of other answers to questions in the database sphere is  ‘it depends’. It can depend on such things as how much disk space you have available for backups. How far back you are going to need to restore from? I know the backups are on tape but if you need to do a restore the time needed to perform the restore will increase significantly if the backup first needs to be recovered from tape.

If I have the space available I generally keep between 2 and 3 days worth of backup files. The following script, is a modified version of Andy Leonards (BLOG | @AndyLeonard ) that i use for deleting old files

declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
declare @BackupPath nvarchar(255)
Declare @FileType NCHAR(3)
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = ( Select Replace(Convert(nvarchar, @DeleteDateTime, 111),
'/', '-') + 'T'
+ Convert(nvarchar, @DeleteDateTime, 108)
set @BackupPath = N'E:\Backup\'
set @FileType = N'bak'
EXECUTE master.dbo.xp_delete_file 0, @BackupPath, @FileType, @DeleteDate, 0
--the final parametes is will allow the Extended Proc to drop to the 1st level of subfolders,
--you need to set it to 1 to delete from the 1st level of subfolder

The final parameter in the extended stored proc allow you to specify the proc delete from the first level subfolders in the folder specified. Again this can be scheduled in a SQL Agent Job and run in conjunction with the full and transaction log backups.


Posted by robert.carter on 31 March 2010

Not to rain on your parade, but tape is not a suitable backup media because of magnetic print through. Two tb hard drives are much more trustworthy. Promise Tech used to make a little rack that will hold 15 hard drives; some of my relatives still use this approach

Posted by Gethyn Ellis on 31 March 2010


Your not raining on my parade, as long the backups are stored off server, preferably on a separate site then go with works for you.

Just be clear though, I am backing to disk storage first, then writing the backup file to tape. At some point and it has to be what best suits your business needs you will need to perform maintenance on those disk backups, deleting older files and clearing space for new backups

Posted by awoodhouse on 31 March 2010

I've always been of the opinion - if something already exists, why do it again. Ofcourse there are a few exceptions to this.

My databases are backed up using the Maintenance plan, which do exactly what you have described. They backup to disk, the disk is then backed up to tape. And everytime new backups are created, the last X days worth are removed from the disk.

The only difference i can see, is that your script backs up ALL databases, and ALL transaction log files. But, in my case we have some databases i don't want backed up, or backed up less frequently as they are only used monthly.

And the backup file names are in the format <database name>_<type of backup (db/log)>_<datetime>.BAK.

Given that the functionality already exists, granted it may take a bit of effort to set up, but it gives you some flexibility - why re-create something that already exists out-of-the-box?

Posted by Gethyn Ellis on 1 April 2010

The scripts are very easily modified to meet the needs that you describe. The advantage of scripts of maintenance plans is they offer more control and more flexibility. For example when taking a full backup you want to specify it with the  WITH CHECKSUM option enabled, then this is not available to you in a maintenance plan.

That said Maintenance plans do have their place and if they meet your needs there is no reason not you use. Brad McGehee has a book on Maintenance Plans that I think would interest you, it is available as an ebook from this site www.sqlservercentral.com/Books

Leave a Comment

Please register or log in to leave a comment.