ch 29 - advanced availability.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing  
-- Paul Nielsen

-- Chapter 29 - Advanced Availability

-----------------------------------------------------------
-----------------------------------------------------------
-- INITIALIZE THE LOG SHIPPING

-- On Warm Standby
CREATE PROCEDURE LogShipInitializeReceive
AS
SET NoCount ON
RESTORE DATABASE CHA2
  FROM DISK = '\\Noli\LogShipping\CHA2Initilze.bak'
  WITH 
    FILE = 1, 
    NORECOVERY,
    MOVE 'CHA2' TO 'c:\SQL2\CHA2.mdf',
   MOVE 'CHA2_log' TO 'c:\SQL2\CHA2.ldf'

-- On Primary 
USE CHA2
go
CREATE PROCEDURE LogShipInitialze
AS
SET NoCount ON
Print 'Backing up Primary Server'
BACKUP DATABASE CHA2
  TO DISK = 'c:\LogShipping\CHA2Initilze.bak'
  WITH 
    NAME = 'CHA2Initilze',
    INIT
PRINT '----- '
Print 'Restoring Warm Standby Server'
EXEC [NOLI\SQL2].Master.dbo.LogShipInitializeReceive

EXEC LogShipInitialze

----------------------------------------------
-- Ship the transaction log 

-- On Warm Standby
CREATE PROCEDURE LogShipJobReceive
AS
SET NoCount ON
RESTORE LOG CHA2
  FROM DISK = '\\Noli\LogShipping\CHA2Job.bak'
  WITH 
    FILE = 1, 
    STANDBY = 'c:\SQL2\CHA2.sby',
    MOVE 'CHA2' TO 'c:\SQL2\CHA2.mdf',
    MOVE 'CHA2_log' TO 'c:\SQL2\CHA2.ldf'

-- On Primary 
USE CHA2
go
CREATE PROCEDURE LogShipJob
AS
SET NoCount ON
Print 'Log Ship from Primary Server'
BACKUP LOG CHA2
  TO DISK = 'c:\LogShipping\CHA2Job.bak'
  WITH 
    NAME = 'CHA2Log',
    INIT
PRINT '----- '
Print 'Receiving Log Ship on Warm Standby Server'
EXEC [NOLI\SQL2].Master.dbo.LogShipJobReceive

EXEC LogShipJob

------------------------------------
USE msdb
EXEC msdb.dbo.sp_add_job
  @Job_Name = 'LogShip_CHA2',
  @Enabled = 1,
  @Owner_login_name = 'NOLI\SQL',
  @Description = 'log shipping with stored procedures'
 
EXEC msdb.dbo.sp_add_jobstep 
  @Job_Name = 'LogShip_CHA2',
  @SubSystem = 'TSQL',
  @Step_Name = 'LogShip',
  @Database_Name = 'CHA2',
  @Retry_Attempts = 3,
  @Command = 'EXEC LogShipJob'

EXEC msdb.dbo.sp_add_JobSchedule
  @Job_Name = 'LogShip_CHA2',
  @Name = 'FiveMin', 
  @freq_type = 4,  -- Daily
  @freq_interval = 1,
  @freq_subday_type = 0x4,  -- minutes
  @freq_subday_interval = 5 -- every 5 min

EXEC msdb.dbo.sp_add_jobserver
  @Job_Name = 'LogShip_CHA2',
  @server_name = N'(local)' 

--------------------------------------
-- Switching to the Warm Standby Server

CREATE PROCEDURE StandbyGoLive
AS
RESTORE DATABASE CHA2 WITH RECOVERY