July 14, 2011 at 3:11 am
Hi,
I have hunted high & low for this one with no luck so i'm hoping someone can help here.
I'm pretty new to SQL admin & backups etc.
Basically I am trying to set up backups for a business critical application. It performs a bunch of SPs between 1am and 5am which i want to avoid interupting with trans log backups so ive set it up like this...
11pm - Full backup (appended to backup device A)
1am - 5am - various grooming jobs
5am - Full backup (appended to backup device A)
5:30am - 10.45pm - Trans backups every 15mins (Append to device B)
now, the failure happens on the first trans backup at 5:30am, stating that no full backup is available.
I can only assume that this is because the backup Device B has a gap in the trans log sequence.
So should i use a single device? This poses the problem that i end up with a 40 - 100gig file to shift about as the DB is 7-9gig in size with trans log being 8mb each time.
Thoughts? I'm probably missing something really simple here.
Ta
July 14, 2011 at 4:22 am
Devices don't matter. Is there something that your grooming jobs are doing? There's no reason for the backup to fail unless you are doing some switching of the recovery model.
Backups are non-blocking. You could run full or transaction log backups in the middle of the workday, and apart from load, you could continue to work. It also doesn't matter where you send the backups. SQL Server only cares that the backups occur. It does not track or manage backups being located together or in any specific location.
A couple of recommendations. First, don't append backups to each other. If you have media failure or any issue with that file, then you lose multiple backups. Make each backup to a separate file. It's a risk issue.
There are numerous scripts here that can help you run backups. You could use maintenance plans, but there are some great scripts, like this one, to help you with more flexible and reliable backups. http://qa.sqlservercentral.com/scripts/Backup+%2f+Restore/62380/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 14, 2011 at 4:29 am
Hi,
Thanks for the reply. I'll check out the scripts as I was also worried about the single file issue here.
Some of the grooming jobs do change the recovery model which is why i wanted to stop the transaction log backups during those hours. then a full backup after the jobs before starting the transaction log backups again.
I'll check again tonight as ive shifted times a bit further to avoid any overlap of jobs finishing after the full backup is taken in the morning.
Ta
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply