What is difference between sp_add_log_shipping_secondary_database & sp_add_log_shipping_secondary_primary

sp_add_log_shipping_secondary_database & sp_add_log_shipping_secondary_primary

====================================================================

sp_add_log_shipping_secondary_database:-

======================================
1.Sets up a secondary databases for log shipping
2.sp_add_log_shipping_secondary_database must be run from the master database on the secondary server. This stored procedure does the following:

A.sp_add_log_shipping_secondary_primary should be called prior to this stored procedure to initialize the primary log shipping database information on the secondary server.

B.Adds an entry for the secondary database in log_shipping_secondary_databases using the supplied arguments.

C.Adds a local monitor record in log_shipping_monitor_secondary on the secondary server using supplied arguments.

D.If the monitor server is different from the secondary server, adds a monitor record in log_shipping_monitor_secondary on the monitor server using supplied arguments.


EXEC master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N'LogShipAdventureWorks'
,@primary_server = N'TRIBECA'
,@primary_database = N'AdventureWorks2012'
,@restore_delay = 0
,@restore_mode = 1
,@disconnect_users = 0
,@restore_threshold = 45 
,@threshold_alert_enabled = 0
,@history_retention_period = 1440 ;
GO

~~~~~~~~~~

sp_add_log_shipping_secondary_primary:-

==============================================

1. Sets up the primary information, adds local and remote monitor links, and creates copy and restore jobs on the secondary server for the specified primary database.
2. sp_add_log_shipping_secondary_primary must be run from the master database on the secondary server. This stored procedure does the following:
3. 
   
4. Generates a secondary ID for the specified primary server and primary database.

Does the following:

1. Adds an entry for the secondary ID in log_shipping_secondary using the supplied arguments.
2. Creates a copy job for the secondary ID that is disabled.
3. Sets the copy job ID in the log_shipping_secondary entry to the job ID of the copy job.
4. Creates a restore job for the secondary ID that is disabled.
5. Set the restore job ID in the log_shipping_secondary entry to the job ID of the restore job.

EXEC master.dbo.sp_add_log_shipping_secondary_primary   

@primary_server = N'TRIBECA'   

,@primary_database = N'AdventureWorks'   

,@backup_source_directory = N'\\tribeca\LogShipping'   

,@backup_destination_directory = N''   

,@copy_job_name = N''   

,@restore_job_name = N''   

,@file_retention_period = 1440   

,@monitor_server = N'ROCKAWAY'   

,@monitor_server_security_mode = 1   

,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT   

,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT   

,@secondary_id = @LS_Secondary__SecondaryId OUTPUT ;  

GO

Question on this topic:-

===============

Your Company has 4 servers named SQL1,SQL2,SQL3,SQL4 with SQl Server 2016 installed.

SQL1 hosts a line of a business database named HR_Applications.

SQL1 is the production server.All Servers have identical hardware.

You configure log shipping for the HR_Applications database.

You configure SQL1 as a primary server, SQL02 as a remote monitoring server, and SQl03 as a secondary server.

You want to configure SQL4 as a secondary server for the HR_Applications.

You need to configure SQL4 by using T-SQL queries.

Which query should you execute first?

1.sp_add_log_shipping_secondary_database @secondary_database = N'HR_Applications'  on SQL4

2.sp_add_log_shipping_secondary_database on SQL4

3.sp_add_log_shipping_secondary_primary on SQl4

4.sp_add_log_shipping_secondary_primary @primary_server= N'sql4' on SQL1

Ans:- 3


/* These queries will help you in troubleshooting log shipping issues */


DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'XXXXX'
-- Get Backup History
SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type] WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
--and physical_device_name = 'F:\Backup_08052018.bak'
ORDER BY backup_start_date DESC
,backup_finish_date

/* Backup history */

SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,
CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],
CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only,
DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
bs.backup_finish_date AS [Backup Finish Date], bmf.physical_device_name AS [Backup Location], bmf.physical_block_size
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = DB_NAME(DB_ID())
AND bs.[type] = 'L' -- Change to L if you want Log backups
ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);



DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type
 END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
 rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -2), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
and rsh.restore_type = 'L'
ORDER BY rsh.restore_date DESC
GO


SELECT
   [rs].[destination_database_name],
   [rs].[restore_date],
   [bs].[backup_start_date],
   [bs].[backup_finish_date],
   [bs].[database_name] as [source_database_name],
   [bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

/* To get the First_lsn and last_lsn */

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'SQLDatabase'
-- Get Backup History
SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type] WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC
,backup_finish_date

/* Script to check % of database restored */

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command like '%RESTORE%'