April 25, 2012

sp_help_log_shipping_primary_database (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_help_log_shipping_primary_database(nvarchar @database
, uniqueidentifier @primary_id)

MetaData:

 create procedure sys.sp_help_log_shipping_primary_database   
(
@database sysname = NULL
,@primary_id uniqueidentifier = NULL
)
as
begin
set nocount on
declare @retcode int

--
-- security check
--
exec @retcode = sys.sp_MSlogshippingsysadmincheck
if (@retcode != 0 or @@error != 0)
return 1
--
-- must be invoked from master db
--
if (db_name() != N'master')
begin
raiserror(5001, 16,-1)
return 1
end
--
-- validate input
--
if (@database is null and @primary_id is null)
begin
raiserror(14524, 16, 1, N'@database', N'@primary_id')
return 1
end
--
-- return resultset
--
select
pd.primary_id
,pd.primary_database
,pd.backup_directory
,pd.backup_share
,pd.backup_retention_period
,pd.backup_compression -- note: sqllogship.exe is sensitive to the ordering of these results
,pd.backup_job_id
,'monitor_server' = case when (pd.user_specified_monitor = 1) then pd.monitor_server else cast(NULL as sysname) end
,'monitor_server_security_mode' = case when (pd.user_specified_monitor = 1) then pd.monitor_server_security_mode else cast(NULL as bit) end
,mp.backup_threshold
,mp.threshold_alert
,mp.threshold_alert_enabled
,mp.last_backup_file
,mp.last_backup_date
,mp.last_backup_date_utc
,mp.history_retention_period
from msdb.dbo.log_shipping_primary_databases as pd join msdb.dbo.log_shipping_monitor_primary as mp
on pd.primary_id = mp.primary_id
where (@database is not null and pd.primary_database = @database)
or (@primary_id is not null and pd.primary_id = @primary_id)
--
-- all done
--
return 0
end

No comments:

Post a Comment

Total Pageviews