If you are using Log Shipping, there have probably been times that you have discovered that the Log Shipping Secondary is not up to date. Perhaps you found out by right clicking on your SQL Server instance and running the performance dashboard report for Log Shipping Status. If you are not familiar with this report check here.
Perhaps there is a Reporting team that uses that secondary and they met you at your desk upon arrival, informing you that the database is not online so they can’t work yet. So what do you do when you realize that the secondary is so far behind that it is probably not going to catch up on its own? This process assumes that you have a Log Shipping config set up to run a Log Shipping Restore job just prior to the beginning of the business day and that job leaves the database in Standby mode so it can be read from. At the end of the business day the log shipping restore job has one or more schedules set to kick off restores for the transaction logs during the business day.
Here is a process you can use.
1. Disable the Copy Job on the secondary that is behind.
2. View the previously mentioned Log Shipping Status report and determine which file is the next in line to be restored.
3. Create a folder in the path where the transaction log backups are being copied to and perform a Cut operation on all the files just after the current file being restored.
4. Paste those files into the folder you made in step 3.
Here is what you have accomplished with this. You have set up the database to go to Read Only/Standby once the current file is done restoring. It can’t continue the restore process because it has reached the last transaction log file in the original folder path. If the Reporting team can live with the data as is, then they can get to work while you set up the next part that will allow you to catch it up. If it still is not usable for the Reporting team because the data has to be more current, then you have still helped yourself because you have to get the database out of recovery mode so you can do the next part to catch it up.
5. Take a differential backup of the current database.
6. While that is running, open SQL Server Management Studio to set up your script to restore the most recent full backup and the differential you are making. Remember to consider that you may have to use the WITH MOVE part of the RESTORE database syntax because the location of your files on the secondary may not be the same as on your primary. Also, if your Log Shipping config is designed to leave the database in Standby then you will need to use the WITH STANDBY clause and then name the .tuf file where the redo information is kept.
7. If the Reporting team started querying after step 3 you will need to ask them to disconnect because you can’t restore a database while other sessions are connected. Alternatively you could use ALTER MYDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE at the beginning of your RESTORE script but that would be inconsiderate.
8. Once the secondary is done restoring the most recent transaction log it was on, start the restore scripts you built in step 6. Once this step is done, you can inform the Reporting team that the database is ready to be queried.
9. Cut and paste the files from steps 3 and 4 so they are back in the target folder for the copy job.
10. Re-enable the Copy job. Once step 8 is done, you can inform the Reporting team that the database is ready to be queried.