troubleshooting Question

Sql Server Maintenance Plan - Backup

Avatar of Jack5Back
Jack5Back asked on
Microsoft SQL ServerMicrosoft Dynamics
3 Comments1 Solution696 ViewsLast Modified:
Hi Guys,
I am having something on my Sql server that I need some help with.

Enviornment:
Sql Server 2008 R2 Standard.
Windows Server 2008 R2

Situation:
I have a maintenance plan running and one of the tasks for the maintenance plan is to backup the database onto a locally attached disk. I would like to have the maintenance plan create a new file for each backup so I can delete them individually (or move them individually) as I like.

Currently, I have "create a backup file for every database" checked. Also, I have it selected that the backup set will expire after 14 days.

Problem:
The problem is that the disk is filling up. The disk fills up and then future backups fail until I go it and delete the unnecessary (oldes) backups.

I think I can fix this by deselecting "create a backup file for every database". But, I really like the idea of creating seperate files for each night's backup.

Questions:
Is there a way for Sql to automatically delete .bak files that are more than 14 days old? Do I have to run them all into the same file to get this functionality?

If I ran every night's backup into the same file, would it automatically overwrite data that is only 14 days old or would I still have the same problem with it filling the disk?

Any other suggestions on this?

Thanks,
Ben
ASKER CERTIFIED SOLUTION
Zaheer Iqbal
Technical Assurance & Implementation

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros