April 24, 2009 at 5:38 am
Interested to know views on this:
An SQL job will restore the database at a specified time (weekly). This db shouldn't be in use as it's test and will be restored before the 9am Monday start. However, you know that someone will have left their PC on and connected from Friday :angry:, the job will fail and Monday will start badly. :doze:
RESTORE DATABASE MyTestDb
FROM DISK = 'C:\MyProductionDb.bak'
WITH REPLACE,
MOVE 'MyTestDb_Data' TO 'D:\MSSQL\data\MyTestDb.mdf',
MOVE 'MyTestDb_Log' TO 'E:\MSSQL\data\MyTestDb.ldf'
So, do I preceed the restore script with some clever KILL SPID script?
Or
Do I put
ALTER DATABASE MyTestDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
April 24, 2009 at 6:03 am
that is best option, I do the same thing...
🙂
Rajesh Kasturi
April 24, 2009 at 6:21 am
Take it offline. If you make the DB single user there's a chance that someone will grab that single connection before the restore starts.
ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE
Then you can restore confident that no one can be using it at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2009 at 6:32 am
Yes...That is TRUE..
GilaMonster is right...
Rajesh Kasturi
April 24, 2009 at 8:03 am
I just kill any processes in that happen to be in the database before I execute the restore.
cursor loop through sys.sysprocesses where dbid = {yourdbid} and execute a dynamic kill command for any processes that you find.
The probability of survival is inversely proportional to the angle of arrival.
April 24, 2009 at 8:27 am
FNS (4/24/2009)
Interested to know views on this:An SQL job will restore the database at a specified time (weekly). This db shouldn't be in use as it's test and will be restored before the 9am Monday start. However, you know that someone will have left their PC on and connected from Friday :angry:, the job will fail and Monday will start badly. :doze:
RESTORE DATABASE MyTestDb
FROM DISK = 'C:\MyProductionDb.bak'
WITH REPLACE,
MOVE 'MyTestDb_Data' TO 'D:\MSSQL\data\MyTestDb.mdf',
MOVE 'MyTestDb_Log' TO 'E:\MSSQL\data\MyTestDb.ldf'
So, do I preceed the restore script with some clever KILL SPID script?
Or
Do I put
ALTER DATABASE MyTestDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
We've got pretty tight control over who can be connected to the server, so instead of SINGLE_USER, which as Gail has pointed out, can be slipped past, we use RESTRICTED_USER. Arrives at the same place.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2009 at 8:28 am
Thanks all for your suggestions.
Since this is only a copy of the live db for the developers to run their dev code against I've gone with the off-line approach. If the job fails half way through and the restore copy is unusable all that will happen is I'll need to manually run the restore as soon as I get in. 😎
This would only be a 20-30 minute delay and I'm usually in before them anyway. 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply