Background
When performing a full restore over an existing database, SQL Backup sometime report that the database cannot be restored because it is currently in use, like shown below
This error shows not only during restore backup, also for drop database or alter database as well.
Workaround
In this situation database administrator needs to disconnect all the connected users from a SQL Server Database to get exclusive access of the Database.In the situation where a full restore is blocked because users are currently connected to the database, the best solution to this problem is to take the database offline prior to running the restore, as this is the easiest way to kill all connections to the database.
Before restore attempt
Database administrator can execute the below mentioned TSQL command to get a Single User access to a database. SINGLE_USER WITH ROLLBACK IMMEDIATE command is used only one user can connect to the database at a time.
use master
alter database DB_NAME set offline with rollback immediate;
After restore
Database administrator can execute the below mentioned TSQL command to give Multiple User access to a database. MULTI_USER command is used any number of users who have rights to connect to the database will be able to connect to the database.
use master
alter database DB_NAME
set online with rollback immediate;
So now all authenticated database user can connect to the database again.
Hi there! I know this is kinda off topic nevertheless I’d figured I’d ask.
Would you be interested in exchanging links or maybe guest authoring a
blog article or vice-versa? My blog discusses a lot
of the same topics as yours and I think we could greatly benefit from
each other. If you happen to be interested feel free to
shoot me an e-mail. I look forward to hearing from you!
Excellent blog by the way!
yes will love to, email me your planning
You really make it seem so easy with your presentation but I find this matter to be actually something that I think I would never understand.
It seems too complicated and very broad for me. I’m looking forward for your next post, I will try to get the hang of it!
After exploring a few of the blog posts on your blog, I truly like your technique of writing a blog.
I bookmarked it to my bookmark website list and will be checking back in the near
future. Take a look at my website too and tell me your opinion.
Nice stuff, thanks for posting!
Saved me a lot of headache and time: When you do this very same thing from the UI it takes ages, no idea why.
thanks, saved me too 🙂
In my case, the .BAK file was corrupt. I repair my .BAK file by Stellar Phoenix SQL Backup Recovery software.
https://www.stellarinfo.com/database-recovery/sql-backup.php
Thanks, your info help me from die…^^