Log Shipping With dbatools – Part 4: Recover a Log Shipped Database
In the third part of the series we discussed the way to get the log shipping errors using the command “Get-DbaLogShippingError”.
This blog will be about how to recover a log shipped database.
Out-of-the-box solutions
I can be very short in this paragraph, there is no out-of-the-box solution to recover a log shipped database.
Why this command
Initially, log shipping was meant to be used as a warm standby. You have your data on another instance but you still have some human intervention to get it all back up. Imagine the following situation. You have set up log shipping using either the GUI or the commands in dbatools.
You have about 15 databases and everything is working fine. Until one day the primary instance goes down and is not recoverable.
For the production to continue you have to bring the log shipped databases online fast. You have to figure out what the last transaction log backup was. You have to check if it was copied to the secondary instance and if it’s restored.
To do this by running a couple of queries, copying the files if needed, and running the log shipping jobs takes time. I’d rather run a command and recover one or more databases and get back to the problem of the primary instance.
Invoke-DbaLogShippingRecovery
The Invoke-DbaLogShippingRecovery command it will execute the following steps:
- Check the agent status and start the agent if it’s not a started state.
- Retrieve the latest transaction log backup and try to copy it to the secondary instance if it’s not present. It will wait and check the log shipping status to see if the backup file is copied.
- Retrieve the last restored transaction log backup. Execute the restore process to get the database up-to-date.
- Disable the jobs after the copy and restore process.
- After all the actions it restores the database to a normal state.
To execute the command
| |
The result of the command
The image below shows the database in a normal state after the command in the SQL Server Management Studio.
The result of the jobs being disabled
More options
In my example, I showed how I recovered a single database. The parameter does accept multiple databases.
| |
Besides setting the individual databases you can also let the command recover all the log-shipped databases
| |
In some cases, you want to recover the databases but not execute the recovery to a normal state
| |
This concludes the command “Invoke-DbaLogShippingRecovery”. This was the final post in this series. If you want to look back at the other command follow the links below: