Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Aug 24 '10 at 8:36

This question came from our site for professional and enthusiast programmers.

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer

You should be able to set this up with a database maintenance plan.

If there is insufficient space, the backup won't run, so point 1 is satisfied.
(Although there should be some sort of alerting built in to your servers to notify you of this, as this is BAD.)

For point 2, get the maintenance plan to clean up old backups.

To get to the database maintenance plan go to: SQL Server Management Studio, connect to your SQL Server, expand the Management folder, then right click on Maintenance Plans and run through the wizard.

share|improve this answer
    
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. –  John Sansom Aug 24 '10 at 8:24
    
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? –  Bravax Aug 24 '10 at 8:43
    
No need to apologise my friend! Through collective contribution, together we produce a higher quality of content overall, for others to benefit from. –  John Sansom Aug 24 '10 at 8:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.