June 24, 2020 at 4:21 pm
Hi all,
New guy on the block please be patient if Q's are dumb!!!
When I try to restore a DB it keeps giving me an error that the DB is in use.
I have closed the DB and even then I get the error. So I will have to close SSMS and restart for it to work.
What am I doing wrong.
Thanks
Paps
June 24, 2020 at 4:46 pm
If there is an application that uses this database, then it's likely trying to connect over and over. Closing it will clear the exiting connections.
If you are in SSMS, you need to be in the master database.
If you are using the GUI to do a restore, the one of the options is to close existing connections. If you are restoring by using a T-SQL command, then add this to the top of the script:
USE master;
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
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/
June 24, 2020 at 5:55 pm
Thanks
June 25, 2020 at 11:52 am
If you're by yourself on the server and you've recently had the database open in the Object Explorer in SSMS, say looking at tables or something, in addition to changing your connection to another database, make sure that in Object Explorer you open a table list or something in the other database. I've seen SSMS maintain that other connection until told to do something else. Setting restricted use might not help since you have restricted access.
Also, if this is a production system, be darned sure you're on the right database before you kick everyone out with ROLLBACK IMMEDIATE. It's not lying. It'll rollback all transactions, kick out everyone but restricted users. Many people have accidently brought down production systems because they did this in the wrong spot.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply