Reinitialize SQL server log Shipping

I have been busy with other things over the past few days and did not pay attention to the status of log shipping. I checked the report this morning and found that the database of log shipping has not been synchronized for several days. The database status is restoring, you need to reinitialize it.

 

Steps:

 

1. Restore the complete backup of the nearest Database

 

2. Restore all log backups after the complete backup. Use the following script to find all log backups after the last full backup, and then run the following command directly on the secondary database:

 

Select 'Restore log ['+ database_name +
'] Fromdisk = ''' + physical_device_name
+ ''' With norecovery'

From MSDB. DBO. backupset ainnerjoin MSDB. DBO. backupmediafamilyb

On a. media_set_id = B. media_set_id

Wheredatabase_name = 'yourdatabasename 'andtype = 'l'

Andbackup_start_date> =

(Selecttop 1 backup_start_datefrom MSDB. DBO. backupset

Wheredatabase_name = 'yourdatabasename 'andtype = 'D' orderbybackup _ start_datedesc

)

Order by A. backup_start_date

 

3. Run the backup and restore job to display that the latest log has been restored to the secondary database.

 

At the same time, the preceding statement can also be used to restore the database, which is very convenient.