Backing up databases in a mirror configuration

I have a user database maintance plan with subplans for full and
transaction log backups deployed to both the Principal and Mirror
servers. Each subplan has its own schedule.
Obviously, the principal server executes a successfully backup, while
the mirror copy fails. This generates errors that are picked up by
MOM and also from the jobs themselves.
In order to eliminate these errors, I emplyed a method that prevents
the jobs from running if the server is hosting the mirror copy (note
that all the databases on the server are mirrored):
1) Remove the schedule from the job that runs the SSIS package - job
name 'User Databases.Full Database Backup'
2) Create a new job called 'User Databases.Full Database Backup Status
Check' that checks for the read/write and online status of the
databases and executes the job in step one if they are online
3) Add a schedule to the job 'User Databases.Full Database Backup
Status Check' with the same details as that removed in step 1
I have a big problem with this approach: if any of the databases are
the mirror, then the entire backup subplan is not run - even if some
of the databases are the principal on the same server - it's an all or
nothing approach since the databases checked in step 2 may not
necessarily match the list of databases defined in the subplan.
Clearly, I'd like the checking step to only be affected by the
databases defined in the subplan, but I cannot think of a way of
interrogating the subplan details using SQL code to determine this.
Ideally, I would like to edit the maintenance plan's list of databases
to only include databases that are the principal on that server and to
do it each time the job is executed - maybe using the two job approach
as described above.
I would like some advice on a way to do this or whether I am barking
up the wrong tree and should try a different angle - perhaps defining
my own backup process (I really want to avoid doing this). Clearly,
if the maintenance plan could detect the fact that the database is a
mirror copy and just skip it, the whole use goes away, but it does
not.
Thanks> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this).
This is what you would have to do. Maint plans are for simpler installations, and conditionally
handling a mirrored database *inside* a backup task is not available. Consider wishing this at
connect.microsoft.com.
Another options is to have one backup task per database, and have a TSQL task before each which
check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
but should be doable.
Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
the file name and you will find examples...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viracochas" <warmtea@.hotmail.com> wrote in message
news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
>I have a user database maintance plan with subplans for full and
> transaction log backups deployed to both the Principal and Mirror
> servers. Each subplan has its own schedule.
> Obviously, the principal server executes a successfully backup, while
> the mirror copy fails. This generates errors that are picked up by
> MOM and also from the jobs themselves.
> In order to eliminate these errors, I emplyed a method that prevents
> the jobs from running if the server is hosting the mirror copy (note
> that all the databases on the server are mirrored):
> 1) Remove the schedule from the job that runs the SSIS package - job
> name 'User Databases.Full Database Backup'
> 2) Create a new job called 'User Databases.Full Database Backup Status
> Check' that checks for the read/write and online status of the
> databases and executes the job in step one if they are online
> 3) Add a schedule to the job 'User Databases.Full Database Backup
> Status Check' with the same details as that removed in step 1
> I have a big problem with this approach: if any of the databases are
> the mirror, then the entire backup subplan is not run - even if some
> of the databases are the principal on the same server - it's an all or
> nothing approach since the databases checked in step 2 may not
> necessarily match the list of databases defined in the subplan.
> Clearly, I'd like the checking step to only be affected by the
> databases defined in the subplan, but I cannot think of a way of
> interrogating the subplan details using SQL code to determine this.
> Ideally, I would like to edit the maintenance plan's list of databases
> to only include databases that are the principal on that server and to
> do it each time the job is executed - maybe using the two job approach
> as described above.
> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this). Clearly,
> if the maintenance plan could detect the fact that the database is a
> mirror copy and just skip it, the whole use goes away, but it does
> not.
> Thanks
>|||On 21 May, 13:43, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > I would like some advice on a way to do this or whether I am barking
> > up the wrong tree and should try a different angle - perhaps defining
> > my own backup process (I really want to avoid doing this).
> This is what you would have to do. Maint plans are for simpler installations, and conditionally
> handling a mirrored database *inside* a backup task is not available. Consider wishing this at
> connect.microsoft.com.
> Another options is to have one backup task per database, and have a TSQL task before each which
> check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
> but should be doable.
> Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
> plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
> the file name and you will find examples...)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Viracochas" <warm...@.hotmail.com> wrote in message
> news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
Thanks for the feedback. I feared there was no easy way, though it
won't be too difficult to set it up.