October 20, 2015 at 2:04 am
I've been looking through a lot of google searches, but haven't been able to find an answer, so I'm hoping you guys can help me 🙂
Once in a while, our database backup files get "corrupted" and the server can't complete its backup. If I delete the file, the backup works fine.
I have yet to find out why it gets corrupted, but in the meantime, I'd like to make sure the backups don't break.
I'm using backup devices and running one full backup each day.
What I'd like to do is run the backup WITH FORMAT. From my google searches, that should fix the file, but I see no way of doing that through the maintenance plan editor, how would I do that?
New databases gets created on the servers now and then, so I need to use the "all user databases" option of the maintenance plan instead of a static T-SQL script.
October 20, 2015 at 2:20 am
Are you backing up each day to the same file?
If so do you never need to go back more than the current backup?
Format will write new header information so any other "backups" in the file are rendered useless.
I would strongly recommend backing up to a new file every time.
October 20, 2015 at 2:26 am
Hi!
We're doing filebackups of the SQL backups to tape and cloud every night, so we only need one day in the files.
October 20, 2015 at 2:33 am
The closest your going to get with a maintenance plan is to set the expiry to 1 day, to expire the media set.
The alternative would be to backup to a new file each day and clean up the previous days file.
October 20, 2015 at 2:37 am
Ok, so set it to expire after one day and run a maintenance cleanup task to delete the file?
October 20, 2015 at 2:45 am
Either or.
Personally, I would never append a backup to an existing file. I would always create a new file for each backup, that way if corruption happens on day 10, I don't render all 10 days worth of backups useless. I then setup cleanup jobs to remove files older than what I need on disk as the backup server has copies for the 30 days, month ends going back a year, quarter ends going back 5 years and year ends going back 10 years, just in case we get audited.
Ola Hallengren has a great script to do backups to individual files and clean them up in one go, its widely used by many companies and people here on SSC and comes highly recommended.
October 20, 2015 at 2:53 am
That's basically what we're doing too. The backup device gets overwritten, not appended, and then backed up by TSM and DPM to cloud and tape respectively.
I'll take a look at the script though, thanks! 🙂
October 20, 2015 at 3:23 am
OK, your getting corruption in the media header then hence why you want to use FORMAT to recreate the media header.
I would defiantly look at creating a new file each time and then cleaning up what you don't need afterwards as theres no way to issue FORMAT in the maint plan.
You can do this in the maint plan if you want, just change the backup option to "Create a backup for every database" instead of "Backup databases across one or more files" then add in a maintenance cleanup task to loop through the directories and remove anything older than say 23 hours.
Ola's scripts are great though as they are quite flexible, where as maint plans can be fairly rigid in what they can do.
October 20, 2015 at 5:19 am
I think Anthony hit the nail on the head here - create a new backup file and clean up the ones older than N days.
Personally, I don't like maintenance plans. They're a brute-force approach, do more work than necessary, don't handle errors and aren't very flexible. They're better than nothing, but that's about it. Like Anthony pointed out, Ola's scripts are much better and flexible. Or, if you're ready, you can write your own. It's a great way to learn about some of the system tables if you're comfortable with them. Just make sure to test, test, test.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply