Sql-server – SQL server log shipping – dry-run data modification script on secondary then resume log shipping

I have manually reset the log shipping by updating the last_restored_file column in the log_shipping_monitor_secondary table on the standby server to the last transaction log that was successfully restored.

Something like this:

UPDATE lsms 
SET lsms.last_restored_file = '{transaction log file}'
FROM dbo.log_shipping_monitor_secondary lsms
WHERE lsms.secondary_database = '{DB Name}';

We have a 3.5 TB DB which is already involved in log shipping to two different server, as one will be in restore sate only and one will be in stand by mode which helps for reporting need. all source and destinations are sql 2005.

Make sure you have Instant File Initialization ON. This helps to drastically cut down the backup/restore times.

we took a differential backup to of primary (since we have to many log backups which run every 15 mins) and restored it and started log shipping. Here it fails by skipping the log file since it is looking for previous log file which is already shipped to other servers.

A differential or FULL backup does not break the log chain. Also, logshipping would not skip the files. There has to be something that might be taking ad-hoc log backup and then breaking logshipping.

I have set up a 3 way logshipping (dont know what to call .. so calling 3 way - one primary ships to 3 other standby servers) and it works without any issues.

Finally we stopped log backups and followed the same procedure it succeeded but it took 3 hours downtime which we don’t want to do since it is a prod server.

As I mentioned earlier, Instant File Initialization will help with the restoring of full backup - cutting down the restore time.

Setting up log-shipping won't involve a downtime on your primary server. It will only on your secondary server - if you are doing reporting and this is your case. It is by design, that during the restore - you cannot access the database.