Log Shipping Failover

Is it possible to failover a single or a selected number of databases
onto a standby server? We're currently log shipping about 12 databases
to a standby and I was wondering if it was possible to randomly
failover onto selected dbs.
My understanding of log shipping is that when the primary server goes
down, the standby takes over. But, does that mean that all of the db's
on the standby server now become the primary? Or, can failover occur on
selected dbs?
The reason I ask is because if one db on the primary, for example, was
to go down, I'd like to be able to bring the particular db on the
standby server online while I fix or restore what's on the primary
server. Can it be done individually or is it an all or nothing
scenario?
Your personal advice or links is greatly appreciated.
Thanks,
PepsLog shipping pairs are at the individual database level. As such you
can change the log shipping roles of the primary & secondary for a
single DB if you like without affecting any other databases that happen
to be shipping their logs to or from the primary or secondary servers
(with sp_change_secondary_role, sp_change_monitor_role &
sp_change_primary_role or with the SQLEM GUI (ie. delete the logshipping
maint plan, RESTORE...WITH RECOVERY on the secondary and create a
logshipping maint plan (on the secondary) in the other direction)).
For more info see BOL:
How to set up and perform a log shipping role change
http://msdn.microsoft.com/library/d...r />
_9t0p.asp
*mike hodgson*
http://sqlnerd.blogspot.com
theSpinel wrote:

>Is it possible to failover a single or a selected number of databases
>onto a standby server? We're currently log shipping about 12 databases
>to a standby and I was wondering if it was possible to randomly
>failover onto selected dbs.
>My understanding of log shipping is that when the primary server goes
>down, the standby takes over. But, does that mean that all of the db's
>on the standby server now become the primary? Or, can failover occur on
>selected dbs?
>
>The reason I ask is because if one db on the primary, for example, was
>to go down, I'd like to be able to bring the particular db on the
>standby server online while I fix or restore what's on the primary
>server. Can it be done individually or is it an all or nothing
>scenario?
>
>Your personal advice or links is greatly appreciated.
>
>Thanks,
>Peps
>
>|||Thanks, Mike.
I tested this on one of the db's but I received an error about not
finding the store procedure 'sp_change_primary_role.'
Any ideas?|||Those sp_change... procs are in the msdb database. You will need to
either switch to that database before running those procs (with the USE
statement) or explicitly state the full object name when executing it,
for example
exec msdb.dbo.sp_change_primary_role @.db_name='MyDB', ....;
Personally I would do this all with T-SQL statements, however it may be
easier for you to do it with the GUI:
1) manually start the SQLAgent "backup" job on the primary,
2) manually start the SQLAgent "copy" job on the primary,
3) delete the maint plan on the primary,
4) restore that log backup on the secondary DB using the WITH RECOVERY
option,
5) delete the SQLAgent "restore" job on the secondary;
6) then, optionally, you can establish the log shipping in the other
direction
just by going through the maint plan wizard on the secondary and
specifying
the old primary as the new secondary.
And don't forget to make sure the logins work properly on the secondary
(I always forget that bit), ie. that they exist (with the same passwords
as on primary) and that they're not orphaned. Up to you.
This BOL article is pretty concise in what you need to do (in
Transact-SQL) if you want to go the T-SQL way (just remember all the
sp_change... procs are in msdb (sp_resolve_logins is in master)) but
you'll need to go through the maint wizard afterwards to establish the
log shipping back in the other direction again (ie. step 6 above):
How to set up and perform a log shipping role change (Transact-SQL)
http://msdn.microsoft.com/library/d...r />
_9t0p.asp
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
theSpinel wrote:

>Thanks, Mike.
>I tested this on one of the db's but I received an error about not
>finding the store procedure 'sp_change_primary_role.'
>Any ideas?
>
>