SQL Server Log Shipping: how to change a target database to stand by?

Question

I have added Log Shippig to SQL Server:

SQL server1\source_database->SQL server2\target_database

As I see from SSMs Report it works ok. But the target database always in "Restoring" state. I want to change it to have a read access to the target db and made all as described here

https://www.mssqltips.com/sqlservertip/3600/change-the-restore-mode-of-a-secondary-sql-server-database-in-log-shipping-with-ssms/

But the target database still in Restoring mode. What I did wrong? How to change the state and have a read access ?

Answer 1

There are two ways of doing:

1. Configure log shipping to use standby mode as default: (set restore_mode to 1 in msdb..log_shipping_secondary_databases on the target server)
2. Use restoring mode and manually set database to standby in the restore job

I very much prefer method #2, because it's god slow to restore every backup to standby mode if you have a lot of action in the database.

To manually use restore mode add a step in the SQL Agent Job after the restoring and run something like:

if exists(
select 1 from sys.databases where name = 'YOUR DATABASE NAME' and state_desc = 'RESTORING'
)
begin

restore database YOUR_DB_NAME with standby =  'D:\PATH_TO_STANDBY_FILE\YOUR_DB_NAME_standby.tuf'
end

With this method, you get faster restores and your db will be accessible as soon as the restore job is finished.