Log Shipping Role Reversal

I have log shipping configured on a pair of test SQL 2000 servers, both are
at SP3. The log shipping is working fine, but the problem occurs when I try
to reverse roles. I run this SQL statement on the primary server:
USE master
GO
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'ModelCopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 3,
@.access_level = 1
This places the primary database into read-only mode. I then run this SQL
statement on the secondary server.
USE master
GO
EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'ModelCopy',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null
At which time I get this error:
Server: Msg 927, Level 14, State 2, Line 1
Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
Line 49
Could not set single user mode.
In each case, I am runnig the statements in Query Analyzer with the
Enterprise Manager window closed. I understand that these statements cause
the primary to make a final copy and the secondary to do a final restore, but
the database I'm using is very small (a copy of the Model databae) and
shouldn't take but a few seconds, which is all the time it took to create and
initialize the database on the secondary to begin with. Is this process hung,
or am I just being impatient? It's been over 20 minutes.
TIA,
Ken
Ken,
See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
3101 if There Are Outstanding Transaction Log Backups
http://support.microsoft.com/default...&Product=sql2k
This is similar, though not identical, to the error you are getting. The
upshot is that you need to make sure that all the transaction logs have been
restored if you set the @.terminate parameter to 1.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> I have log shipping configured on a pair of test SQL 2000 servers, both
are
> at SP3. The log shipping is working fine, but the problem occurs when I
try
> to reverse roles. I run this SQL statement on the primary server:
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'ModelCopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 3,
> @.access_level = 1
> This places the primary database into read-only mode. I then run this SQL
> statement on the secondary server.
> USE master
> GO
> EXEC msdb.dbo.sp_change_secondary_role
> @.db_name = 'ModelCopy',
> @.do_load = 1,
> @.force_load = 1,
> @.final_state = 1,
> @.access_level = 1,
> @.terminate = 1,
> @.keep_replication = 0,
> @.stopat = null
> At which time I get this error:
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
> Line 49
> Could not set single user mode.
> In each case, I am runnig the statements in Query Analyzer with the
> Enterprise Manager window closed. I understand that these statements cause
> the primary to make a final copy and the secondary to do a final restore,
but
> the database I'm using is very small (a copy of the Model databae) and
> shouldn't take but a few seconds, which is all the time it took to create
and
> initialize the database on the secondary to begin with. Is this process
hung,
> or am I just being impatient? It's been over 20 minutes.
> TIA,
> Ken
|||Thanks for the info Ron. It pointed me in the right direction. I still needed
to to a detach/attach sequesnce on the database before running the secondary
stored procedure, but at least it is working in a predictable manner now.
"Ron Talmage" wrote:

> Ken,
> See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
> 3101 if There Are Outstanding Transaction Log Backups
> http://support.microsoft.com/default...&Product=sql2k
> This is similar, though not identical, to the error you are getting. The
> upshot is that you need to make sure that all the transaction logs have been
> restored if you set the @.terminate parameter to 1.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
> news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> are
> try
> but
> and
> hung,
>
>
|||OK, I spoke too soon. Occasionally, the sp_change_secondary_role procedure
will end with a "sqlmaint.exe failed. [SQLSTATE 42000][Error 22029]. The step
failed." message. I haven't been able to find any helpful information on this
message yet. Can you shed some more light on what is happening during this
procedure? Same scripts as posted previously in this thread, and include the
manual running of the restore job and a database detach/attach sequence in
Enterprise Manager immediately prior to running the secondary role change
stpred procedure.
Thanks Again,
Ken
"kmkrause2" wrote:
[vbcol=seagreen]
> Thanks for the info Ron. It pointed me in the right direction. I still needed
> to to a detach/attach sequesnce on the database before running the secondary
> stored procedure, but at least it is working in a predictable manner now.
> "Ron Talmage" wrote:

Log Shipping Role Reversal

We're implementing log shipping and have run into problems
when we attempt to reverse server roles.
The error occurs when we run the sp_change_secondary_role.
We receive a "sqlmaint.exe" error. If the stored procedure
is run 3 or 4 times it will occasionally succeed but the
role reversal is not successful even then.
Has anyone had any experience with this?
Thanks,
JohnKen,
See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
3101 if There Are Outstanding Transaction Log Backups
http://support.microsoft.com/defaul...7&Product=sql2k
This is similar, though not identical, to the error you are getting. The
upshot is that you need to make sure that all the transaction logs have been
restored if you set the @.terminate parameter to 1.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> I have log shipping configured on a pair of test SQL 2000 servers, both
are
> at SP3. The log shipping is working fine, but the problem occurs when I
try
> to reverse roles. I run this SQL statement on the primary server:
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'ModelCopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 3,
> @.access_level = 1
> This places the primary database into read-only mode. I then run this SQL
> statement on the secondary server.
> USE master
> GO
> EXEC msdb.dbo.sp_change_secondary_role
> @.db_name = 'ModelCopy',
> @.do_load = 1,
> @.force_load = 1,
> @.final_state = 1,
> @.access_level = 1,
> @.terminate = 1,
> @.keep_replication = 0,
> @.stopat = null
> At which time I get this error:
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
> Line 49
> Could not set single user mode.
> In each case, I am runnig the statements in Query Analyzer with the
> Enterprise Manager window closed. I understand that these statements cause
> the primary to make a final copy and the secondary to do a final restore,
but
> the database I'm using is very small (a copy of the Model databae) and
> shouldn't take but a few seconds, which is all the time it took to create
and
> initialize the database on the secondary to begin with. Is this process
hung,
> or am I just being impatient? It's been over 20 minutes.
> TIA,
> Ken|||Thanks for the info Ron. It pointed me in the right direction. I still neede
d
to to a detach/attach sequesnce on the database before running the secondary
stored procedure, but at least it is working in a predictable manner now.
"Ron Talmage" wrote:

> Ken,
> See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
> 3101 if There Are Outstanding Transaction Log Backups
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;294397&Product=sql2k[/ur
l]
> This is similar, though not identical, to the error you are getting. The
> upshot is that you need to make sure that all the transaction logs have be
en
> restored if you set the @.terminate parameter to 1.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
> news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> are
> try
> but
> and
> hung,
>
>|||OK, I spoke too soon. Occasionally, the sp_change_secondary_role procedure
will end with a "sqlmaint.exe failed. [SQLSTATE 42000][Error 22029].
The step
failed." message. I haven't been able to find any helpful information on thi
s
message yet. Can you shed some more light on what is happening during this
procedure? Same scripts as posted previously in this thread, and include the
manual running of the restore job and a database detach/attach sequence in
Enterprise Manager immediately prior to running the secondary role change
stpred procedure.
Thanks Again,
Ken
"kmkrause2" wrote:
[vbcol=seagreen]
> Thanks for the info Ron. It pointed me in the right direction. I still nee
ded
> to to a detach/attach sequesnce on the database before running the seconda
ry
> stored procedure, but at least it is working in a predictable manner now.
> "Ron Talmage" wrote:
>

Log shipping role change

The pimary server has crashed. I couldn't able to run
sp_change_primary_role procedure. I ran sp_change_secondary_role in
secondary server which is giving errors. sqlmain.exe failed. Any
ideas?barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408171352.65a1a20f@.posting.google.com>...
> The pimary server has crashed. I couldn't able to run
> sp_change_primary_role procedure. I ran sp_change_secondary_role in
> secondary server which is giving errors. sqlmain.exe failed. Any
> ideas?

I have no idea - what do "I couldn't run", "giving errors" and
"failed" mean? You will need to post more information for someone to
be able to make a suggestion - what version of MSSQL, how are you
running sqlmaint.exe, what exact commands did you execute and what are
the complete error messages?

Simon|||Becuase primary server is unavailable, I couldn't able to demote the
primary server. I ran this query to promote secondary server. I've
tried with different options @.terminate=0. I've ensured no user
connections exist. But still getting sqlmint.exe error. Any one
experienced this problem? I've checked KB article, but still not
working

EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'test',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0408180001.75139651@.posting.google.com>...
> barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408171352.65a1a20f@.posting.google.com>...
> > The pimary server has crashed. I couldn't able to run
> > sp_change_primary_role procedure. I ran sp_change_secondary_role in
> > secondary server which is giving errors. sqlmain.exe failed. Any
> > ideas?
> I have no idea - what do "I couldn't run", "giving errors" and
> "failed" mean? You will need to post more information for someone to
> be able to make a suggestion - what version of MSSQL, how are you
> running sqlmaint.exe, what exact commands did you execute and what are
> the complete error messages?
> Simon|||Becuase primary server is unavailable, I couldn't able to demote the
primary server. I ran this query to promote secondary server. I've
tried with different options @.terminate=0. I've ensured no user
connections exist. But still getting sqlmint.exe error. Any one
experienced this problem? I've checked KB article, but still not
working

EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'test',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0408180001.75139651@.posting.google.com>...
> barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408171352.65a1a20f@.posting.google.com>...
> > The pimary server has crashed. I couldn't able to run
> > sp_change_primary_role procedure. I ran sp_change_secondary_role in
> > secondary server which is giving errors. sqlmain.exe failed. Any
> > ideas?
> I have no idea - what do "I couldn't run", "giving errors" and
> "failed" mean? You will need to post more information for someone to
> be able to make a suggestion - what version of MSSQL, how are you
> running sqlmaint.exe, what exact commands did you execute and what are
> the complete error messages?
> Simon|||Because the primary server is unavailable, I couldn't able to demote
the primary server. I ran the following sql to promote secondary to
primary.
But it fails always. I've tried with @.terminate=0. No user connections
exist in database. I would apprecite if anyone got this kind of
problem. I've seen KB article, but still getting error, sqlmaint.exe
failed

EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'test',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0408180001.75139651@.posting.google.com>...
> barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408171352.65a1a20f@.posting.google.com>...
> > The pimary server has crashed. I couldn't able to run
> > sp_change_primary_role procedure. I ran sp_change_secondary_role in
> > secondary server which is giving errors. sqlmain.exe failed. Any
> > ideas?
> I have no idea - what do "I couldn't run", "giving errors" and
> "failed" mean? You will need to post more information for someone to
> be able to make a suggestion - what version of MSSQL, how are you
> running sqlmaint.exe, what exact commands did you execute and what are
> the complete error messages?
> Simon|||Becuase primary server is unavailable, I couldn't able to demote the
primary server. I ran this query to promote secondary server. I've
tried with different options @.terminate=0. I've ensured no user
connections exist. But still getting sqlmint.exe error. Any one
experienced this problem? I've checked KB article, but still not
working

EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'test',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0408180001.75139651@.posting.google.com>...
> barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408171352.65a1a20f@.posting.google.com>...
> > The pimary server has crashed. I couldn't able to run
> > sp_change_primary_role procedure. I ran sp_change_secondary_role in
> > secondary server which is giving errors. sqlmain.exe failed. Any
> > ideas?
> I have no idea - what do "I couldn't run", "giving errors" and
> "failed" mean? You will need to post more information for someone to
> be able to make a suggestion - what version of MSSQL, how are you
> running sqlmaint.exe, what exact commands did you execute and what are
> the complete error messages?
> Simon|||Becuase primary server is unavailable, I couldn't able to demote the
primary server. I ran this query to promote secondary server. I've
tried with different options @.terminate=0. I've ensured no user
connections exist. But still getting sqlmint.exe error. Any one
experienced this problem? I've checked KB article, but still not
working

EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'test',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0408180001.75139651@.posting.google.com>...
> barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408171352.65a1a20f@.posting.google.com>...
> > The pimary server has crashed. I couldn't able to run
> > sp_change_primary_role procedure. I ran sp_change_secondary_role in
> > secondary server which is giving errors. sqlmain.exe failed. Any
> > ideas?
> I have no idea - what do "I couldn't run", "giving errors" and
> "failed" mean? You will need to post more information for someone to
> be able to make a suggestion - what version of MSSQL, how are you
> running sqlmaint.exe, what exact commands did you execute and what are
> the complete error messages?
> Simon|||Becuase primary server is unavailable, I couldn't able to demote the
primary server. I ran this query to promote secondary server. I've
tried with different options @.terminate=0. I've ensured no user
connections exist. But still getting sqlmint.exe error. Any one
experienced this problem? I've checked KB article, but still not
working

EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'test',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0408180001.75139651@.posting.google.com>...
> barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408171352.65a1a20f@.posting.google.com>...
> > The pimary server has crashed. I couldn't able to run
> > sp_change_primary_role procedure. I ran sp_change_secondary_role in
> > secondary server which is giving errors. sqlmain.exe failed. Any
> > ideas?
> I have no idea - what do "I couldn't run", "giving errors" and
> "failed" mean? You will need to post more information for someone to
> be able to make a suggestion - what version of MSSQL, how are you
> running sqlmaint.exe, what exact commands did you execute and what are
> the complete error messages?
> Simon|||barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408181322.363cffdf@.posting.google.com>...
> Becuase primary server is unavailable, I couldn't able to demote the
> primary server. I ran this query to promote secondary server. I've
> tried with different options @.terminate=0. I've ensured no user
> connections exist. But still getting sqlmint.exe error. Any one
> experienced this problem? I've checked KB article, but still not
> working
> EXEC msdb.dbo.sp_change_secondary_role
> @.db_name = 'test',
> @.do_load = 1,
> @.force_load = 1,
> @.final_state = 1,
> @.access_level = 1,
> @.terminate = 1,
> @.keep_replication = 0,
> @.stopat = null
>
>
> sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0408180001.75139651@.posting.google.com>...
> > barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408171352.65a1a20f@.posting.google.com>...
> > > The pimary server has crashed. I couldn't able to run
> > > sp_change_primary_role procedure. I ran sp_change_secondary_role in
> > > secondary server which is giving errors. sqlmain.exe failed. Any
> > > ideas?
> > I have no idea - what do "I couldn't run", "giving errors" and
> > "failed" mean? You will need to post more information for someone to
> > be able to make a suggestion - what version of MSSQL, how are you
> > running sqlmaint.exe, what exact commands did you execute and what are
> > the complete error messages?
> > Simon

As I mentioned in my previous post, no one can help you if you don't
provide more information. Saying that you get an error is not very
helpful - you always need to say exactly what the full error message
is. You can simply copy and paste it from Query Analyzer.

And if you have searched the KB, it helps to say which articles you
have already checked. For example, is this one relevant?

http://support.microsoft.com/defaul...kb;en-us;294397

Simon|||Sorry for multilpe postings. I've opened another thread and closing this.

barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408181322.363cffdf@.posting.google.com>...
> Becuase primary server is unavailable, I couldn't able to demote the
> primary server. I ran this query to promote secondary server. I've
> tried with different options @.terminate=0. I've ensured no user
> connections exist. But still getting sqlmint.exe error. Any one
> experienced this problem? I've checked KB article, but still not
> working
> EXEC msdb.dbo.sp_change_secondary_role
> @.db_name = 'test',
> @.do_load = 1,
> @.force_load = 1,
> @.final_state = 1,
> @.access_level = 1,
> @.terminate = 1,
> @.keep_replication = 0,
> @.stopat = null
>
>
> sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0408180001.75139651@.posting.google.com>...
> > barbara_2003@.hotmail.com (barbara) wrote in message news:<5cd01256.0408171352.65a1a20f@.posting.google.com>...
> > > The pimary server has crashed. I couldn't able to run
> > > sp_change_primary_role procedure. I ran sp_change_secondary_role in
> > > secondary server which is giving errors. sqlmain.exe failed. Any
> > > ideas?
> > I have no idea - what do "I couldn't run", "giving errors" and
> > "failed" mean? You will need to post more information for someone to
> > be able to make a suggestion - what version of MSSQL, how are you
> > running sqlmaint.exe, what exact commands did you execute and what are
> > the complete error messages?
> > Simon

Log shipping reversal - Two legs in SQL 2000

Anyone do log shipping role reversal when two legs are being shipped?
--
jlCan you please better describe what you're looking for. One database being
shipped, two, etc.
Many thanks.
"John L" wrote:
> Anyone do log shipping role reversal when two legs are being shipped?
> --
> jl

Log shipping reversal - Two legs in SQL 2000

Anyone do log shipping role reversal when two legs are being shipped?
jl
Can you please better describe what you're looking for. One database being
shipped, two, etc.
Many thanks.
"John L" wrote:

> Anyone do log shipping role reversal when two legs are being shipped?
> --
> jl
sql

Log shipping reversal - Two legs in SQL 2000

Anyone do log shipping role reversal when two legs are being shipped?
--
jlCan you please better describe what you're looking for. One database being
shipped, two, etc.
Many thanks.
"John L" wrote:

> Anyone do log shipping role reversal when two legs are being shipped?
> --
> jl

log shipping question

What happens when you check the " Allow database to assume primary role"
option during log shipping setup. What happens if you dont check it ?
Also, does anyone have any links as far as recovery procedures for a log
shipping implementation used for disaster recovery..i.e. if Server A fails,
what are the exact steps to follow to recover the application onto Server B
which would also include changing the servername from Server B to Server A
...
Allow database to assume primary role - When this option is enabled, this secondary server becomes the primary server if there are any problems on the original primary server. If you select this option, you must provide a share where the transaction logs
are stored when the server assumes the role of a primary server (for example, (\\secondary_computername\sharename).
KBA http://support.microsoft.com/default...314515&sd=tech about complete FAQ sheet on Log shipping.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hassan" wrote:

> What happens when you check the " Allow database to assume primary role"
> option during log shipping setup. What happens if you dont check it ?
> Also, does anyone have any links as far as recovery procedures for a log
> shipping implementation used for disaster recovery..i.e. if Server A fails,
> what are the exact steps to follow to recover the application onto Server B
> which would also include changing the servername from Server B to Server A
> ...
>
>

log shipping question

What happens when you check the " Allow database to assume primary role"
option during log shipping setup. What happens if you dont check it ?
Also, does anyone have any links as far as recovery procedures for a log
shipping implementation used for disaster recovery..i.e. if Server A fails,
what are the exact steps to follow to recover the application onto Server B
which would also include changing the servername from Server B to Server A
...Allow database to assume primary role - When this option is enabled, this se
condary server becomes the primary server if there are any problems on the o
riginal primary server. If you select this option, you must provide a share
where the transaction logs
are stored when the server assumes the role of a primary server (for example
, (\\secondary_computername\sharename).
KBA http://support.microsoft.com/defaul...;314515&sd=tech a
bout complete FAQ sheet on Log shipping.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Perform
ance topic.
"Hassan" wrote:

> What happens when you check the " Allow database to assume primary role"
> option during log shipping setup. What happens if you dont check it ?
> Also, does anyone have any links as far as recovery procedures for a log
> shipping implementation used for disaster recovery..i.e. if Server A fails
,
> what are the exact steps to follow to recover the application onto Server
B
> which would also include changing the servername from Server B to Server A
> ...
>
>

log shipping question

What happens when you check the " Allow database to assume primary role"
option during log shipping setup. What happens if you dont check it ?
Also, does anyone have any links as far as recovery procedures for a log
shipping implementation used for disaster recovery..i.e. if Server A fails,
what are the exact steps to follow to recover the application onto Server B
which would also include changing the servername from Server B to Server A
...Allow database to assume primary role - When this option is enabled, this secondary server becomes the primary server if there are any problems on the original primary server. If you select this option, you must provide a share where the transaction logs are stored when the server assumes the role of a primary server (for example, (\\secondary_computername\sharename).
KBA http://support.microsoft.com/default.aspx?scid=kb;en-us;314515&sd=tech about complete FAQ sheet on Log shipping.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hassan" wrote:
> What happens when you check the " Allow database to assume primary role"
> option during log shipping setup. What happens if you dont check it ?
> Also, does anyone have any links as far as recovery procedures for a log
> shipping implementation used for disaster recovery..i.e. if Server A fails,
> what are the exact steps to follow to recover the application onto Server B
> which would also include changing the servername from Server B to Server A
> ...
>
>

log shipping failover to stand by server

Hi Guys
in logshipping to fail over to stand by server do i have
to do any role change
as far as i know i have to run following script at the
stand by server. am i right?
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read
only', 'false'
EXEC SP_DBOPTION 'database_name', 'dbo use
only', 'false'
pls advice me
Thanks
Biju
Biju,
have a look at 'log shipping, role changes' in BOL. You don't need to run
your script manually, but run
sp_change_primary_role
sp_change_secondary_role
sp_change_monitor_role
Usually the logins have already been transferred so there's nothing else to
do. These sprocs are useful because they'll try to get the last log (the
live one) if at all possible.
HTH,
Paul Ibison
|||BUT I DONT want to change roles.
>--Original Message--
>Biju,
>have a look at 'log shipping, role changes' in BOL. You
don't need to run
>your script manually, but run
>sp_change_primary_role
>sp_change_secondary_role
>sp_change_monitor_role
>Usually the logins have already been transferred so
there's nothing else to
>do. These sprocs are useful because they'll try to get
the last log (the
>live one) if at all possible.
>HTH,
>Paul Ibison
>
>.
>
|||Biju,
the name is perhaps slightly unintuitive. sp_change_secondary_role:
(a) Copies the final production database transaction log backup to the
standby server along with any additional transaction log backups that were
not previously copied.
(b) Disables the log-shipping transaction copy job on the standby server.
(c) Restores all transaction log backups not previously restored to the
standby database and initiates the recovery of the standby database.
(d) Terminates and rolls back all pending transactions.
(e) Sets the access level of the standby database after completion of the
system stored procedure to multiuser mode.
(f) Creates a log-shipping database maintenance plan on the new primary
server for the new production database. The SQL Server Agent job defined in
this plan backs up the transaction log of the new production database to the
folder location you specified when you set up log shipping.
HTH,
Paul Ibison