I'm apparently very late to the game here - I intermittently (reluctantly) end up becoming someone's DBA and used to use dbforums.com all the time - great community, lots of great help provided. Looks like that ship sailed a couple years back! Anybody know what happened to them? That crap site in place of it is kind of a joke. To replace all that great knowledge with another newsfeed is really a shame...
Anyhoo, I'm trying to improve a simple DB restore script that intermittently fails with "Database in use". Some nights it runs no problem. Other nights, not so much.
Here's the script:
ALTER DATABASE nfache1
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
go
RESTORE DATABASE nfache1nrt
FROM DISK = 'C:\Temp_NRT\nfache1NRT.BAK'
WITH MOVE 'Sneferu_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.NRTDB\MSSQL\DATA\nfache1nrt.mdf' ,
MOVE 'Sneferu_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.NRTDB\MSSQL\DATA\nfache1nrt_log.ldf' , replace
go
-- set back to multi
ALTER DATABASE nfache1 SET MULTI_USER
go
Super simple 2016 SQL Server environment, only hosting three databases.
The "workaround" that I have is to just manually restart the SQL server and the run the restore script: Voila! It works.
I'd rather approach it programmatically by adding something to the above script rather than running a separate schedule system task to complete the SQL restart.
Any suggestions from you pros?
Thanks in advance 🙂 Glad to be here...
BTW, I couldn't help it - "Avatar" for my avatar! I'm sure that's the oldest pun in the book but hey...
Simply, when the restore is attempted, there are open connections to the database.
It appears you are attempting to close the connections to the database by setting it to single user, which should work fine.
However, if you look at your code, you are setting the database named "nfache1" to single user, and then you are attempting to restore the database named "nfache1nrt".
It appears you need to set the database named "nfache1nrt" to single user.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 21, 2019 at 5:52 pm
Super helpful - I'm going to adjust that and try it out tonight. As you can see, I shouldn't be allowed to manage other folks' SQL environments lol 😉
This might have been a super easy one - I will let you know the results.
Many thanks for taking the time to help out, Michael.
October 21, 2019 at 6:05 pm
I tend to use this instead:
ALTER DATABASE [mydbname] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
this would prevent any non SYSADMIN type users from getting in before the restore properly initiates.
October 21, 2019 at 6:14 pm
OK - incorporating both Chris and Michael's suggested edits. How does this look:
ALTER DATABASE nfache1nrt
SET RESTRICTED_USER WITH
ROLLBACK IMMEDIATE
go
RESTORE DATABASE nfache1nrt
FROM DISK = 'C:\Temp_NRT\nfache1NRT.BAK'
WITH MOVE 'Sneferu_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.NRTDB\MSSQL\DATA\nfache1nrt.mdf' ,
MOVE 'Sneferu_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.NRTDB\MSSQL\DATA\nfache1nrt_log.ldf' , replace
go
-- set back to multi
ALTER DATABASE nfache1nrt SET MULTI_USER
go
I'm assuming that the second to last line will set the database back to multi user so everybody can get back into it?
Thanks!
October 21, 2019 at 6:51 pm
The second to last line will not matter, unless the database was set in that manner prior to the backup you are now restoring.
Restoring the backup will re-create the setting as they were when the backup was taken.
If it's already multi-user after the restore, this code will not really do any harm. It will, however, kill all the connections to the database.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 21, 2019 at 9:27 pm
OK - Thanks for the clarification. That makes sense to me.
I'll check in the AM to see how it goes. Thank you, gentlemen!
October 21, 2019 at 9:38 pm
If it's DEV or QA then probably you can delete the db and then restore it.
October 22, 2019 at 9:53 am
RESTRICTED_USER Access Mode
In RESTRICTED_USER access mode, only the users who have db_owner or db_creator permission can access. Users who belong to the sysadmin fixed server role can also access the database which is in RESTRICTED_USER access mode.
SQL Database Recovery Expert 🙂
November 7, 2019 at 3:14 pm
I wanted to let all know that the suggested changes worked perfectly. I have had no issues with the restore since implementing. MANY thanks to Michael and Chris for their assistance. While my interactions may be few, I'm grateful for your expertise 🙂
November 13, 2019 at 4:56 pm
Spoke too soon!
Last night, I encountered another case of "Exclusive access could not be obtained because the database is un use. [SQLSTATE 42000] (Error 3101).
I read up more on the forum and wanted to see if the following change makes sense.
I changed the following:
ALTER DATABASE nfache1nrt
SET RESTRICTED_USER WITH
ROLLBACK IMMEDIATE
go
with
USE MASTER
ALTER DATABASE nfache1nrt
SET OFFLINE WITH
ROLLBACK IMMEDIATE
go
Finally, can anyone confirm that while I'm initially taking the database offline, the following command will bring it back online at the same time as making it multi user?
-- set back to multi
ALTER DATABASE nfache1nrt SET MULTI_USER
go
Thanks in advance...
November 15, 2019 at 8:34 pm
No need to reply, as the proof is in the pudding! The updated process works like a charm (until it doesn't).
Thanks again
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply