SQL Backup file retention, w/ERASEFILES and FILEOPTIONS
plavalle
Posts: 8
We have a mission-critical 350+ GB DB that we backup (FULL) nightly; splitting the backup into four SQB files, via the “TO DISK†option. This produces four 20GB SQB files (excellent compression) and completes in about 20 min. (excellent performance, w/Compression Level 2 and 356 bit encryption)! The ability to execute four backup threads in parallel is just TOO GOOD not to use…
The backup files are stored on a LOCAL partition on the SQL Server (a RAID-10 array) with approx. 400 GB of total space; additionally they are also stored on a NAS device for 2nd level, “near-lineâ€, recovery. Also; the NAS device is attached to a tape backup system, for long-term archival. The NAS device has approx. 1 TB of total space that is dedicated to our SQL Server Group, for backups. Since splitting a backup into multiple SQB files and the MIRRORFILE option are mutually exclusive; we use the COPYTO option, to copy the four nightly SQB files to the NAS device.
The problem is that, due to the space limitations on the SQL Server’s LOCAL Backups partition; we can only keep (at most) three days of backups, FULL and LOGS (every 15 min.) on the Server. Whereas; on the NAS device, we would like to keep at least a full week of backup files. Is there any way to accomplish this goal of different retention periods for each location; using the ERASEFILES and FILEOPTIONS parameters of SQL Backup? There does not, as far as we can tell, appear be a SQL Backup parameter configuration that will support this “per location†retention period disparity…
This would be useful in other circumstances; such as where a very static DB is updated only once per month. On the LOCAL Backups partition; we currently keep three month’s of backups (three SQB files); but on the NAS device, we would keep 12 month’s of backup files (it is a small DB), if it were possible. The alternatives are managing the number of backups on the NAS device with another “clean-up†process (outside of SQL Backup) or living with the (as far as we know) limitations of the ERASE FILES/FILEOPTIONS parameters of SQL Backup…
Any suggestions? Has this “problem†ever come up before? Due to the amount of space dedicated on the NAS device for our SQL Server backups (and the Tape Backup rotation cycle); we have the “luxury†of keeping more backup files on the NAS that on the LOCAL partition on the SQL Server. The LOCAL set of backups is intended for minimum restore time; in case of a (relatively) “small†disaster. Restoring from the NAS (across several sub-nets of our network and from a different physical location) results in a MUCH longer restore time; i.e. the “last resortâ€, or fail-safe, especially in the event that the SQL Server itself were to completely fail and a restore to another platform became necessary. We would prefer to not have a separate “retention period†process for managing the number of SQB backup files on the NAS device, outside of SQL Backup’s built-in capabilities…
Sorry for the length of this post; I thought that the details of the “issue†were relevant to circumstances and any potential solution of the "problem"...
Thanks,
The backup files are stored on a LOCAL partition on the SQL Server (a RAID-10 array) with approx. 400 GB of total space; additionally they are also stored on a NAS device for 2nd level, “near-lineâ€, recovery. Also; the NAS device is attached to a tape backup system, for long-term archival. The NAS device has approx. 1 TB of total space that is dedicated to our SQL Server Group, for backups. Since splitting a backup into multiple SQB files and the MIRRORFILE option are mutually exclusive; we use the COPYTO option, to copy the four nightly SQB files to the NAS device.
The problem is that, due to the space limitations on the SQL Server’s LOCAL Backups partition; we can only keep (at most) three days of backups, FULL and LOGS (every 15 min.) on the Server. Whereas; on the NAS device, we would like to keep at least a full week of backup files. Is there any way to accomplish this goal of different retention periods for each location; using the ERASEFILES and FILEOPTIONS parameters of SQL Backup? There does not, as far as we can tell, appear be a SQL Backup parameter configuration that will support this “per location†retention period disparity…
This would be useful in other circumstances; such as where a very static DB is updated only once per month. On the LOCAL Backups partition; we currently keep three month’s of backups (three SQB files); but on the NAS device, we would keep 12 month’s of backup files (it is a small DB), if it were possible. The alternatives are managing the number of backups on the NAS device with another “clean-up†process (outside of SQL Backup) or living with the (as far as we know) limitations of the ERASE FILES/FILEOPTIONS parameters of SQL Backup…
Any suggestions? Has this “problem†ever come up before? Due to the amount of space dedicated on the NAS device for our SQL Server backups (and the Tape Backup rotation cycle); we have the “luxury†of keeping more backup files on the NAS that on the LOCAL partition on the SQL Server. The LOCAL set of backups is intended for minimum restore time; in case of a (relatively) “small†disaster. Restoring from the NAS (across several sub-nets of our network and from a different physical location) results in a MUCH longer restore time; i.e. the “last resortâ€, or fail-safe, especially in the event that the SQL Server itself were to completely fail and a restore to another platform became necessary. We would prefer to not have a separate “retention period†process for managing the number of SQB backup files on the NAS device, outside of SQL Backup’s built-in capabilities…
Sorry for the length of this post; I thought that the details of the “issue†were relevant to circumstances and any potential solution of the "problem"...
Thanks,
Peter J. LaValle Jr.
Comments
You should have the functionality that you are after in the next few weeks when we release SQL Backup 5.3. We have implemented an ERASEFILES_REMOTE option to allow you to specify a different purge duration for files in remote locations.
Regards,
Jonathan
-Project Manager
-Red Gate Software Ltd
Now, that's what I call GREAT Customer Service! A little spooky; in a synchronistic / effect-before-cause sort of way... But, still GREAT news! We will be upgrading to 5.3 as soon as it is released.
Thanks,
Pete
P.s. I was rereading The Hitchhiker's Guide to the Galaxy yesterday (for the ??? time). What's the probability that I am getting what I want; before I ask for it? Infinite improbability, or just lucky, like Zaphod Beeblebrox? HA! Back to the Heart of Gold; to create another feature...
We recently bought SQL Backup and have verison 5.3 installed. I do not see the ERASEFILES_REMOTE option in the GUI, but do see is as a parameter to the sp. Is this feature going to be integrated into the GUI as well?
Thanks!
Jeff
Yes, I imagine that we will look to integrate this in a future version of SQL Backup UI, it should be fairly easy to implement. However, at the moment we haven't an outlook of when this will be.
Regards,
Jonathan
-Project Manager
-Red Gate Software Ltd
I would hope it is soon as the GUI will not allow you to edit any jobfiles with this parameter. Sort of defeats the purpose of the central backup utility.