SQL Server Backup retention policy

Question

I need to backup database in full recovery mode with a condition as: 1. Backup database, only when space is available for backup operation and Backup retention policy must be like expect last two days backup folder, delete all other backup folder. any help will be highly appreciable.

Solution 1

You should backup your database via a maintenance plan "Back Up Database Task". Following that, you should add a "Maintenance Cleanup Task" to specify your retention policy and delete backups older than say, two days.

You can create your plan via the "Maintenance Plan Wizard".

Taking backups is your number one task as a DBA so you should really always make sure you have sufficient disk space. If disk space is an issue and you are using SQL Server 2008 Enterprise or SQL Server 2008 R2 Standard or higher, you can use backup compression to save space.

Solution 2

Solution

From your problem description, it sounds like you are running your backup device storage close to the wire. Correct this situation be allocating sufficient storage and you remove the need to check for sufficient space ahead of time.

Then simply follow the recommendations of others and create a Database Maintenance plan to manage your backup job.

Take a look at the following Blog post for an example implementation: Create database maintenance plans in SQL Server 2005 using SSIS

Background

So the issue here then is that SQL Server does not determine the amount of disk space required in order to store a database backup file prior to the actual execution of the backup process. My assumption is that this occurs because the size cannot be determined until the backup operation has completed, perhaps because of transactional activity that is occurring in tandem to the backup process for example.

However, it's considered good practice to have over and above what would be considered sufficient disk space, available at the location you store your database backups. This ensures that you always have sufficient space available to store your backups, even in the case of unexpected growth, perhaps as the result of increased transactional activity for example.

Further Reading

To read more on the basics of administration of SQL Server databases, in order to identify areas for further study, take a look at the excellent Technet Magazine Article: Top Tips for Effective Database Maintenance written by Paul Randal.

Comments

[John Sansom]

Just a minor highlight, your point about the backup process not running is inaccurate. The backup process would indeed run but would fail when disk space runs out. To me your explanation implies that the backup process will not even start if there is insufficient space, which is not the case.

[Bravax]

You're right, sorry I worded that badly. I assume (I've never actually seen this in practice), that the incomplete backup file would be deleted, and that the transaction log wouldn't be affected by this?