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 !

Backup on a remote server

I'm using SQL 7.0 in win2k. I'm using 2 harddisks partitioned into 2 and bo
th partitions are mirrored. One is for system and the other one I'm using f
or database. I'm doing a comlete daily backup of 2 critical databses which
eats up 500MB per database.
My question is, is it possible to use a remote server or storage and configu
re SQL to automatically backup those databases there? Because when I tried
to map a storage that I want to use forthe backups, I didn't see it in the "
choose backup destination
". What I'm basically doing now is to backup the databases in the system pa
rtition on the SQL server and move it to the remote storage manually from ti
me to time. Do you know of any simpler method?
Thank you.You can use a network share, it is just Enterprise Manager that is limited
to local disks, if I remember correctly. With Backup T-SQL statement you can
use remote shares as well. And you can use a scheduled to execute this
command regularly. Do please read about the Backup command and scheduled
jobs in Books OnLine if this is what you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Jun Soriano" <anonymous@.discussions.microsoft.com> wrote in message
news:4C041E6F-D81D-471A-A27F-C8E501674E41@.microsoft.com...
> I'm using SQL 7.0 in win2k. I'm using 2 harddisks partitioned into 2 and
both partitions are mirrored. One is for system and the other one I'm using
for database. I'm doing a comlete daily backup of 2 critical databses which
eats up 500MB per database. My question is, is it possible to use a remote
server or storage and configure SQL to automatically backup those databases
there? Because when I tried to map a storage that I want to use forthe
backups, I didn't see it in the "choose backup destination". What I'm
basically doing now is to backup the databases in the system partition on
the SQL server and move it to the remote storage manually from time to time.
Do you know of any simpler method?
> Thank you.
>|||Hi,
Enterprise manager will not show the network drives, Steps to perfom a
backup across network and schedule to run,
1. Create a backup device using sp_addumpdevice command (Sp_addumpdevice
'disk','dbname_bak','\\networkservername
\sharename\dbname.bak'
2. Create a batch file (backup.bat)
3. Inside the batch file
net use k: \\networkmachine\sharename /user:OS_username password
isql -Uuser -Ppassword -S server -Q'backup database dbname to
backupdevice with init" (use the device name create in step1)
net use k: /d
4. Save this contents in Backup.bat
5. Schedule this batch file using SQL Agent , In the Job step select the
type as "Operating system command"
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OMK5poS9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> You can use a network share, it is just Enterprise Manager that is limited
> to local disks, if I remember correctly. With Backup T-SQL statement you
can
> use remote shares as well. And you can use a scheduled to execute this
> command regularly. Do please read about the Backup command and scheduled
> jobs in Books OnLine if this is what you need.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Jun Soriano" <anonymous@.discussions.microsoft.com> wrote in message
> news:4C041E6F-D81D-471A-A27F-C8E501674E41@.microsoft.com...
and
> both partitions are mirrored. One is for system and the other one I'm
using
> for database. I'm doing a comlete daily backup of 2 critical databses
which
> eats up 500MB per database. My question is, is it possible to use a
remote
> server or storage and configure SQL to automatically backup those
databases
> there? Because when I tried to map a storage that I want to use forthe
> backups, I didn't see it in the "choose backup destination". What I'm
> basically doing now is to backup the databases in the system partition on
> the SQL server and move it to the remote storage manually from time to
time.
> Do you know of any simpler method?
>|||Hi,
It works fine to backup to a remote server with a maintenance plan! Just map
a network drive to the remote server share and manually type (browsing won't
work) this drive letter in the "Use this directory" field on the maintenance
plan property page. Don't forget to make the sql server service use an
account with sufficient permissions on the remote server though.
/Roger
"Jun Soriano" <anonymous@.discussions.microsoft.com> wrote in message
news:4C041E6F-D81D-471A-A27F-C8E501674E41@.microsoft.com...
> I'm using SQL 7.0 in win2k. I'm using 2 harddisks partitioned into 2 and
both partitions are mirrored. One is for system and the other one I'm using
for database. I'm doing a comlete daily backup of 2 critical databses which
eats up 500MB per database. My question is, is it possible to use a remote
server or storage and configure SQL to automatically backup those databases
there? Because when I tried to map a storage that I want to use forthe
backups, I didn't see it in the "choose backup destination". What I'm
basically doing now is to backup the databases in the system partition on
the SQL server and move it to the remote storage manually from time to time.
Do you know of any simpler method?
> Thank you.
>

Backup on a remote server

I'm using SQL 7.0 in win2k. I'm using 2 harddisks partitioned into 2 and both partitions are mirrored. One is for system and the other one I'm using for database. I'm doing a comlete daily backup of 2 critical databses which eats up 500MB per database. My question is, is it possible to use a remote server or storage and configure SQL to automatically backup those databases there? Because when I tried to map a storage that I want to use forthe backups, I didn't see it in the "choose backup destination". What I'm basically doing now is to backup the databases in the system partition on the SQL server and move it to the remote storage manually from time to time. Do you know of any simpler method
Thank youYou can use a network share, it is just Enterprise Manager that is limited
to local disks, if I remember correctly. With Backup T-SQL statement you can
use remote shares as well. And you can use a scheduled to execute this
command regularly. Do please read about the Backup command and scheduled
jobs in Books OnLine if this is what you need.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Jun Soriano" <anonymous@.discussions.microsoft.com> wrote in message
news:4C041E6F-D81D-471A-A27F-C8E501674E41@.microsoft.com...
> I'm using SQL 7.0 in win2k. I'm using 2 harddisks partitioned into 2 and
both partitions are mirrored. One is for system and the other one I'm using
for database. I'm doing a comlete daily backup of 2 critical databses which
eats up 500MB per database. My question is, is it possible to use a remote
server or storage and configure SQL to automatically backup those databases
there? Because when I tried to map a storage that I want to use forthe
backups, I didn't see it in the "choose backup destination". What I'm
basically doing now is to backup the databases in the system partition on
the SQL server and move it to the remote storage manually from time to time.
Do you know of any simpler method?
> Thank you.
>|||Hi,
Enterprise manager will not show the network drives, Steps to perfom a
backup across network and schedule to run,
1. Create a backup device using sp_addumpdevice command (Sp_addumpdevice
'disk','dbname_bak','\\networkservername\sharename\dbname.bak'
2. Create a batch file (backup.bat)
3. Inside the batch file
net use k: \\networkmachine\sharename /user:OS_username password
isql -Uuser -Ppassword -S server -Q'backup database dbname to
backupdevice with init" (use the device name create in step1)
net use k: /d
4. Save this contents in Backup.bat
5. Schedule this batch file using SQL Agent , In the Job step select the
type as "Operating system command"
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OMK5poS9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> You can use a network share, it is just Enterprise Manager that is limited
> to local disks, if I remember correctly. With Backup T-SQL statement you
can
> use remote shares as well. And you can use a scheduled to execute this
> command regularly. Do please read about the Backup command and scheduled
> jobs in Books OnLine if this is what you need.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Jun Soriano" <anonymous@.discussions.microsoft.com> wrote in message
> news:4C041E6F-D81D-471A-A27F-C8E501674E41@.microsoft.com...
> > I'm using SQL 7.0 in win2k. I'm using 2 harddisks partitioned into 2
and
> both partitions are mirrored. One is for system and the other one I'm
using
> for database. I'm doing a comlete daily backup of 2 critical databses
which
> eats up 500MB per database. My question is, is it possible to use a
remote
> server or storage and configure SQL to automatically backup those
databases
> there? Because when I tried to map a storage that I want to use forthe
> backups, I didn't see it in the "choose backup destination". What I'm
> basically doing now is to backup the databases in the system partition on
> the SQL server and move it to the remote storage manually from time to
time.
> Do you know of any simpler method?
> >
> > Thank you.
> >
>|||Hi,
It works fine to backup to a remote server with a maintenance plan! Just map
a network drive to the remote server share and manually type (browsing won't
work) this drive letter in the "Use this directory" field on the maintenance
plan property page. Don't forget to make the sql server service use an
account with sufficient permissions on the remote server though.
/Roger
"Jun Soriano" <anonymous@.discussions.microsoft.com> wrote in message
news:4C041E6F-D81D-471A-A27F-C8E501674E41@.microsoft.com...
> I'm using SQL 7.0 in win2k. I'm using 2 harddisks partitioned into 2 and
both partitions are mirrored. One is for system and the other one I'm using
for database. I'm doing a comlete daily backup of 2 critical databses which
eats up 500MB per database. My question is, is it possible to use a remote
server or storage and configure SQL to automatically backup those databases
there? Because when I tried to map a storage that I want to use forthe
backups, I didn't see it in the "choose backup destination". What I'm
basically doing now is to backup the databases in the system partition on
the SQL server and move it to the remote storage manually from time to time.
Do you know of any simpler method?
> Thank you.
>

Backup of mirrored databases using TSM

Hi,

I currently have 2 mirrored servers and would like to implement a backup solution using an existing TSM server. The first thing that comes to mind is using the TSM client or Litespeed by Quest, but I'd like to know the effects of performing backups on principal and mirrored servers first.

Will using one of these products cause errors or problems should the backup client try to backup a mirrored database? Can anyone make any recommendations on the effects of using TSM client or Litespeed for a mirrored environment?

Thanks.

That depends on how you would like to do the restore process. The restore process will dictate how you may want to do your backups and not the other way around. In our case we just use the native SQL Server agent to generate the backup files and have TSM to pick up the backup files. This is a disadvantage if your backing up terrabytes of data. In this case, LightSpeed will help decrease backup time.|||

Obviously, if you back up the principal while it is under heavy load, it will compete for resources. Backup tends to consume all available IO bandwidth, but not much CPU.

At the present time, you cannot back up a mirror database. Yes, I know, you want to, and we'll get to it, but not now.

Log backups will not interfere with mirroring as they would in a log-shipping environment, so that is not an issue.

Is there anything else you are concerned about?

|||

Sorry for the delay in my reply. Both of your suggestions were helpful and will consider all my options.
Thanks.

Backup of mirrored databases using TSM

Hi,

I currently have 2 mirrored servers and would like to implement a backup solution using an existing TSM server. The first thing that comes to mind is using the TSM client or Litespeed by Quest, but I'd like to know the effects of performing backups on principal and mirrored servers first.

Will using one of these products cause errors or problems should the backup client try to backup a mirrored database? Can anyone make any recommendations on the effects of using TSM client or Litespeed for a mirrored environment?

Thanks.

That depends on how you would like to do the restore process. The restore process will dictate how you may want to do your backups and not the other way around. In our case we just use the native SQL Server agent to generate the backup files and have TSM to pick up the backup files. This is a disadvantage if your backing up terrabytes of data. In this case, LightSpeed will help decrease backup time.|||

Obviously, if you back up the principal while it is under heavy load, it will compete for resources. Backup tends to consume all available IO bandwidth, but not much CPU.

At the present time, you cannot back up a mirror database. Yes, I know, you want to, and we'll get to it, but not now.

Log backups will not interfere with mirroring as they would in a log-shipping environment, so that is not an issue.

Is there anything else you are concerned about?

|||

Sorry for the delay in my reply. Both of your suggestions were helpful and will consider all my options.
Thanks.