July 3, 2013 at 6:11 am
Even with setting the database to SINGLE_USER WITH ROLLBACK IMMEDIATE (on the line immediately prior to the restore command), I am getting these errors occasionally on nightly restore:
Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). Exact code is below. Really can't wrap my head around this, if it got to restore database command, then the single user command must've been successful, right? Could something else steal the single connection away from me while this is running?
ALTER DATABASE DbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [DbName] FROM
DISK = N'Z:\DbName.bak' WITH FILE = 1,
MOVE N'DbName' TO N'D:\SqlData\DbName.mdf',
MOVE N'DbName_log' TO N'D:\SqlData\DbName.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
ALTER DATABASE DbName SET RECOVERY SIMPLE;
ALTER DATABASE DbName SET MULTI_USER;
GO
July 3, 2013 at 7:01 am
Might be a silly question, are you connected to the database in question when running these commands?
July 3, 2013 at 7:11 am
Why
ALTER DATABASE DbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
....
this leaves one connection which block yours restore, try this
ALTER DATABASE DbName SET offline WITH ROLLBACK IMMEDIATE;
....
July 3, 2013 at 7:13 am
SQLSACT (7/3/2013)
Might be a silly question, are you connected to the database in question when running these commands?
I am using master database. But you raise a good point, for me to hold the single_user connection I have to be using the database, but for me to restore the database, I have to be using master. However the moment I switch databases, I lose the single_user connection which makes it available for a brief moment :unsure:
July 3, 2013 at 8:14 am
e4d4 (7/3/2013)
Why
ALTER DATABASE DbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;....
this leaves one connection which block yours restore, try this
ALTER DATABASE DbName SET offline WITH ROLLBACK IMMEDIATE;....
I swear I tried this before and it didn't work. But I'll give it a shot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply