home *** CD-ROM | disk | FTP | other *** search
/ 95.86.62.111 / 95.86.62.111.tar / 95.86.62.111 / sql2000 / INSTALL / instls.sql < prev    next >
Text File  |  2000-07-08  |  53KB  |  1,460 lines
  1. /**********************************************************************/
  2. /* instls.sql                                                         */
  3. /*                                                                    */
  4. /* Installs the tables and stored procedures used to support log      */
  5. /* shipping on the primary, secondary, and monotor servers.           */
  6. /*
  7. ** Copyright Microsoft, Inc. 1994 - 2000
  8. ** All Rights Reserved.
  9. */
  10. /*                                                                    */
  11. /**********************************************************************/
  12.  
  13. PRINT N'--------------------------------'
  14. PRINT N'Starting execution of INSTLS.SQL'
  15. PRINT N'--------------------------------'
  16. go
  17.  
  18. USE msdb
  19. go
  20.  
  21. -- Explicitly set the options that the server stores with the object in sysobjects.status
  22. -- so that it doesn't matter if the script is run using a DBLib or ODBC based client.
  23. SET QUOTED_IDENTIFIER OFF -- We don't use quoted identifiers
  24. SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
  25. go
  26.  
  27. -- @drop_tbl, set to one to drop and then recreate the tables
  28. DECLARE @drop_tbl BIT
  29. SELECT  @drop_tbl = 0
  30.  
  31. /* drop stored procedures (silently)*/
  32. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_update_log_shipping_plan' AND type = N'P'))
  33.     DROP PROCEDURE sp_update_log_shipping_plan
  34. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_plan' AND type = N'P'))
  35.     DROP PROCEDURE sp_add_log_shipping_plan
  36. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_plan' AND type = N'P'))
  37.     DROP PROCEDURE sp_delete_log_shipping_plan
  38. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_plan_database' AND type = N'P'))
  39.     DROP PROCEDURE sp_add_log_shipping_plan_database
  40. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_plan_database' AND type = N'P'))
  41.     DROP PROCEDURE sp_delete_log_shipping_plan_database
  42. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_database' AND type = N'P'))
  43.     DROP PROCEDURE sp_add_log_shipping_database
  44. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_database' AND type = N'P'))
  45.     DROP PROCEDURE sp_delete_log_shipping_database
  46. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_verify_lsp_identifiers' AND type = N'P'))
  47.     DROP PROCEDURE sp_verify_lsp_identifiers
  48. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_update_log_shipping_plan_database' AND type = N'P'))
  49.     DROP PROCEDURE sp_update_log_shipping_plan_database
  50. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_remove_log_shipping_monitor' AND type = N'P')  )
  51.     DROP PROCEDURE sp_remove_log_shipping_monitor
  52. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_can_tlog_be_applied' AND type = N'P')  )
  53.     DROP PROCEDURE sp_can_tlog_be_applied
  54. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_change_primary_role' AND type = N'P')  )
  55.     DROP PROCEDURE sp_change_primary_role
  56. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_change_secondary_role' AND type = N'P')  )
  57.     DROP PROCEDURE sp_change_secondary_role
  58. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_define_log_shipping_monitor' AND type = N'P'))
  59.     DROP PROCEDURE sp_define_log_shipping_monitor
  60. IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_resolve_logins' AND type = N'P')  )
  61.     DROP PROCEDURE sp_resolve_logins
  62.  
  63. /* drop tables */
  64. IF (EXISTS (SELECT *
  65.             FROM INFORMATION_SCHEMA.TABLES
  66.             WHERE (TABLE_NAME = N'log_shipping_monitor') AND @drop_tbl = 1))
  67. BEGIN
  68.     PRINT 'Dropping Table log_shipping_monitor'
  69.     DROP TABLE msdb.dbo.log_shipping_monitor
  70. END
  71.  
  72. IF (EXISTS (SELECT *
  73.             FROM INFORMATION_SCHEMA.TABLES
  74.             WHERE (TABLE_NAME = N'log_shipping_databases') AND @drop_tbl = 1))
  75. BEGIN
  76.     PRINT 'Dropping Table log_shipping_databases'
  77.     DROP TABLE msdb.dbo.log_shipping_databases
  78. END
  79.  
  80. IF (EXISTS (SELECT *
  81.             FROM INFORMATION_SCHEMA.TABLES
  82.             WHERE (TABLE_NAME = N'log_shipping_plan_databases') AND @drop_tbl = 1))
  83. BEGIN
  84.     PRINT 'Dropping Table log_shipping_plan_databases'
  85.     DROP TABLE msdb.dbo.log_shipping_plan_databases
  86. END
  87.  
  88. IF (EXISTS (SELECT *
  89.             FROM INFORMATION_SCHEMA.TABLES
  90.             WHERE (TABLE_NAME = N'log_shipping_plans') AND @drop_tbl = 1))
  91. BEGIN
  92.     PRINT 'Dropping Table log_shipping_plans'
  93.     DROP TABLE msdb.dbo.log_shipping_plans
  94. END
  95.  
  96. IF (EXISTS (SELECT *
  97.             FROM INFORMATION_SCHEMA.TABLES
  98.             WHERE (TABLE_NAME = N'log_shipping_plan_history') AND @drop_tbl = 1))
  99. BEGIN
  100.     PRINT 'Dropping Table log_shipping_plan_history'
  101.     DROP TABLE msdb.dbo.log_shipping_plan_history
  102. END
  103.  
  104. /* alter existing tables */
  105. IF (EXISTS (SELECT *
  106.             FROM INFORMATION_SCHEMA.TABLES
  107.             WHERE (TABLE_NAME = N'sysdbmaintplans')))
  108. BEGIN
  109.   IF (NOT EXISTS (SELECT *
  110.                   FROM msdb.dbo.syscolumns
  111.                   WHERE name = N'log_shipping' AND id = OBJECT_ID(N'sysdbmaintplans')))
  112.   BEGIN
  113.     PRINT ''
  114.     PRINT 'Altering table sysdbmaintplans.'
  115.     ALTER TABLE sysdbmaintplans ADD log_shipping BIT NULL DEFAULT (0)
  116.   END
  117. END
  118.  
  119. /* create tables */
  120.  
  121. /**********************************************************************/
  122. /* TABLE : log_shipping_monitor                                       */
  123. /* Populated on primary and secondary servers. Contains information   */
  124. /* need to contact the monitor server                                 */
  125. /**********************************************************************/
  126.  
  127. IF (EXISTS (SELECT *
  128.             FROM INFORMATION_SCHEMA.TABLES
  129.             WHERE (TABLE_NAME = N'log_shipping_monitor')))
  130. BEGIN
  131.   PRINT 'Table log_shipping_monitor already exists.'
  132. END
  133.  
  134. ELSE BEGIN
  135.  PRINT 'Creating table log_shipping monitor.'  
  136.  CREATE TABLE log_shipping_monitor
  137.  (
  138.   monitor_server_name    sysname              NOT NULL,
  139.   logon_type              INT                  NOT NULL,
  140.   logon_data              VARBINARY(256)    NULL
  141.  )
  142. END
  143. go
  144.  
  145. /**********************************************************************/
  146. /* TABLE : log_shipping_databases                                      */
  147. /* Populated on the primary server. Used by the GUI to display which  */
  148. /* databases are participating in log shipping.                       */
  149. /**********************************************************************/
  150.  
  151. IF (EXISTS (SELECT *
  152.             FROM INFORMATION_SCHEMA.TABLES
  153.             WHERE (TABLE_NAME = N'log_shipping_databases')))
  154.  
  155. BEGIN
  156.  PRINT 'Table log_shipping_databases already exists.'
  157. END
  158.  
  159. ELSE BEGIN
  160.  PRINT 'Creating table log_shipping_databases.'
  161.  CREATE TABLE log_shipping_databases
  162.  (
  163.   database_name               sysname               NOT NULL,
  164.   maintenance_plan_id   UNIQUEIDENTIFIER NULL
  165.  )
  166. END
  167. go
  168.  
  169. /**********************************************************************/
  170. /* TABLE : log_shipping_plans                                         */
  171. /* Populated on the secondary server.                                 */
  172. /*                                                                    */
  173. /**********************************************************************/
  174.  
  175. IF (EXISTS (SELECT *
  176.             FROM INFORMATION_SCHEMA.TABLES
  177.             WHERE (TABLE_NAME = N'log_shipping_plans')))
  178.  
  179. BEGIN
  180.  PRINT 'Table log_shipping_plans already exists.'
  181.   -- check for old table structure
  182.  IF (NOT EXISTS (SELECT * 
  183.                  FROM INFORMATION_SCHEMA.COLUMNS
  184.                  WHERE (TABLE_NAME = N'log_shipping_plans')
  185.                  AND (COLUMN_NAME = N'maintenance_plan_id')))
  186.  BEGIN
  187.   PRINT 'Adding column maintenance_plan_id to table log_shipping_plans...'
  188.   ALTER TABLE log_shipping_plans ADD maintenance_plan_id UNIQUEIDENTIFIER NULL
  189.  END
  190.  IF (NOT EXISTS (SELECT * 
  191.                  FROM INFORMATION_SCHEMA.COLUMNS
  192.                  WHERE (TABLE_NAME = N'log_shipping_plans')
  193.                  AND (COLUMN_NAME = N'backup_job_id')))
  194.  BEGIN
  195.   PRINT 'Adding column backup_job_id to table log_shipping_plans...'
  196.   ALTER TABLE log_shipping_plans ADD backup_job_id UNIQUEIDENTIFIER NULL
  197.  END 
  198.  IF (NOT EXISTS (SELECT * 
  199.                  FROM INFORMATION_SCHEMA.COLUMNS
  200.                  WHERE (TABLE_NAME = N'log_shipping_plans')
  201.                  AND (COLUMN_NAME = N'share_name')))
  202.  BEGIN
  203.   PRINT 'Adding column share_name to table log_shipping_plans...'
  204.   ALTER TABLE log_shipping_plans ADD share_name NVARCHAR(500) NULL
  205.  END
  206. END
  207.  
  208. ELSE BEGIN
  209.  PRINT 'Creating table log_shipping_plans.'
  210.  CREATE TABLE log_shipping_plans
  211.  (
  212.   plan_id                  UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  213.   plan_name                sysname          NOT NULL,
  214.   description              NVARCHAR(500)    NULL,
  215.   source_server            sysname          NOT NULL,
  216.   source_dir               NVARCHAR(500)    NOT NULL,
  217.   destination_dir          NVARCHAR(500)    NOT NULL,
  218.   copy_job_id              UNIQUEIDENTIFIER NOT NULL,
  219.   load_job_id              UNIQUEIDENTIFIER NOT NULL,
  220.   history_retention_period INT              NOT NULL, /* minutes */
  221.   file_retention_period    INT              NOT NULL, /* minutes */
  222.   maintenance_plan_id      UNIQUEIDENTIFIER NULL,     /* for role changes */
  223.   backup_job_id            UNIQUEIDENTIFIER NULL,
  224.   share_name               NVARCHAR(500)    NULL
  225.  )
  226. END
  227. go
  228.  
  229. /**********************************************************************/
  230. /* TABLE : log_shipping_plan_databases                                */
  231. /* Populated on secondary server.                                     */
  232. /*                                                                    */
  233. /**********************************************************************/
  234.  
  235. IF (EXISTS (SELECT *
  236.             FROM INFORMATION_SCHEMA.TABLES
  237.             WHERE (TABLE_NAME = N'log_shipping_plan_databases')))
  238. BEGIN
  239.   PRINT 'Table log_shipping_plan_databases already exists.'
  240.  
  241.  -- check for old table structure
  242.   IF (NOT EXISTS (SELECT * 
  243.                  FROM INFORMATION_SCHEMA.COLUMNS
  244.                  WHERE (TABLE_NAME = N'log_shipping_plan_databases')
  245.                  AND (COLUMN_NAME = N'recover_db')))
  246.   BEGIN
  247.     PRINT 'Adding column recover_db to table log_shipping_plan_databases...'
  248.     ALTER TABLE log_shipping_plan_databases ADD recover_db BIT NOT NULL DEFAULT 0
  249.   END
  250.   IF (NOT EXISTS (SELECT * 
  251.                  FROM INFORMATION_SCHEMA.COLUMNS
  252.                  WHERE (TABLE_NAME = N'log_shipping_plan_databases')
  253.                  AND (COLUMN_NAME = N'terminate_users')))
  254.   BEGIN
  255.     PRINT 'Adding column terminate_users to table log_shipping_plan_databases...'
  256.     ALTER TABLE log_shipping_plan_databases ADD terminate_users BIT NOT NULL DEFAULT 0    
  257.   END
  258. END
  259.  
  260. ELSE BEGIN
  261.  PRINT 'Creating table log_shipping_plan_databases.'
  262.  CREATE TABLE log_shipping_plan_databases
  263.  (
  264.   plan_id               UNIQUEIDENTIFIER NOT NULL,
  265.   source_database       sysname          NOT NULL,
  266.   destination_database  sysname          NOT NULL,
  267.   load_delay            INT              NOT NULL,  /* minutes */
  268.   load_all              BIT              NOT NULL,  /* 1 if all copied T-Logs should be loaded */
  269.   last_file_copied      NVARCHAR(500)    NULL,
  270.   date_last_copied      DATETIME         NULL,
  271.   last_file_loaded      NVARCHAR(500)    NULL,
  272.   date_last_loaded      DATETIME         NULL,
  273.   copy_enabled          BIT              NOT NULL,
  274.   load_enabled          BIT              NOT NULL,  /*1 = load enabled, 0 = load disabled */
  275.   recover_db            BIT              NOT NULL,
  276.   terminate_users       BIT              NOT NULL
  277.  )
  278. END
  279. go
  280.  
  281. IF (EXISTS (SELECT *
  282.             FROM INFORMATION_SCHEMA.TABLES
  283.             WHERE (TABLE_NAME = N'log_shipping_plan_history')))
  284. BEGIN
  285.  PRINT 'Table log_shipping_plan_history already exists.'
  286. END
  287.  
  288. ELSE BEGIN
  289.  PRINT 'Creating table log_shipping_plan_history'
  290.   CREATE TABLE msdb.dbo.log_shipping_plan_history
  291.   (
  292.     sequence_id          INT              NOT NULL IDENTITY UNIQUE,
  293.     plan_id              UNIQUEIDENTIFIER NOT NULL,
  294.     source_database      sysname          NOT NULL,
  295.     destination_database sysname          NOT NULL,
  296.     activity             BIT              NOT NULL DEFAULT (0), -- 0 = copy, 1=load
  297.     succeeded            BIT              NOT NULL,
  298.     num_files            INT              NOT NULL DEFAULT (0),
  299.     last_file            NVARCHAR(256)    NULL,
  300.     end_time             DATETIME         NOT NULL DEFAULT (GETDATE()),
  301.     duration             INT              NULL     DEFAULT (0),
  302.     error_number         INT              NOT NULL DEFAULT (0),
  303.     message              NVARCHAR(500)    NULL
  304.  )   
  305. END
  306. go
  307.  
  308. /**************************************************************/
  309. /* Create stored procedures                                   */
  310. /**************************************************************/
  311.  
  312. /**************************************************************/
  313. /* sp_verify_lsp_identifiers                                  */
  314. /**************************************************************/
  315. PRINT 'Creating procedure sp_verify_lsp_identifiers.'
  316. go
  317. CREATE PROCEDURE sp_verify_lsp_identifiers
  318.   @plan_id   UNIQUEIDENTIFIER = NULL OUTPUT,
  319.   @plan_name sysname          = NULL
  320. AS BEGIN
  321.   DECLARE @_plan_id UNIQUEIDENTIFIER
  322.  
  323.   SET NOCOUNT ON
  324.  
  325.   IF (@plan_id IS NULL) AND (@plan_name IS NULL)
  326.   BEGIN
  327.     RAISERROR (14410, -1, -1)
  328.     RETURN(1)
  329.   END
  330.  
  331.   IF (@plan_name IS NOT NULL)
  332.   BEGIN
  333. -- load plan_id for supplied plan name
  334.     SELECT @_plan_id = plan_id from msdb.dbo.log_shipping_plans WHERE plan_name = @plan_name
  335. -- IF it doesn't exist
  336.     IF (@_plan_id IS NULL)
  337.     BEGIN
  338.       RAISERROR (14262,16,1, N'plan_name', @plan_name)
  339.       RETURN(1)
  340.     END
  341. -- IF a plan_id was supplied as well, check it matches with the plan_name
  342.     IF @_plan_id <> ISNULL (@plan_id, @_plan_id)
  343.     BEGIN
  344.       RAISERROR (14410, -1, -1)
  345.       RETURN(1)
  346.     END
  347.   END
  348.  
  349.   SELECT @plan_id = ISNULL (@plan_id, @_plan_id)
  350.   RETURN(0)
  351. END
  352. go
  353.  
  354. /**************************************************************/
  355. /* sp_add_log_shipping_plan                                   */
  356. /**************************************************************/
  357. PRINT 'Creating procedure sp_add_log_shipping_plan.'
  358. go
  359. CREATE PROCEDURE sp_add_log_shipping_plan
  360.   @plan_name                sysname,
  361.   @description              NVARCHAR(500)    = NULL,
  362.   @source_server            sysname,
  363.   @source_dir               NVARCHAR(500),
  364.   @destination_dir          NVARCHAR(500),
  365.   @history_retention_period INT             = 2880, -- 2 days
  366.   @file_retention_period    INT             = 2880, -- 2 days
  367.   @copy_frequency           INT             = 5,
  368.   @restore_frequency        INT             = 5,
  369.   @plan_id                  UNIQUEIDENTIFIER = NULL OUTPUT
  370. AS
  371. BEGIN
  372.   DECLARE @copy_job_id      UNIQUEIDENTIFIER
  373.   DECLARE @restore_job_id   UNIQUEIDENTIFIER
  374.   DECLARE @friendly_name    NVARCHAR(500)
  375.   DECLARE @copy_job_name    NVARCHAR(500)
  376.   DECLARE @copy_sql         NVARCHAR(500)
  377.   DECLARE @restore_job_name NVARCHAR(500)
  378.   DECLARE @restore_sql      NVARCHAR(500)
  379.   DECLARE @rv               INT
  380.  
  381.   SET NOCOUNT ON
  382. -- check plan_name is unique
  383.   IF EXISTS (select * from msdb.dbo.log_shipping_plans where plan_name = @plan_name)
  384.   BEGIN
  385.     RAISERROR (14261,16,1, N'plan_name', @plan_name)
  386.     RETURN (1) -- Failure
  387.   END
  388.  
  389.   SELECT @plan_id = NEWID ()
  390.   SELECT @copy_sql =    N'EXECUTE master.dbo.xp_sqlmaint ''-LSCopyPlanID "' + CONVERT (NVARCHAR(36), @plan_id) + '"'''
  391.   SELECT @copy_job_name = N'Log Shipping copy for ' + @plan_name
  392.   SELECT @restore_job_name = N'Log Shipping Restore for ' + @plan_name
  393.   SELECT @restore_sql = N'EXECUTE master.dbo.xp_sqlmaint ''-LSRestorePlanID "' + CONVERT (NVARCHAR(36), @plan_id) + '"'''
  394.  
  395.   BEGIN TRANSACTION
  396.  
  397. -- create copy job
  398.  
  399.     EXECUTE @rv = msdb.dbo.sp_add_job @job_name = @copy_job_name , @job_id= @copy_job_id OUTPUT
  400.  
  401.     IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 
  402.  
  403.     EXECUTE @rv = msdb.dbo.sp_add_jobstep @job_id = @copy_job_id, @step_id = 1, @step_name = N'Log Shipping Copy', 
  404.       @command = @copy_sql,
  405.       @on_fail_action = 2, @flags = 4, @subsystem = N'TSQL', @on_success_step_id = 0, @on_success_action = 1, 
  406.       @on_fail_step_id = 0
  407.     IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 
  408.  
  409.     EXECUTE @rv = msdb.dbo.sp_add_jobschedule @job_id = @copy_job_id,
  410.       @freq_type = 4, @freq_interval = 1, @freq_subday_type = 0x4, @freq_subday_interval = @copy_frequency,
  411.       @freq_relative_interval = 0, @name = N'Schedule 1'
  412.     IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 
  413.  
  414.     EXECUTE msdb.dbo.sp_add_jobserver @job_id = @copy_job_id, @server_name = N'(local)' 
  415.  
  416. -- create restore job
  417.  
  418.     EXECUTE @rv = msdb.dbo.sp_add_job @job_name = @restore_job_name, @job_id= @restore_job_id OUTPUT
  419.  
  420.     IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 
  421.  
  422.     EXECUTE @rv = msdb.dbo.sp_add_jobstep @job_id = @restore_job_id, @step_id = 1, @step_name = N'Log Shipping Restore', 
  423.       @command = @restore_sql,
  424.       @on_fail_action = 2, @flags = 4, @subsystem = N'TSQL', @on_success_step_id = 0, @on_success_action = 1, 
  425.       @on_fail_step_id = 0
  426.     IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 
  427.  
  428.     EXECUTE @rv = msdb.dbo.sp_add_jobschedule @job_id = @restore_job_id,
  429.       @freq_type = 4, @freq_interval = 1, @freq_subday_type = 0x4, @freq_subday_interval = @copy_frequency,
  430.       @freq_relative_interval = 0, @name = N'Schedule 1'
  431.     IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 
  432.  
  433.     EXECUTE @rv = msdb.dbo.sp_add_jobserver @job_id = @restore_job_id, @server_name = N'(local)'
  434.     IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 
  435.  
  436. -- populate log_shipping_plans
  437.  
  438.     INSERT INTO msdb.dbo.log_shipping_plans (
  439.       plan_id,
  440.       plan_name,
  441.       description,
  442.       source_server,
  443.       source_dir,
  444.       destination_dir,
  445.       copy_job_id,
  446.       load_job_id,
  447.       history_retention_period,
  448.       file_retention_period )
  449.     VALUES (
  450.       @plan_id, 
  451.       @plan_name, 
  452.       @description, 
  453.       @source_server, 
  454.       @source_dir, 
  455.       @destination_dir, 
  456.       @copy_job_id, 
  457.       @restore_job_id, 
  458.       @history_retention_period,
  459.       @file_retention_period)
  460.  
  461.     IF (@@error <> 0) GOTO rollback_quit -- error
  462.  
  463.   COMMIT TRANSACTION
  464.   RETURN(0)  -- success
  465. rollback_quit:
  466.   ROLLBACK TRANSACTION
  467.   RETURN (1) -- Failure
  468. END
  469. go
  470. /**************************************************************/
  471. /* sp_delete_log_shipping_plan                                */
  472. /**************************************************************/
  473. PRINT 'Creating procedure sp_delete_log_shipping_plan.'
  474. go
  475. CREATE PROCEDURE sp_delete_log_shipping_plan
  476.   @plan_id     UNIQUEIDENTIFIER = NULL,
  477.   @plan_name   sysname          = NULL,
  478.   @del_plan_db BIT              = 0
  479. AS BEGIN
  480.   DECLARE @rv          INT
  481.   DECLARE @copy_job UNIQUEIDENTIFIER
  482.   DECLARE @load_job UNIQUEIDENTIFIER
  483.  
  484.   SET NOCOUNT ON
  485.  
  486.   EXECUTE @rv = msdb.dbo.sp_verify_lsp_identifiers @plan_id OUTPUT, @plan_name
  487.   IF (@rv<>0)
  488.     RETURN(1) -- error 
  489.  
  490.   BEGIN TRANSACTION
  491. -- dri check
  492.   IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_plan_databases WHERE plan_id = @plan_id)
  493.   BEGIN
  494.     IF (@del_plan_db = 0)
  495.     BEGIN
  496.       RAISERROR (14423,-1,-1)
  497.       goto rollback_quit
  498.     END
  499. -- cascade delete
  500.     DELETE FROM msdb.dbo.log_shipping_plan_databases WHERE plan_id = @plan_id
  501.   END
  502.  
  503. -- delete the copy and load jobs
  504.   SELECT @copy_job = copy_job_id, @load_job = load_job_id FROM msdb.dbo.log_shipping_plans WHERE plan_id = @plan_id
  505.   IF (@copy_job IS NOT NULL)
  506.   BEGIN
  507.     EXECUTE @rv =msdb.dbo.sp_delete_job @copy_job
  508.     IF (@@error <> 0 OR @rv <> 0)
  509.       goto rollback_quit
  510.   END
  511.  
  512.   IF (@load_job IS NOT NULL)
  513.   BEGIN
  514.     EXECUTE @rv =msdb.dbo.sp_delete_job @load_job
  515.     IF (@@error <> 0 OR @rv <> 0)
  516.       goto rollback_quit
  517.   END
  518.     
  519.   DELETE FROM msdb.dbo.log_shipping_plans WHERE plan_id = @plan_id
  520.   COMMIT TRANSACTION
  521.   RETURN(0)
  522. rollback_quit:
  523.   ROLLBACK TRANSACTION
  524.   RETURN(1) -- error
  525. END
  526. go
  527. /**************************************************************/
  528. /* sp_update_log_shipping_plan                                */
  529. /**************************************************************/
  530. PRINT 'Creating procedure sp_update_log_shipping_plan.'
  531. go
  532. CREATE PROCEDURE sp_update_log_shipping_plan
  533.   @plan_id                  UNIQUEIDENTIFIER = NULL,
  534.   @plan_name                sysname          = NULL,
  535.   @description              NVARCHAR(500)    = NULL,
  536.   @source_server            sysname          = NULL,
  537.   @source_dir               NVARCHAR(500)    = NULL,
  538.   @destination_dir          NVARCHAR(500)    = NULL,
  539.   @copy_job_id              UNIQUEIDENTIFIER = NULL,
  540.   @load_job_id              UNIQUEIDENTIFIER = NULL,
  541.   @history_retention_period INT              = NULL,
  542.   @file_retention_period    INT              = NULL
  543. AS
  544. BEGIN
  545.   DECLARE @_plan_name                sysname
  546.   DECLARE @_description              NVARCHAR(500)
  547.   DECLARE @_source_server            sysname
  548.   DECLARE @_source_dir               NVARCHAR(500)
  549.   DECLARE @_destination_dir          NVARCHAR(500)
  550.   DECLARE @_copy_job_id              UNIQUEIDENTIFIER
  551.   DECLARE @_load_job_id              UNIQUEIDENTIFIER
  552.   DECLARE @_history_retention_period INT
  553.   DECLARE @_file_retention_period    INT
  554.   DECLARE @rv                        INT
  555.  
  556.   SET NOCOUNT ON
  557.  
  558. -- check plan name and name
  559.   IF (@plan_id IS NULL)
  560.   BEGIN
  561.     EXECUTE @rv = msdb.dbo.sp_verify_lsp_identifiers @plan_id OUTPUT, @plan_name
  562.     IF (@rv <> 0)
  563.       RETURN(@rv)
  564.   END
  565.   ELSE
  566.   BEGIN
  567.     EXECUTE @rv = msdb.dbo.sp_verify_lsp_identifiers @plan_id OUTPUT
  568.     IF (@rv <> 0)
  569.       RETURN(@rv)
  570.   END
  571.   
  572. -- get existing values
  573.   SELECT
  574.     @_plan_name                = plan_name,
  575.     @_description              = description,
  576.     @_source_server            = source_server,
  577.     @_source_dir               = source_dir,
  578.     @_destination_dir          = destination_dir,
  579.     @_copy_job_id              = copy_job_id,
  580.     @_load_job_id              = load_job_id,
  581.     @_history_retention_period = history_retention_period,
  582.     @_file_retention_period    = file_retention_period
  583.   FROM msdb.dbo.log_shipping_plans
  584.   WHERE plan_id = @plan_id
  585.  
  586. -- If the plan name is changing, check it doesn't already exist
  587.   IF (@plan_name <> @_plan_name)
  588.   BEGIN
  589.     IF (EXISTS (SELECT plan_id FROM msdb.dbo.log_shipping_plans where plan_name = @plan_name))
  590.     BEGIN
  591.       RAISERROR (14262, 16,1, 'plan_name', @plan_name)
  592.       RETURN(1) -- error
  593.     END
  594.   END
  595.  
  596. -- enter new values for all changing parameters
  597.   SELECT @_plan_name                = ISNULL (@plan_name,                @_plan_name)
  598.   SELECT @_description              = ISNULL (@description,              @_description)
  599.   SELECT @_source_server            = ISNULL (@source_server,            @_source_server)
  600.   SELECT @_source_dir               = ISNULL (@source_dir,               @_source_dir)
  601.   SELECT @_destination_dir          = ISNULL (@destination_dir,          @_destination_dir)
  602.   SELECT @_copy_job_id              = ISNULL (@copy_job_id,              @_copy_job_id)
  603.   SELECT @_load_job_id              = ISNULL (@load_job_id,              @_load_job_id)
  604.   SELECT @_history_retention_period = ISNULL (@history_retention_period, @_history_retention_period)
  605.   SELECT @_file_retention_period    = ISNULL (@file_retention_period,    @_file_retention_period)
  606.  
  607. -- update plan
  608.   UPDATE msdb.dbo.log_shipping_plans SET
  609.     plan_name                = @_plan_name,
  610.     description              = @_description,
  611.     source_server            = @_source_server,
  612.     source_dir               = @_source_dir,
  613.     destination_dir          = @_destination_dir,
  614.     copy_job_id              = @_copy_job_id,
  615.     load_job_id              = @_load_job_id,
  616.     history_retention_period = @_history_retention_period,
  617.     file_retention_period    = @_file_retention_period
  618.   WHERE plan_id = @plan_id
  619. END
  620. go
  621. /**************************************************************/
  622. /* sp_add_log_shipping_plan_database                          */
  623. /**************************************************************/
  624. PRINT 'Creating procedure sp_add_log_shipping_plan_database.'
  625. go
  626. CREATE PROCEDURE sp_add_log_shipping_plan_database
  627.   @plan_id               UNIQUEIDENTIFIER  = NULL,
  628.   @plan_name             sysname           = NULL,
  629.   @source_database       sysname,
  630.   @destination_database  sysname,
  631.   @load_delay            INT               = 0,    -- load delay in minutes
  632.   @load_all              BIT               = 1,    -- set to 0 to load 1 db at a time
  633.   @copy_enabled          BIT               = 1,    -- perform copy
  634.   @load_enabled          BIT               = 1,    -- perform load
  635.   @recover_db            BIT               = 1,
  636.   @terminate_users       BIT               = 1
  637. AS BEGIN
  638.   DECLARE @rv INT
  639.  
  640.   SET NOCOUNT ON
  641. -- check that the log shipping plan exists
  642.   EXECUTE @rv = msdb.dbo.sp_verify_lsp_identifiers  @plan_id OUTPUT, @plan_name
  643.   IF (@rv <> 0) RETURN (-11) -- error
  644.  
  645. -- check that the destination database exists
  646.   IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = @destination_database)
  647.   BEGIN
  648.     RAISERROR (14262, 16, 1, N'destination_database', @destination_database)
  649.     RETURN(1) -- error
  650.   END
  651.  
  652. -- check that the destination database isn't already part of a log shipping pair
  653.   IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_plan_databases where destination_database = @destination_database)
  654.   BEGIN
  655.     RAISERROR (14424,-1,-1, @destination_database)
  656.     RETURN(1) -- error
  657.   END
  658.  
  659.   INSERT INTO log_shipping_plan_databases (
  660.     plan_id,
  661.     source_database,
  662.     destination_database,
  663.     load_delay,
  664.     load_all,
  665.     last_file_copied,
  666.     date_last_copied,
  667.     last_file_loaded,
  668.     date_last_loaded,
  669.     copy_enabled,
  670.     load_enabled,
  671.     recover_db,
  672.     terminate_users)
  673.   VALUES (
  674.     @plan_id, 
  675.     @source_database, 
  676.     @destination_database, 
  677.     @load_delay, 
  678.     @load_all,
  679.     N'first_file_000000000000.trn', 
  680.     GETDATE (), 
  681.     N'first_file_000000000000.trn', 
  682.     GETDATE (), 
  683.     @copy_enabled, 
  684.     @load_enabled,
  685.     @recover_db,
  686.     @terminate_users)
  687.  
  688.   RETURN(0)
  689. END
  690. go
  691. /**************************************************************/
  692. /* sp_update_log_shipping_plan_database                       */
  693. /**************************************************************/
  694. PRINT 'Creating procedure sp_update_log_shipping_plan_database.'
  695. go
  696. CREATE PROCEDURE sp_update_log_shipping_plan_database
  697.   @destination_database  sysname,
  698.   @load_delay            INT              = NULL,
  699.   @load_all              BIT              = NULL,
  700.   @file_retention_period INT              = NULL,
  701.   @copy_enabled          BIT              = NULL,
  702.   @load_enabled          BIT              = NULL,
  703.   @recover_db            BIT              = NULL,
  704.   @terminate_users       BIT              = NULL 
  705. AS BEGIN
  706.   DECLARE @_load_delay            INT
  707.   DECLARE @_load_all              BIT
  708.   DECLARE @_copy_enabled          BIT
  709.   DECLARE @_load_enabled          BIT
  710.   DECLARE @_recover_db            BIT
  711.   DECLARE @_terminate_users       BIT
  712.   DECLARE @rv                     INT
  713.  
  714.   SET NOCOUNT ON
  715.  
  716. -- get existing values
  717.   SELECT
  718.     @_load_delay            = load_delay,
  719.     @_load_all              = load_all,
  720.     @_copy_enabled          = copy_enabled,
  721.     @_load_enabled          = load_enabled,
  722.     @_recover_db            = recover_db,
  723.     @_terminate_users       = terminate_users
  724.   FROM msdb.dbo.log_shipping_plan_databases
  725.   WHERE destination_database = @destination_database
  726.  
  727. -- update existing values with usr supplied values
  728.   SELECT @_load_delay            = ISNULL (@load_delay,            @_load_delay)
  729.   SELECT @_load_all              = ISNULL (@load_all,              @_load_all)
  730.   SELECT @_copy_enabled          = ISNULL (@copy_enabled,          @_copy_enabled)
  731.   SELECT @_load_enabled          = ISNULL (@load_enabled,          @_load_enabled)
  732.   SELECT @_recover_db            = ISNULL (@recover_db,            @_recover_db)
  733.   SELECT @_terminate_users       = ISNULL (@terminate_users,       @_terminate_users)
  734.  
  735. -- update plan_databases
  736.   UPDATE msdb.dbo.log_shipping_plan_databases SET
  737.     load_delay            = @_load_delay,
  738.     load_all              = @_load_all,
  739.     copy_enabled          = @_copy_enabled,
  740.     load_enabled          = @_load_enabled,
  741.     recover_db            = @_recover_db,
  742.     terminate_users       = @_terminate_users   
  743.   WHERE destination_database = @destination_database
  744.   RETURN(0)
  745. END
  746. go
  747. /**************************************************************/
  748. /* sp_delete_log_shipping_plan_database                       */
  749. /**************************************************************/
  750. PRINT 'Creating procedure sp_delete_log_shipping_plan_database.'
  751. go
  752. CREATE PROCEDURE sp_delete_log_shipping_plan_database
  753.   @plan_id              UNIQUEIDENTIFIER = NULL,
  754.   @plan_name            sysname          = NULL,
  755.   @destination_database sysname          = NULL
  756. AS BEGIN
  757. DECLARE @_plan_id UNIQUEIDENTIFIER
  758. DECLARE @_dest_db sysname
  759.  
  760.   IF (@plan_id IS NULL) AND (@plan_name IS NULL)
  761.   BEGIN
  762.     -- must supply at least one parameter
  763.     IF (@destination_database IS NULL)
  764.     BEGIN
  765.       RAISERROR (14410,-1,-1)
  766.       RETURN(1)
  767.     END
  768.     SELECT @plan_id = plan_id FROM msdb.dbo.log_shipping_plan_databases WHERE destination_database = @destination_database
  769.   END
  770.  
  771.   IF (@plan_name IS NOT NULL)
  772.   BEGIN
  773.     SELECT @_plan_id = plan_id from msdb.dbo.log_shipping_plans WHERE plan_name = @plan_name
  774.     IF (@_plan_id IS NOT NULL)
  775.     BEGIN
  776.       IF @_plan_id <> ISNULL (@plan_id, @_plan_id)
  777.       BEGIN
  778.         RAISERROR (14410,-1,-1)
  779.         RETURN(1)
  780.       END
  781.     END
  782.     ELSE
  783.     BEGIN
  784.       RAISERROR (14262, 16, 1, 'plan_name', @plan_name)
  785.       RETURN(1)
  786.     END
  787.   END
  788.   SELECT @_plan_id = ISNULL (@plan_id, @_plan_id)
  789.   SELECT @_dest_db = ISNULL (@destination_database, N'%')
  790.  
  791.   DELETE FROM log_shipping_plan_databases WHERE plan_id = @_plan_id AND destination_database LIKE @_dest_db
  792. END
  793. go
  794. /**************************************************************/
  795. /* sp_add_log_shipping_database                               */
  796. /**************************************************************/
  797. PRINT 'Creating procedure sp_add_log_shipping_database.'
  798. go
  799. CREATE PROCEDURE sp_add_log_shipping_database
  800.   @db_name                 sysname,
  801.   @maintenance_plan_id   UNIQUEIDENTIFIER  = NULL
  802. AS BEGIN
  803.   SET NOCOUNT ON
  804.   IF NOT EXISTS (select dbid from master.dbo.sysdatabases where name = @db_name)
  805.   BEGIN
  806.     RAISERROR (14261, 16, 1, N'Database', @db_name)
  807.     RETURN (1)
  808.   END
  809.   IF EXISTS (select database_name from msdb.dbo.log_shipping_databases where database_name = @db_name)
  810.   BEGIN
  811.     RAISERROR (14424,-1,-1, @db_name)
  812.     RETURN (1)
  813.   END
  814.   INSERT INTO msdb.dbo.log_shipping_databases (database_name, maintenance_plan_id) VALUES (@db_name, @maintenance_plan_id)
  815. END
  816. go
  817.  
  818. /**************************************************************/
  819. /* sp_delete_log_shipping_database                            */
  820. /**************************************************************/
  821. PRINT 'Creating procedure sp_delete_log_shipping_database.'
  822. go
  823. CREATE PROCEDURE sp_delete_log_shipping_database
  824.   @db_name    sysname
  825. AS BEGIN
  826.   DELETE FROM msdb.dbo.log_shipping_databases WHERE database_name = @db_name
  827. END
  828. go
  829.  
  830. /**************************************************************/
  831. /* sp_can_tlog_be_applied                                     */
  832. /**************************************************************/
  833. PRINT 'Creating procedure sp_can_tlog_be_applied.'
  834. go
  835. CREATE PROCEDURE sp_can_tlog_be_applied
  836.   @backup_file_name NVARCHAR(128),
  837.   @database_name    sysname,
  838.   @result           BIT = 0 OUTPUT
  839. AS
  840. BEGIN
  841.   DECLARE @command                 NVARCHAR(256)
  842.   DECLARE @db_recovery_fork_id0    UNIQUEIDENTIFIER
  843.   DECLARE @db_recovery_fork_id1    UNIQUEIDENTIFIER
  844.   DECLARE @db_backup_lsn           NUMERIC(25,0)
  845.   DECLARE @backup_type             INT
  846.   DECLARE @backup_recovery_fork_id UNIQUEIDENTIFIER
  847.   DECLARE @backup_last_lsn         NUMERIC(25,0)
  848.   DECLARE @lsn_slot                INT
  849.   DECLARE @lsn_block               INT
  850.   DECLARE @lsn_file                INT
  851.  
  852.   SET NOCOUNT ON
  853.  
  854.   SELECT @result = 0
  855.  
  856.   CREATE TABLE #db_info
  857.   (
  858.   ParentObject NVARCHAR(128) COLLATE database_default ,
  859.   Object       NVARCHAR(128) COLLATE database_default,
  860.   Field        NVARCHAR(128) COLLATE database_default,
  861.   Value        SQL_VARIANT
  862.   )
  863.  
  864.   CREATE TABLE #backup_header
  865.   (
  866.   BackupName             NVARCHAR(128) COLLATE database_default NULL,
  867.   BackupDescription      NVARCHAR(256) COLLATE database_default NULL,
  868.   BackupType             INT, 
  869.   ExpirationDate         DATETIME NULL,
  870.   Compressed             INT,
  871.   Position               INT,
  872.   DeviceType             INT,
  873.   UserName               NVARCHAR(128) COLLATE database_default NULL,
  874.   ServerName             NVARCHAR(128) COLLATE database_default,
  875.   DatabaseName           NVARCHAR(128) COLLATE database_default,
  876.   DatabaseVersion        INT,
  877.   DatabaseCreationDate   DATETIME,
  878.   BackupSize             NUMERIC(20,0) NULL,
  879.   FirstLsn               NUMERIC(25,0) NULL,
  880.   LastLsn                NUMERIC(25,0) NULL,
  881.   CheckpointLsn          NUMERIC(25,0) NULL,
  882.   DatabaseBackupLsn      NUMERIC(25,0) NULL,
  883.   BackupStartDate        DATETIME,
  884.   BackupFinishDate       DATETIME,
  885.   SortOrder              INT,
  886.   CodePage               INT,
  887.   UnicodeLocaleId        INT,
  888.   UnicodeComparisonStyle INT,
  889.   CompatibilityLevel     INT,
  890.   SoftwareVendorId       INT,
  891.   SoftwareVersionMajor   INT,
  892.   SoftwareVersionMinor   INT,
  893.   SoftwareVersionBuild   INT,
  894.   MachineName            NVARCHAR(128) COLLATE database_default,
  895.   Flags                  INT NULL,
  896.   BindingId              UNIQUEIDENTIFIER NULL,
  897.   RecoveryForkId         UNIQUEIDENTIFIER NULL,
  898.   Collation              nvarchar(128)    null
  899.   )
  900.  
  901.   -- Populate temp tables
  902.  
  903.   SELECT @command = N'RESTORE HEADERONLY FROM DISK = N''' + @backup_file_name + N'''' 
  904.   INSERT INTO #backup_header
  905.   EXECUTE (@command)
  906.  
  907.   SELECT @command = N'DBCC DBINFO(N''' + @database_name + N''') WITH TABLERESULTS'
  908.   INSERT INTO #db_info
  909.   EXECUTE (@command)
  910.  
  911.   -- Get the values we need into variables for easy access
  912.  
  913.   SELECT top 1 @db_recovery_fork_id0 = ISNULL(CONVERT(UNIQUEIDENTIFIER, Value), 0x00)
  914.   FROM #db_info
  915.   WHERE (Object = N'dbi_recoveryForkNameStack')
  916.     AND (Field = N'm_guid')
  917.  
  918.   SELECT @db_recovery_fork_id1 = ISNULL(CONVERT(UNIQUEIDENTIFIER, Value), 0x00)
  919.   FROM #db_info
  920.   WHERE (Object = N'dbi_recoveryForkNameStack')
  921.     AND (Field = N'm_guid')
  922.   
  923.   SELECT @lsn_slot = CONVERT(INT, Value)
  924.   FROM #db_info
  925.   WHERE (Object = N'dbi_dbbackupLSN')
  926.     AND (Field = N'm_slotId')
  927.  
  928.   SELECT @lsn_block = CONVERT(INT, Value)
  929.   FROM #db_info
  930.   WHERE (Object = N'dbi_dbbackupLSN')
  931.     AND (Field = N'm_blockOffset')
  932.  
  933.   SELECT @lsn_file = CONVERT(INT, Value)
  934.   FROM #db_info
  935.   WHERE (Object = N'dbi_dbbackupLSN')
  936.     AND (Field = N'm_fSeqNo')
  937.  
  938.   SELECT @db_backup_lsn = @lsn_file
  939.   SELECT @db_backup_lsn = @db_backup_lsn * 10000000000
  940.   SELECT @db_backup_lsn = @db_backup_lsn + @lsn_block
  941.   SELECT @db_backup_lsn = @db_backup_lsn * 100000
  942.   SELECT @db_backup_lsn = @db_backup_lsn + @lsn_slot
  943.  
  944.   SELECT TOP 1 @backup_type = BackupType
  945.   FROM #backup_header
  946.  
  947.   -- Null fork id's indicate an old system, for which this backup scheme won't work,
  948.   -- so we set a null result to a value different than the db fork_id.
  949.  
  950.   SELECT TOP 1 @backup_recovery_fork_id = ISNULL(RecoveryForkId, 0x01)
  951.   FROM #backup_header
  952.  
  953.   SELECT TOP 1 @backup_last_lsn = ISNULL(LastLsn, 0)
  954.   FROM #backup_header
  955.  
  956.   -- Basic sanity checks
  957.  
  958.   IF ((SELECT TOP 1 DatabaseName FROM #backup_header) <> @database_name)
  959.   BEGIN
  960.     RAISERROR(14418,-1,-1, @database_name)
  961.     RETURN(1) -- Failure
  962.   END
  963.  
  964.   IF (@backup_type <> 1)
  965.   BEGIN
  966.     RAISERROR(14419,-1,-1)
  967.     RETURN(1) -- Failure
  968.   END
  969.  
  970.   -- Do the magic.
  971.   IF (@backup_type = 1) AND
  972.      ((@db_recovery_fork_id0 = @backup_recovery_fork_id) OR
  973.       (@db_recovery_fork_id1 = @backup_recovery_fork_id)) AND
  974.      (@db_backup_lsn <= @backup_last_lsn)
  975.     SELECT @result = 1
  976.   ELSE
  977.     SELECT @result = 0
  978.  
  979.   RETURN(0) -- Success
  980. END
  981. go
  982.  
  983. /**************************************************************/
  984. /* sp_change_primary_role                                     */
  985. /**************************************************************/
  986. PRINT 'Creating procedure sp_change_primary_role.'
  987. go
  988. CREATE PROCEDURE sp_change_primary_role 
  989.   @db_name       sysname,
  990.   @backup_log    BIT = 1,
  991.   @terminate     BIT = 0,
  992.   @final_state   SMALLINT = 2, -- 1 = RECOVERY, 2 = NORECOVERY, 3 = STANDBY
  993.   @access_level  SMALLINT = 1  -- 1 = multi user, 2 = dbo, 3 = single user
  994. AS BEGIN
  995.   DECLARE @maint_plan_id UNIQUEIDENTIFIER
  996.   DECLARE @job_id        UNIQUEIDENTIFIER
  997.   DECLARE @rv            INT
  998.   DECLARE @cur_date      DATETIME
  999.   DECLARE @command       NVARCHAR (3200)
  1000.   DECLARE @pat_index     INT
  1001.   DECLARE @backup_loc    NVARCHAR (500)
  1002.   DECLARE @backup_ext    NVARCHAR (10)
  1003.   DECLARE @backup_fn     NVARCHAR (1000)
  1004.   DECLARE @return_value  INT
  1005.   SET NOCOUNT ON
  1006.  
  1007.   SELECT @return_value = -1
  1008.  
  1009.   -- check final_state is valid
  1010.   IF (@final_state NOT IN (1,2,3))
  1011.   BEGIN
  1012.     RAISERROR (14266,-1,-1, N'@final_state', '1,2,3')
  1013.     RETURN (1) -- ERROR
  1014.   END
  1015.  
  1016.   -- check access_level is valid
  1017.   IF (@access_level NOT IN (1,2,3))
  1018.   BEGIN
  1019.     RAISERROR (14266,-1,-1, N'@access_level', '1,2,3')
  1020.     RETURN (1) -- ERROR
  1021.   END
  1022.   
  1023.   IF (@db_name IS NULL)
  1024.   BEGIN
  1025.     RAISERROR (14043,-1,-1,N'@db_name')
  1026.     RETURN (1) -- ERROR 
  1027.   END
  1028.  
  1029.   IF (@terminate = 1)
  1030.   BEGIN
  1031.     SELECT @command = N'ALTER DATABASE [' + @db_name + N'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
  1032.     EXECUTE @rv = sp_executesql @command
  1033.     IF (@rv <> 0 OR DATABASEPROPERTY (@db_name, N'IsSingleUser') <> 1) 
  1034.     BEGIN
  1035.       RAISERROR (14440,-1,-1)
  1036.       RETURN (1) -- error
  1037.     END
  1038.   END
  1039.  
  1040.   -- get the maintenance plan
  1041.   SELECT @maint_plan_id = maintenance_plan_id from msdb.dbo.log_shipping_databases where database_name = @db_name
  1042.   IF (@maint_plan_id IS NULL)
  1043.     RETURN (1) -- ERROR
  1044.   
  1045.   -- get the backup job
  1046.   SELECT @job_id = job_id FROM msdb.dbo.sysjobsteps 
  1047.   WHERE command like '%-BkUpLog%' AND 
  1048.     job_id IN (SELECT job_id FROM msdb.dbo.sysdbmaintplan_jobs WHERE plan_id = @maint_plan_id)  
  1049.  
  1050.   IF (@job_id IS NULL)
  1051.     RETURN (1) -- ERROR
  1052.  
  1053.   -- check that this maint plan is involved in log shipping
  1054.   IF NOT EXISTS (SELECT 1 FROM msdb.dbo.log_shipping_databases WHERE maintenance_plan_id = @maint_plan_id)
  1055.     RETURN (1) -- ERROR
  1056.  
  1057.   -- disable the transaction log dump
  1058.   EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0
  1059.  
  1060.   IF (@backup_log = 1)
  1061.   BEGIN
  1062.     SELECT @cur_date = GETDATE ()
  1063.     -- get the location of the tlog dumps
  1064.     SELECT @command = command FROM msdb.dbo.sysjobsteps WHERE job_id = @job_id
  1065.  
  1066.     SELECT @pat_index = PATINDEX (N'%-BkUpLog%', @command)
  1067.     IF (@pat_index IS NOT NULL AND @pat_index > 0 AND PATINDEX (N'%-UseDefDir%', @command) = 0)
  1068.     BEGIN
  1069.       -- trim "-"
  1070.       SELECT @backup_loc = RIGHT (@command, (LEN (@command) - @pat_index)-7)
  1071.       SELECT @backup_loc = LTRIM (@backup_loc)
  1072.       SELECT @backup_loc = RTRIM (@backup_loc)
  1073.  
  1074.       -- check to see if a location is given
  1075.       IF (PATINDEX (N'-%', @backup_loc) = 1)
  1076.         SELECT @backup_loc = NULL
  1077.       ElSE BEGIN
  1078.         -- need to trim off the ""
  1079.         SELECT @pat_index = PATINDEX (N'%"%', @backup_loc)
  1080.         IF (@pat_index IS NOT NULL AND @pat_index > 0)
  1081.         BEGIN
  1082.           SELECT @backup_loc = RIGHT (@backup_loc, LEN(@backup_loc) - 1)
  1083.           SELECT @pat_index = PATINDEX(N'%"%', @backup_loc)
  1084.         END
  1085.         ELSE
  1086.           SELECT @pat_index = PATINDEX (N'% %', @backup_loc)
  1087.  
  1088.         IF (@pat_index IS NOT NULL AND @pat_index > 0)
  1089.         BEGIN
  1090.           SELECT @backup_loc = LEFT (@backup_loc, @pat_index - 1)
  1091.           SELECT @backup_loc = @backup_loc + N'\'
  1092.         END
  1093.       END
  1094.     END
  1095.     ELSE
  1096.       SELECT @backup_loc = NULL
  1097.  
  1098.     -- same thing for file extension
  1099.     SELECT @pat_index = PATINDEX (N'%-BkExt%', @command)
  1100.     IF (@pat_index IS NOT NULL AND @pat_index > 0)
  1101.     BEGIN
  1102.       SELECT @backup_ext = RIGHT (@command, (LEN (@command) - @pat_index)-5)
  1103.       SELECT @backup_ext = LTRIM (@backup_ext)
  1104.       SELECT @backup_ext = RTRIM (@backup_ext)
  1105.       SELECT @pat_index = PATINDEX (N'%"%', @backup_ext)
  1106.       -- need to trim off the ""
  1107.       IF (@pat_index IS NOT NULL AND @pat_index > 0)
  1108.       BEGIN
  1109.         SELECT @backup_ext = RIGHT (@backup_ext, LEN(@backup_ext) - 1)
  1110.         SELECT @pat_index = PATINDEX(N'%"%', @backup_ext)
  1111.       END
  1112.       ELSE
  1113.         SELECT @pat_index = PATINDEX (N'% %', @backup_ext)
  1114.       IF (@pat_index IS NOT NULL AND @pat_index > 0)
  1115.       BEGIN
  1116.         SELECT @backup_ext = LEFT (@backup_ext, @pat_index - 1)
  1117.       END
  1118.     END
  1119.     ELSE BEGIN 
  1120.       SELECT @backup_ext = N'TRN'
  1121.     END
  1122.  
  1123.     -- now do the backup
  1124.     SELECT @cur_date = DATEADD (mi,1,@cur_date)
  1125.     SELECT @backup_fn = 
  1126.       ISNULL (@backup_loc, N'') + 
  1127.       @db_name + 
  1128.       N'_tlog_' + 
  1129.       CONVERT (NVARCHAR, @cur_date, 112) +
  1130.       LEFT (CONVERT (NVARCHAR, @cur_date, 8), 2) +
  1131.       LEFT (RIGHT (CONVERT (NVARCHAR, @cur_date, 8), 5), 2) +
  1132.       N'.' +
  1133.       @backup_ext
  1134.  
  1135.     IF (@final_state = 2)
  1136.     BEGIN
  1137.       BACKUP LOG @db_name TO DISK = @backup_fn WITH NORECOVERY
  1138.       IF (@@error <> 0) GOTO finish
  1139.     END
  1140.     ELSE IF (@final_state = 3)
  1141.     BEGIN
  1142.       DECLARE @undo_file NVARCHAR (500)
  1143.       SELECT @undo_file = ISNULL (@backup_loc, N'') + @db_name + N'.tuf'
  1144.       BACKUP LOG @db_name TO DISK = @backup_fn WITH STANDBY = @undo_file
  1145.       IF (@@error <> 0) GOTO finish
  1146.     END
  1147.     ELSE BEGIN
  1148.       BACKUP LOG @db_name TO DISK = @backup_fn
  1149.       IF (@@error <> 0) GOTO finish
  1150.     END
  1151.   END
  1152.  
  1153.   EXECUTE msdb.dbo.sp_delete_log_shipping_database @db_name = @db_name
  1154.  
  1155.   SELECT @return_value = 0
  1156.  
  1157.   finish:
  1158.   IF (@final_state <> 2)
  1159.   BEGIN
  1160.     -- put the db in it's final state -- 1 = multi user, 2 = dbo, 3 = single user
  1161.     SELECT @command = N'ALTER DATABASE [' + @db_name + N'] SET '
  1162.     IF (@access_level = 1) SELECT @command = @command + 'MULTI_USER'
  1163.     IF (@access_level = 2) SELECT @command = @command + 'RESTRICTED_USER'
  1164.     IF (@access_level = 3) SELECT @command = @command + 'SINGLE_USER'
  1165.  
  1166.     EXECUTE sp_executesql @command
  1167.   END
  1168.  
  1169.   RETURN @return_value
  1170. END
  1171. go
  1172.  
  1173. /**************************************************************/
  1174. /* sp_change_secondary_role                                   */
  1175. /**************************************************************/
  1176. PRINT 'Creating procedure sp_change_secondary_role.'
  1177. go
  1178. CREATE PROCEDURE sp_change_secondary_role
  1179.   @db_name          sysname,
  1180.   @do_load          BIT = 1,
  1181.   @force_load       BIT = 1,
  1182.   @final_state      SMALLINT = 1, -- 1 = RECOVERY, 2 = NORECOVERY, 3 = STANDBY
  1183.   @access_level     SMALLINT = 1, -- 1 = multi user, 2 = dbo, 3 = single user
  1184.   @terminate        BIT = 1,
  1185.   @keep_replication BIT = 0,
  1186.   @stopat           DATETIME = NULL
  1187. AS BEGIN
  1188.   SET NOCOUNT ON
  1189.   DECLARE @job_id             UNIQUEIDENTIFIER
  1190.   DECLARE @plan_id           UNIQUEIDENTIFIER
  1191.   DECLARE @maint_plan_id UNIQUEIDENTIFIER
  1192.   DECLARE @command           NVARCHAR (3200)
  1193.   DECLARE @backup_job_id UNIQUEIDENTIFIER
  1194.   DECLARE @rv            INT
  1195.   DECLARE @return_value  INT
  1196.   DECLARE @pat_index     INT
  1197.  
  1198.   -- check final_state is valid
  1199.   IF (@final_state NOT IN (1,2,3))
  1200.   BEGIN
  1201.     RAISERROR (14266,-1,-1, N'@final_state', '1,2,3')
  1202.     RETURN (1) -- ERROR
  1203.   END
  1204.  
  1205.   -- chaeck access_level is valid
  1206.   IF (@access_level NOT IN (1,2,3))
  1207.   BEGIN
  1208.     RAISERROR (14266,-1,-1, N'@access_level', '1,2,3')
  1209.     RETURN (1) -- ERROR
  1210.   END
  1211.  
  1212.   IF (@db_name IS NULL)
  1213.   BEGIN
  1214.     RAISERROR (14043,-1,-1,N'@db_name')
  1215.     RETURN (1) -- ERROR 
  1216.   END
  1217.  
  1218.   SELECT @return_value = -1
  1219.  
  1220.   IF (@terminate = 1)
  1221.   BEGIN
  1222.     SELECT @command = N'ALTER DATABASE [' + @db_name + N'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
  1223.     EXECUTE @rv = sp_executesql @command
  1224.     IF (@rv <> 0 OR DATABASEPROPERTY (@db_name, N'IsSingleUser') <> 1) 
  1225.     BEGIN
  1226.       RAISERROR (14440,-1,-1)
  1227.       RETURN (1) -- error
  1228.     END
  1229.   END
  1230.  
  1231.   SELECT @plan_id = plan_id FROM msdb.dbo.log_shipping_plan_databases WHERE destination_database = @db_name
  1232.   IF (@plan_id IS NULL)
  1233.   BEGIN
  1234.     RAISERROR (14425,-1,-1, @db_name)
  1235.     GOTO finish
  1236.   END
  1237.  
  1238.   SELECT @backup_job_id = backup_job_id, @maint_plan_id = maintenance_plan_id FROM msdb.dbo.log_shipping_plans WHERE plan_id = @plan_id
  1239.  
  1240.  
  1241.   -- copy any extra files
  1242.   IF (@do_load > 0)
  1243.   BEGIN
  1244.     SELECT @job_id =  copy_job_id FROM msdb.dbo.log_shipping_plans WHERE plan_id = @plan_id
  1245.     SELECT @command = command FROM sysjobsteps WHERE job_id = @job_id
  1246.     SELECT @pat_index = PATINDEX (N'%''%', @command)
  1247.     IF (@pat_index IS NULL)
  1248.     BEGIN
  1249.       GOTO finish
  1250.     END
  1251.     
  1252.     SELECT @command = RIGHT (@command, LEN (@command) - @pat_index)
  1253.     SELECT @command = LEFT  (@command, LEN (@command) - 1)
  1254.     EXECUTE @rv = master.dbo.xp_sqlmaint @command
  1255.     IF (@rv <> 0)
  1256.     BEGIN
  1257.       GOTO finish
  1258.     END
  1259.   END
  1260.  
  1261.   -- disable the copy job as we're now dumping tlogs to this location
  1262.   EXECUTE msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0
  1263.  
  1264.   -- restore tlogs
  1265.   IF (@do_load > 0)
  1266.   BEGIN
  1267.     SELECT @job_id = load_job_id FROM msdb.dbo.log_shipping_plans WHERE plan_id = @plan_id
  1268.     SELECT @command = command FROM sysjobsteps WHERE job_id = @job_id
  1269.     SELECT @pat_index = PATINDEX (N'%''%', @command)
  1270.     IF (@pat_index IS NULL)
  1271.     BEGIN
  1272.       GOTO finish
  1273.     END
  1274.     
  1275.     SELECT @command = RIGHT (@command, LEN (@command) - @pat_index)
  1276.     -- strip off final '
  1277.     DECLARE @len INT
  1278.     SELECT @len = LEN (@command)
  1279.     SELECT @command = LEFT (@command, @len-1)
  1280.     IF (@force_load > 0)
  1281.     BEGIN
  1282.       SELECT @command = @command + N' -ForceLoad'
  1283.     END
  1284.     IF (@keep_replication > 0)
  1285.     BEGIN
  1286.       SELECT @command = @command + N' -KeepRepl'
  1287.     END
  1288.     IF (@stopat IS NOT NULL)
  1289.     BEGIN
  1290.       SELECT @command = @command + N' -StopAt "' + CONVERT (NVARCHAR, @stopat, 20) + '"'
  1291.     END
  1292.     EXECUTE @rv = master.dbo.xp_sqlmaint @command
  1293.     IF (@rv <> 0)
  1294.     BEGIN
  1295.       GOTO finish
  1296.     END
  1297.   END
  1298.   
  1299.   -- bring the database online
  1300.   IF (@final_state = 1)
  1301.     RESTORE DATABASE @db_name WITH RECOVERY
  1302.   ELSE IF (@final_state = 2) 
  1303.     RESTORE DATABASE @db_name WITH NORECOVERY
  1304.   ELSE IF (@final_state = 3)
  1305.   BEGIN
  1306.     SELECT @command = (SELECT destination_dir FROM msdb.dbo.log_shipping_plans WHERE plan_id = @plan_id) + N'\' + @db_name + N'.TUF'
  1307.     RESTORE DATABASE @db_name WITH STANDBY = @command
  1308.   END
  1309.  
  1310.   IF (@@error <> 0) GOTO finish
  1311.  
  1312.   -- remove the database from the logshipping plan
  1313.   EXECUTE msdb.dbo.sp_delete_log_shipping_plan_database @destination_database = @db_name
  1314.  
  1315.   -- add the database to log_shipping_databases
  1316.   EXECUTE msdb.dbo.sp_add_log_shipping_database @db_name = @db_name, @maintenance_plan_id = @maint_plan_id
  1317.  
  1318.   -- bring the job online
  1319.   IF (@backup_job_id IS NOT NULL)
  1320.     EXECUTE sp_update_job @job_id = @backup_job_id, @enabled = 1
  1321.  
  1322.   -- if there are no more databases in this plan, delete it
  1323.   IF (NOT EXISTS (SELECT * FROM msdb..log_shipping_plan_databases WHERE plan_id = @plan_id))
  1324.   BEGIN
  1325.     EXECUTE msdb.dbo.sp_delete_log_shipping_plan @plan_id = @plan_id
  1326.   END
  1327.   ELSE BEGIN
  1328.     -- delete extra maint plan information
  1329.     UPDATE msdb.dbo.log_shipping_plans 
  1330.       SET maintenance_plan_id = NULL, backup_job_id = NULL, share_name = NULL 
  1331.       WHERE @plan_id = @plan_id
  1332.   END
  1333.  
  1334.   SELECT @return_value = 0
  1335.  
  1336. finish:
  1337.   IF (@final_state <> 2)
  1338.   BEGIN
  1339.     -- put the db in it's final state -- 1 = multi user, 2 = dbo, 3 = single user
  1340.     SELECT @command = N'ALTER DATABASE [' + @db_name + N'] SET '
  1341.     IF (@access_level = 1) SELECT @command = @command + 'MULTI_USER'
  1342.     IF (@access_level = 2) SELECT @command = @command + 'RESTRICTED_USER'
  1343.     IF (@access_level = 3) SELECT @command = @command + 'SINGLE_USER'
  1344.  
  1345.     EXECUTE sp_executesql @command
  1346.   END
  1347.  
  1348.   RETURN @return_value
  1349. END
  1350. go
  1351.  
  1352. /**************************************************************/
  1353. /* sp_remove_log_shipping_monitor                             */
  1354. /**************************************************************/
  1355. PRINT 'Creating procedure sp_remove_log_shipping_monitor.'
  1356. go
  1357. CREATE PROCEDURE sp_remove_log_shipping_monitor AS
  1358. BEGIN
  1359.   SET NOCOUNT ON
  1360.   -- do not delete the monitor if there this is a primary or secondary server that is still log shipping
  1361.  
  1362.   -- check that there are no rows in log_shipping_plans
  1363.   IF (EXISTS (SELECT * FROM msdb.dbo.log_shipping_plans))
  1364.   BEGIN
  1365.     RAISERROR (14428,-1,-1)
  1366.     RETURN (1) -- error
  1367.   END
  1368.  
  1369.   -- check that there are no rows in log_shipping_databases
  1370.   IF (EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases))
  1371.   BEGIN
  1372.     RAISERROR (14428,-1,-1)
  1373.     RETURN (1) -- error
  1374.   END
  1375.  
  1376.   DELETE FROM msdb.dbo.log_shipping_monitor
  1377.   RETURN (0) -- success
  1378. END
  1379. go
  1380.  
  1381. /**************************************************************/
  1382. /* sp_define_log_shipping_monitor                             */
  1383. /**************************************************************/
  1384. PRINT 'Creating procedure sp_define_log_shipping_monitor.'
  1385. go
  1386. CREATE PROCEDURE sp_define_log_shipping_monitor
  1387.   @monitor_name    sysname,
  1388.   @logon_type      INT,
  1389.   @user_name       NVARCHAR(63)    = NULL,
  1390.   @password        NVARCHAR(63)    = NULL,
  1391.   @delete_existing BIT        = 0
  1392. AS BEGIN
  1393.   DECLARE @logon_data   VARBINARY(256)
  1394.  
  1395.   SET NOCOUNT ON
  1396.  
  1397.   SELECT @logon_data = NULL
  1398.  
  1399. -- check for an existing monitor. There can be only one
  1400.   IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_monitor)
  1401.   BEGIN
  1402.     IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_monitor WHERE monitor_server_name = @monitor_name)
  1403.       SELECT @delete_existing = 1
  1404.     IF @delete_existing = 0
  1405.     BEGIN
  1406.       RAISERROR (14426,-1,-1)
  1407.       RETURN (1)
  1408.     END
  1409.     SET ROWCOUNT 1
  1410.     DELETE FROM msdb.dbo.log_shipping_monitor
  1411.     SET ROWCOUNT 0
  1412.   END
  1413.  
  1414.   IF @logon_type NOT BETWEEN 1 AND 2
  1415.   BEGIN
  1416.     RAISERROR (14266,-1,-1, N'@logon_type', N'1,2')
  1417.     RETURN (1)
  1418.   END
  1419.  
  1420.   IF @logon_type = 2
  1421.   BEGIN
  1422.     IF @user_name IS NULL
  1423.     BEGIN
  1424.       RAISERROR (14415,-1,-1)
  1425.       RETURN (1)
  1426.     END
  1427.     IF (@password IS NOT NULL)
  1428.     BEGIN
  1429.       EXECUTE master..xp_repl_encrypt @password OUTPUT
  1430.       SELECT @logon_data = convert (VARBINARY(256), @password)
  1431.     END
  1432.   END
  1433.   INSERT INTO log_shipping_monitor VALUES (@monitor_name, @logon_type, @logon_data)
  1434. END
  1435. go
  1436.  
  1437. /**************************************************************/
  1438. /* marked stored procedures as system shipped objects         */
  1439. /**************************************************************/
  1440. EXEC dbo.sp_MS_marksystemobject sp_update_log_shipping_plan
  1441. EXEC dbo.sp_MS_marksystemobject sp_add_log_shipping_plan
  1442. EXEC dbo.sp_MS_marksystemobject sp_delete_log_shipping_plan
  1443. EXEC dbo.sp_MS_marksystemobject sp_add_log_shipping_plan_database
  1444. EXEC dbo.sp_MS_marksystemobject sp_delete_log_shipping_plan_database
  1445. EXEC dbo.sp_MS_marksystemobject sp_add_log_shipping_database
  1446. EXEC dbo.sp_MS_marksystemobject sp_delete_log_shipping_database
  1447. EXEC dbo.sp_MS_marksystemobject sp_verify_lsp_identifiers
  1448. EXEC dbo.sp_MS_marksystemobject sp_update_log_shipping_plan_database
  1449. EXEC dbo.sp_MS_marksystemobject sp_remove_log_shipping_monitor
  1450. EXEC dbo.sp_MS_marksystemobject sp_can_tlog_be_applied
  1451. EXEC dbo.sp_MS_marksystemobject sp_change_primary_role
  1452. EXEC dbo.sp_MS_marksystemobject sp_change_secondary_role
  1453. EXEC dbo.sp_MS_marksystemobject sp_define_log_shipping_monitor
  1454. go
  1455.  
  1456. PRINT '--------------------------------'
  1457. PRINT 'Finished execution of INSTLS.SQL'
  1458. PRINT '--------------------------------'
  1459. go
  1460.