Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2009-01-19 : 08:46:12
|
| Hi,I'm currently setting up a mirroring environment for a database I'm responsible for and I'd just like to ask what kind of backup strategies you would recommend. Up until now I have full backups every day and translog backups every 15 minutes, and both go to disk first and then to tape (files are backed to tape right after db-backup is finished). I'm planning to keep this strategy.But should I also back up the mirrored database and not only the principal?? What happens when the principal fails for some reason...?- Lumbago |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-19 : 23:07:19
|
| You can't backup Mirror Database as it is not accessible. What type of DB Mirroring are you implementing? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-01-20 : 02:19:39
|
| I haven't actually started the mirroring yet (not even in test) but I like to plan ahead. sodeep: it's just regular mirroring, nothing funky about it.Tara: so what you're actually saying is that you have a backup script running on a regular schedule, but it doesn't actually do any backup of the mirrored databases until it becomes a principal? What happens when some sort of failure occurs and the mirror becomes the principal? Will the LSN correspond between the principal and mirror so that I in case of total disaster can use a full backup and say 10 hours of log backups from the former principal and 3 hours of log files from the new principal, to form a new and up-to-date database somewhere else?- Lumbago |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-20 : 08:35:36
|
| Both Backups and Database mirroring are Recovery solutions and difference is backup works as Cold(Disaster recovery) and other as High availability solution.unless Log chains are broken,you should be able to restore up-to date from backups. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-01-20 : 12:57:42
|
| We do not use automative failover in database mirroring for performance reasons. So we will never have the mirror become the principal unless someone ran the script to do it. I have the same exact jobs on both the principal and the mirror server. I run nightly full backups for the user databases, nightly full backups for the system databses, and tlog backups every 15 minutes. My backup script searches for the databases that need a transaction log backup and runs it on those only. So on my mirror server, it will ignore the mirrored database since the status is in recovery. The LSNs will definitely correspond between the principal and the mirror, that's how database mirroring works. I've never tested the log chain where part of them came from the old principal and the other part came from the new principal, so I don't know if that will work. On the case of a manual failover though in my environment, I would run a full backup to start the new chain.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Backup strategy for a mirrored db