I've set up log shipping using standby mode (The "Disconnect users in the database when restoring backups" is unchecked).
I tested running the following code on secondary server/database.
declare @a int
while 1=1
begin
select @a = count(*) from ....
end
However, the code stops with the following error when log shipping restore job start to run.
Database cannot be opened. It is in the middle of a restore.
How to avoid the issue?
Best Answer
This is kind of how log shipping has to work. You need exclusive access to a database to apply a log backup, which means your script has to set the database to single_user or otherwise evict all the users before it can apply the log backup(s).
Some workarounds: