August 29, 2012 at 2:58 pm
So I just inherited a basic mirror set up with two SQL 2008 R2 servers.
They did a Full backup when they set up the mirror about 1.5 years ago, then they do TLog backups every hour -- but they delete them after two weeks, so they are useless, right? And you can't just keep keep the TLog backups forever or you'll fill you drive.
So with mirroring, can you just take regular Full backups of the Principal database, then regular hourly TLog backups? Or would you have to reestablish the mirror each time you did this or something?
August 29, 2012 at 3:12 pm
Yes, you should take regularly scheduled full backups along with t-log backups. If no full backups have been taken since the mirror was established and t-log backups have been deleted, then any t-log backups you have now are useless.
August 29, 2012 at 3:15 pm
You have to take full backups regularly. Without a full backup, the log backups are useless and that database can't be restored if necesary (and no, mirroring doesn't make backups unnecessary)
Even if they did retain all log backups, could you imagine restoring full backup and several thousand log backups (and heaven help you if one is damaged)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2012 at 3:17 pm
GilaMonster (8/29/2012)
You have to take full backups regularly. Without a full backup, the log backups are useless and that database can't be restored if necesary (and no, mirroring doesn't make backups unnecessary)Even if they did retain all log backups, could you imagine restoring full backup and several thousand log backups (and heaven help you if one is damaged)
What?? You mean mirroring and RAID disks aren't a replacement for backups??? :w00t::-P
August 29, 2012 at 3:51 pm
Thank you all, but I guess what was confusing is that each time you take a Full backup, the TLog backups that follow are based on that Full backup, so I thought this would therefore mess with Mirroring. And since you have to initialize mirroring with a Full backup, again it seemed taking another Full could mess things up. But from what you're all saying, nope!... just do regular old backups of your Primary database once Mirroring is established.
August 29, 2012 at 4:02 pm
No. Log backups are not based off a full backup unless that's the first ever full backup since the DB went into full recovery. Otherwise they're based solely on the previous log backup
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2012 at 4:13 pm
GilaMonster (8/29/2012)
No. Log backups are not based off a full backup unless that's the first ever full backup since the DB went into full recovery. Otherwise they're based solely on the previous log backup
Hmmm... maybe I didn't word that correctly. May I try this?
Say week 1, right at Friday at Midnight you create a new database and do a Full backup, and then Hourly TLog backups after that.
Then week 2 Friday at Midnight you do another Full backup, and continue the hourly TLog backups. So say Tuesday at 10am you find out you have a corrupt database, you would restore using Week 2 Full backup + the Week 2 TLog backups until 9am. Therefore those TLog backups you are using are "based on" the Week 2 Full backup. Is that not correct somehow?
Are you saying you could instead do the restore by using the Week 1 Full backup, and all TLog backups right until Tuesday on Week 2?
August 29, 2012 at 4:44 pm
jpSQLDude (8/29/2012)
Therefore those TLog backups you are using are "based on" the Week 2 Full backup. Is that not correct somehow?
Nope.
Are you saying you could instead do the restore by using the Week 1 Full backup, and all TLog backups right until Tuesday on Week 2?
Absolutely, yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2012 at 6:25 pm
I just wanted to clarify, too, that taking any kind of backup has no effect on database mirroring.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply