Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_logshippinginstallmetadata

  No additional text.


Syntax
create procedure sys.sp_logshippinginstallmetadata
as
begin
    set nocount on
    declare @retcode int

    
    -- security check to see if we should install
    
    exec @retcode = sys.sp_MSlogshippingsysadmincheck @mode = 0
    if (@retcode != 0 or @@error != 0)
        return 1
    
    -- MSDB should exist
    
    if not exists (select * from master.sys.databases where name = N'msdb')
    begin
        raiserror(15010, 16, 1, N'msdb')
        return 1
    end
    if (DATABASEPROPERTYEX(N'msdb', N'Status') != N'ONLINE')
    begin
        raiserror(32007, 16, 1, N'msdb')
        return 1
    end
    
    -- start transaction
    
    begin tran sp_logshippinginstallmetadata
    save tran sp_logshippinginstallmetadata
    
    -- create the tables
    
    if object_id(N'msdb.dbo.log_shipping_primary_databases') is null
    begin
        create table msdb.dbo.log_shipping_primary_databases
        (
            primary_id  uniqueidentifier primary key not null,
            primary_database sysname unique not null,
            backup_directory nvarchar(500) not null,
            backup_share nvarchar(500) not null,
            backup_retention_period int not null, -- minutes (default=1440)
            backup_job_id uniqueidentifier not null,
            monitor_server sysname not null, -- (default = primary server)
            user_specified_monitor bit null,
            monitor_server_security_mode bit not null, -- (default = 1 (integrated))
            last_backup_file nvarchar(500) null, -- (initially null)
            last_backup_date datetime null, -- (initially null)
            backup_compression tinyint not null default 2 --(default to server default)
        )
        exec msdb.dbo.sp_MS_marksystemobject 'log_shipping_primary_databases'
        create unique nonclustered index uc1lsprimary_databases
            on msdb.dbo.log_shipping_primary_databases (primary_database)
        create nonclustered index nc1lsprimary_databases
            on msdb.dbo.log_shipping_primary_databases (monitor_server)
        create nonclustered index nc2lsprimary_databases
            on msdb.dbo.log_shipping_primary_databases (user_specified_monitor)
    end

    if object_id(N'msdb.dbo.log_shipping_primary_secondaries') is null
    begin
        create table msdb.dbo.log_shipping_primary_secondaries
        (
            primary_id  uniqueidentifier not null,
            secondary_server sysname not null,
            secondary_database sysname not null,
            constraint pklsprimary_secondaries
                primary key (primary_id, secondary_server, secondary_database)
        )
        exec msdb.dbo.sp_MS_marksystemobject 'log_shipping_primary_secondaries'
        create nonclustered index nc1lsprimary_secondaries
            on msdb.dbo.log_shipping_primary_secondaries (primary_id)
    end

    if object_id(N'msdb.dbo.log_shipping_monitor_primary') is null
    begin
        create table msdb.dbo.log_shipping_monitor_primary
        (
            primary_id uniqueidentifier primary key not null,
            primary_server sysname not null,
            primary_database sysname not null,
            backup_threshold int not null,  -- in minutes (default=45)
            threshold_alert int not null, -- (default=14420)
            threshold_alert_enabled bit not null, -- (default=1)
            last_backup_file nvarchar(500) null, -- (initially null)
            last_backup_date datetime null,  -- (initially null)
            last_backup_date_utc datetime null, -- (initially null)
            history_retention_period int not null -- minutes (default=1440)
        )
        exec msdb.dbo.sp_MS_marksystemobject 'log_shipping_monitor_primary'
        create unique nonclustered index uc1lsmonitor_primary
            on msdb.dbo.log_shipping_monitor_primary (primary_server, primary_database)
    end

    if object_id(N'msdb.dbo.log_shipping_monitor_history_detail') is null
    begin
        create table msdb.dbo.log_shipping_monitor_history_detail
        (
            agent_id uniqueidentifier not null, -- primary id for backup, secondary id for copy/restore
            agent_type tinyint not null, -- 0 = Backup, 1 = Copy, 2= Restore
            session_id int not null, -- session number for the backup/copy/restore/job
            database_name sysname null, -- primary db for backup, empty for copy, secondary db for restore
            session_status tinyint not null, -- 0 = Starting, 1 = Running, 2 = Success, 3 = Error, 4 = Warning
            log_time datetime  not null,
            log_time_utc datetime  not null,
            message nvarchar(4000) not null
        )
        exec msdb.dbo.sp_MS_marksystemobject 'log_shipping_monitor_history_detail'
        create clustered index c1lsmonitor_history_detail
            on msdb.dbo.log_shipping_monitor_history_detail (agent_id, agent_type)
        create nonclustered index nc2lsmonitor_history_detail
            on msdb.dbo.log_shipping_monitor_history_detail (database_name)
        create nonclustered index nc3lsmonitor_history_detail
            on msdb.dbo.log_shipping_monitor_history_detail (log_time_utc)
    end

    if object_id(N'msdb.dbo.log_shipping_monitor_error_detail') is null
    begin
        create table msdb.dbo.log_shipping_monitor_error_detail
        (
            agent_id uniqueidentifier not null, -- primary id for backup, secondary id for copy/restore
            agent_type tinyint not null, -- 0 = Backup, 1 = Copy, 2= Restore
            session_id int   not null, -- session number for the backup/copy/restore/job
            database_name sysname null, -- primary db for backup, empty for copy, secondary db for restore
            sequence_number int not null,
            log_time datetime not null,
            log_time_utc datetime not null,
            message nvarchar(4000)   not null,
            source nvarchar(4000)   not null,
            help_url nvarchar(4000)   not null
        )
        exec msdb.dbo.sp_MS_marksystemobject 'log_shipping_monitor_error_detail'
        create clustered index c1lsmonitor_error_detail
            on msdb.dbo.log_shipping_monitor_error_detail (agent_id, agent_type)
        create nonclustered index nc2lsmonitor_error_detail
            on msdb.dbo.log_shipping_monitor_error_detail (database_name)
        create nonclustered index nc3lsmonitor_error_detail
            on msdb.dbo.log_shipping_monitor_error_detail (log_time_utc)
    end

    if object_id(N'msdb.dbo.log_shipping_secondary') is null
    begin
        create table msdb.dbo.log_shipping_secondary
        (
            secondary_id uniqueidentifier primary key not null,
            primary_server sysname not null,
            primary_database sysname not null,
            backup_source_directory nvarchar(500) not null,
            backup_destination_directory nvarchar(500) not null,
            file_retention_period int not null, -- in minutes (default=1440)
            copy_job_id uniqueidentifier not null,
            restore_job_id uniqueidentifier not null,
            monitor_server sysname not null, --(default = secondary server)
            monitor_server_security_mode bit not null, -- (default = 1 (integrated))
            user_specified_monitor bit null,
            last_copied_file nvarchar(500) null, -- (initially null)
            last_copied_date datetime null, -- (initially null)
        )
        exec msdb.dbo.sp_MS_marksystemobject 'log_shipping_secondary'
        create unique nonclustered index uc1lssecondary
            on msdb.dbo.log_shipping_secondary (primary_server, primary_database)
        create nonclustered index nc1lssecondary
            on msdb.dbo.log_shipping_secondary (monitor_server)
        create nonclustered index nc2lssecondary
  on msdb.dbo.log_shipping_secondary (user_specified_monitor)
    end

    if object_id(N'msdb.dbo.log_shipping_secondary_databases') is null
    begin
        create table msdb.dbo.log_shipping_secondary_databases
        (
            secondary_database sysname primary key not null,
            secondary_id uniqueidentifier not null,
            restore_delay int not null, -- in minutes (default=0)
            restore_all bit not null, -- (default=1)
            restore_mode bit not null, -- (default=0)
            disconnect_users bit not null, -- (default=0)
            block_size int null, -- (default=-1)
            buffer_count int null, -- (default=-1)
            max_transfer_size int null, -- (default=-1)
            last_restored_file nvarchar(500) null, -- (initially null)
            last_restored_date datetime null -- (initially null)
        )
        exec msdb.dbo.sp_MS_marksystemobject 'log_shipping_secondary_databases'
        create nonclustered index nc1lssecondary_databases
            on msdb.dbo.log_shipping_secondary_databases (secondary_id)
    end

    if object_id(N'msdb.dbo.log_shipping_monitor_secondary') is null
    begin
        create table msdb.dbo.log_shipping_monitor_secondary
        (
            secondary_server sysname not null,
            secondary_database sysname not null,
            secondary_id uniqueidentifier not null,
            primary_server sysname not null,
            primary_database sysname not null,
            restore_threshold int not null, -- in minutes (default=45)
            threshold_alert int not null, -- (default=14421)
            threshold_alert_enabled bit not null, -- (default=1)
            last_copied_file nvarchar(500) null, -- (initially null)
            last_copied_date datetime null, -- (initially null)
            last_copied_date_utc datetime null, -- (initially null)
            last_restored_file nvarchar(500) null, -- (initially null)
            last_restored_date datetime null, -- (initially null)
            last_restored_date_utc datetime null, -- (initially null)
            last_restored_latency int null, -- (initially null)
            history_retention_period int not null, -- minutes (default=1440)
            constraint pklsmonitor_secondary
                primary key (secondary_id, secondary_database)
        )
        exec msdb.dbo.sp_MS_marksystemobject 'log_shipping_monitor_secondary'
        create unique nonclustered index uc1lsmonitor_secondary
            on msdb.dbo.log_shipping_monitor_secondary (secondary_server, secondary_database)
        create nonclustered index nc1lsmonitor_secondary
            on msdb.dbo.log_shipping_monitor_secondary (secondary_id)
        create nonclustered index nc2lsmonitor_secondary
            on msdb.dbo.log_shipping_monitor_secondary (restore_threshold)
        create nonclustered index nc3lsmonitor_secondary
            on msdb.dbo.log_shipping_monitor_secondary (last_restored_latency)
    end

    if object_id(N'msdb.dbo.log_shipping_monitor_alert') is null
    begin
        create table msdb.dbo.log_shipping_monitor_alert
        (
            alert_job_id uniqueidentifier primary key not null
        )
      exec msdb.dbo.sp_MS_marksystemobject 'log_shipping_monitor_alert'
    end
/*
    
    -- NO need for this anymore - system category now exists for LS
    -- add category for logshipping if needed
    
    if not exists (select * from msdb.dbo.syscategories where name=N'Log Shipping' and category_class=1)
    begin
        exec @retcode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Log Shipping'
        if (@@error != 0 or @retcode != 0)
            goto UNDO
    end
*/
    
    -- commit transaction
    
    commit tran
    
    -- all done
    
    return 0

UNDO:
    rollback tran sp_logshippinginstallmetadata
    commit tran
    return 1
end

 
Last revision 2008RTM
See also

  sp_upgrade_log_shipping (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash