Export (0) Print
Expand All
Expand Minimize

sp_change_secondary_role

SQL Server 2000

Converts the secondary database of a log shipping plan into a primary database.

Syntax

sp_change_secondary_role [ @db_name = ] 'db_name'
    , [ @do_load = ] do_load
    , [ @force_load = ] force_load
    , [ @final_state = ] final_state
    , [ @access_level = ] access_level
    , [ @terminate = ] terminate
    , [ @keep_replication = ] keep_replication
    , [ @stopat = ] stop_at_time

Arguments

[@db_name =] db_name

Specifies the name of the secondary database. db_name is sysname, with no default.

[@do_load =] do_load

Specifies that any pending transaction logs be copied and restored before converting db_name to a primary database. do_load is bit, with a default of 1.

[@force_load =] force_load

Specifies that the –ForceLoad option be used in restoring any pending transaction logs to the secondary database. This option is ignored unless do_load is set to 1. force_load is bit, with a default of 1.

[@final_state =] final_state

Specifies the recovery state of the database after completion of this stored procedure. final_state is smallint, with a default of 1, and can be any of these values.

Value Description
1 RECOVERY
2 NO RECOVERY
3 STANDBY

For more information about the meaning of these options, see RESTORE.

[@access_level =] access_level

Specifies the access level of the database after completion of this stored procedure. access_level is smallint, with a default of 1, and can be any of these values.

Value Description
1 MULTI_USER
2 RESTRICTED_USER
3 SINGLE_USER

For more information about the meaning of these options, see ALTER DATABASE.

[@terminate =] terminate

Specifies that all pending transactions be immediately rolled back, and the secondary database placed in single user mode for the duration of this stored procedure. terminate is bit, with a default of 1.

[@keep_replication =] keep_replication

Specifies that replication settings be preserved when restoring any pending transaction logs on the secondary database. This option is ignored unless do_load is set to 1. keep_replication is bit, with a default of 0.

[@stopat =] stop_at_time

Specifies that when applying any pending transaction logs, the secondary database be restored to the state it was in as of the specified date and time. This option is ignored unless do_load is set to 1. stop_at_time is datetime, with a default of NULL.

Return Code Values

0 (success) or –1 (failure)

Result Sets

None

Remarks

sp_change_secondary_role must be run on the instance of SQL Server marked as the current primary server.

In order to complete a log shipping role change, you must perform several steps in addition to running this procedure. For more information, see How to set up and perform a log shipping role change (Transact-SQL).

Permissions

Only members of the sysadmin fixed server role can execute sp_change_secondary_role.

Examples

This example shows how to convert the secondary database to a primary database. Previously shipped transaction logs are applied on the secondary database before it is converted.

EXEC sp_change_secondary_role @db_name = 'db2',
   @do_load = 1,
   @final_state = 1,
   @access_level = 3

See Also

sp_change_monitor_role

sp_change_primary_role

sp_resolve_logins

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft