Backup plan for a SQL2005 mirrored database.

Heya all,

Sorry if this has been posted elsewhere, etc., please point me in the right direction if it has 'cos I couldn't find it!

Right, we have a mirrored database with full safety and a witness for automatic fail over, all works fine, very impressed with it. Now I need to backup the database involved and this is where I could do with some help and answers and/or tips.

As the mirror database is off-line/recovering it seems you can't back that one up, but I'd like to have something that tries to back it up for if/when it fails over and becomes the primary. The solution I've used for now is to write a small .Net application that uses the client side fail over connection string (Data Source=Server1;Failover Partner=Server2) so that it connects to whatever system is currently the primary, and then issues the relevant 'BACKUP xxx' statements to backup the database.

This applications is launched from a windows scheduled task job on the hour (or near to it), every hour. At 06:00 it does a full backup, at 12:00, 18:00 and 00:00 it does a differential backup, and all other times it does a transaction log backup.

This all seems to work fine so far, and generates all the relevant backup files to a share on another server.

So, my main question is; does this look like a good plan? Am I missing some really simple wizard or button that would backup the relevant database from whatever server is up?

Secondly, are the backups from each server interchangeable as they're in a mirrored configuration? That is, for example, the backup application (on the hour) connects to Server1 does a full back up, followed later by a log backup. Server1 then dies, so the next time the backup application runs it connects to Server2 and because of the current time does a log backup from Server2. If we had to restore from backups, could we use the full backup and log from Server1, followed by the log from Server2? Otherwise I'll guess I'll need to modify the logic for the backup application to detect it's failed over, and maybe do a full backup on Server2, or something like that.

Anyway, thanks for any help/advice/tips,

Gareth/OhGod
I feel that there is no need to backup your mirrored db while you have configured mirroring because mirroring acts as a high availability solution similar to log shipping........your principal and mirror server will be almost in sync as they have automatic failover (since witness is present)..........your mirror db is'nt critical...it is your principal db which is extremely critical for which you have configured mirroring..........so no need to backup your mirror db instead you can take a backup of your principal db @. regular intervals into a network drive or somewhere to keep it safe in the advent of a failure.........

Your mirrored db will be restored using the WITH NORECOVERY option so that you cannot even read the data.....so if needed you can take a database snapshot to read the data @. that time only...........

I guess you cannot restore the full backup + Tran log from Server1 and then Tran log from Server 2 because there will be mismatch in Log sequence number (LSN)

Pls correct me if am wrong feel free to revert if you need any help or assistance...........
|||

Deepak - you're incorrect here.

Yes, a full backup + logs from the principal, plus subsequent log backups from the new principal after a failover forms a complete and valid log backup chain. And yes, your logic around taking backups is sound as you may find that the mirroing partnership fails for an extended period of time, in which case you need to have backups available for recovery in case something happens to your one remaining server.

Hope this helps.

|||Sorry Deepak, I think I may have confused you. It's not so much that I want to backup the mirror server in/by itself, it's that ideally I'd like to backup both servers in case one fails, then I at least have the backups from the other server.

Setting up a SQL job, or similar, had problems because the mirror database is in recovery, and as you mentioned, unreadable. Hence the solution I came up with was the .Net application that connects to whatever server is currently the primary and issues the backup commands on that. This way if the initial primary fails - potentially for a long period of time - then I still get backups from the fail over mirror (now the primary).

Paul, thanks for that, that's excellent news. I think I'm now set.

Thanks all,

Gareth/OhGod.
|||Paul, Thanks for pointing my mistake i've learnt this today ...........Gareth thank you for asking this question else I would have a misconception about the backups in a mirrored database ........@. the end of the day i learnt new things
Thanks guys !