Wednesday, June 17, 2020

Unable to delete Log Shipping configuration


If you mistakenly delete the log shipping jobs, if you trying to reconfigure the log shipping for the same database again, and you are getting below error. 

The specified @job_id ('text') does not exist. (Microsoft SQL Server, Error: 14262) 

So here is the solution for your problem.


Run this below script in master databases.
Exec sp_delete_log_shipping_primary_secondary
@Primary_database = N'DATABASE_NAME'
,@secondary_server = N'SECONDARY_SERVER_NAME'
,@secondary_database = N'SECONDARY_DATABASE_NAME'
and to delete further all reference to that server.
also run this below stored procedure followed by
EXEC sp_delete_log_shipping_primary_database
@database = N'DATABASE_NAME'
you can get the list of backups taken from MSDB database using the following script:
DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'Test_Data'

-- Get Backup History
SELECT TOP (100) 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

No comments: