Setting up a retention policy for SQL backups
Hello,
This email is my first to the Experts Exchange so forgive me for any breach of protocol. I am trying to clean up a server's local drive since it is running out of disk space. The main culprit that is taking up most of the disk space are these nightly SQL backups. I have been able to move alot of these backups to another drive but the backups are still incrementing so I will eventually have to do it again in the future. How can I set up a retention policy to only keep backups that are current for say like a week or two? We are also backing up all local drives for this server with NetBackup each night so I am wondering if I even need to keep these backups on the local server. The server has SQL Server 2005. Thanks!
This email is my first to the Experts Exchange so forgive me for any breach of protocol. I am trying to clean up a server's local drive since it is running out of disk space. The main culprit that is taking up most of the disk space are these nightly SQL backups. I have been able to move alot of these backups to another drive but the backups are still incrementing so I will eventually have to do it again in the future. How can I set up a retention policy to only keep backups that are current for say like a week or two? We are also backing up all local drives for this server with NetBackup each night so I am wondering if I even need to keep these backups on the local server. The server has SQL Server 2005. Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
RiteshShah,
Thanks for the reply. I am not a programmer so is it possible to set up the retention policy through SQL Server Management Studio?
Thanks for the reply. I am not a programmer so is it possible to set up the retention policy through SQL Server Management Studio?
you don't need to programme anything, just set above script in your JOBS and you are done.
take transaction log backup every 5 min.
take differential backup at every 1 hour
take full backup at every 24 hours
and delete 24 hours backup.
create job for everything and sleep without tension.