August 27, 2015 at 10:20 am
You cannot time the restores based on no current connections to the database because in the finite time between determining no connections and getting the restore job kicked off any number of logins could grab a connection to the database. There are ways around that, but a checker-job or script isn't it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 27, 2015 at 10:46 am
TheSQLGuru (8/27/2015)
You cannot time the restores based on no current connections to the database because in the finite time between determining no connections and getting the restore job kicked off any number of logins could grab a connection to the database. There are ways around that, but a checker-job or script isn't it.
My assumption is that the case usage of the read/only target server would be mostly for reporting purposes, having only a limited number of infrequent users who won't freak out if they occasionally get a dropped connection.
So, immediately after querying no active connections, the job could set the database to RESTRICTED_USER mode to keep it locked down while performing the restore, and then immediately set it back to MULTI_USER when the restore completes. I'm just talking off the top of my head and obviously the process would have to be tweaked to fit a specific usage scenario.
However, the following is a basic example of what I'm proposing. It would be part of a job scheduled to kick off every X minutes. I'd expect the target to get restored at irregular intervals, maybe skipping several cycles between, if that's suitable for a given scenario.
IF ( [confirmed no active connections] )
BEGIN;
ALTER DATABASE TargetDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE TargetDB ... ;
ALTER DATABASE TargetDB SET MULTI_USER;
END;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply