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.