January 13, 2025 at 2:05 pm
I have made an empty DB called "HJEM_BB"
I try to fill it from a backup (From device)
When i select the backup, it opens nicely.
Sets it to overwrite, some time goes, and i get this message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore of database 'hjem_bb' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
------------------------------
ADDITIONAL INFORMATION:
Microsoft.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.200.48053.0&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Then i tried to make a new db with another name. Same result.
What to do?
Best Regards
Edvard Korsbæk
Version info:
SQL Server Management Studio 19.3.4.0
SQL Server Management Objects (SMO) 16.200.48053.0+08fe64c9e8eb5ff3c7ea5787f145e9ecb3d57df8
Microsoft T-SQL Parser 17.0.27.0+b6df00d03710e3fafcbe827aad08bdbe9d45d1ab
Microsoft Analysis Services Client Tools 16.0.20054.0
Microsoft Data Access Components (MDAC) 10.0.22621.4317
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.22631
January 13, 2025 at 2:26 pm
There can be no other connections to the database when you run a restore. That includes SSMS. So, if you have that database open in front of you and you try to restore it, it'll fail. Make sure all connections are removed and then restore the database.
"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
January 13, 2025 at 3:48 pm
I tried to run:
Use Master
Go
Declare @dbname sysname
Set @dbname = 'hjem_bb'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End
Runs without problems, but the DB is still in use.
tried too:
ALTER DATABASE hjem_bb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE hjem_bb SET MULTI_USER
go
none worked
January 13, 2025 at 3:51 pm
There's a connection to the database. That's what that error is telling you. I'll bet money it's SSMS. However, to find out, just open a query window, not connected to the database in question, and run sp_who2. You can see all the connections and figure out where it's coming from on your database.
"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
January 13, 2025 at 6:00 pm
If you cannot figure out who is connecting - you could just take the database offline and then perform the restore.
ALTER DATABASE hjem_bb SET OFFLINE WITH ROLLBACK IMMEDIATE;
At that point, just restore the database from your backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2025 at 6:26 pm
Thanks for your offline idea, and for sp_who2.
It was "Something" from Redgate that caused the trouble. I have uninstalled, and used your trick, and the db is up and running.
Best regards
Edvard Korsbæk
January 17, 2025 at 3:28 pm
Thanks for your offline idea, and for sp_who2.
It was "Something" from Redgate that caused the trouble. I have uninstalled, and used your trick, and the db is up and running.
Best regards
Edvard Korsbæk
stop the process, restore and restart the process not an option then?
uninstalling is a bit over the top
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 4, 2025 at 11:25 am
This was removed by the editor as SPAM
February 11, 2025 at 7:26 am
It helped me, Thank you so much for the info.
February 11, 2025 at 2:05 pm
Why create or use the empty database to start with? Why not just define the new database name in the restore, avoiding any risks of database being in use by other sessions?
February 12, 2025 at 6:56 am
"stop the process, restore and restart the process not an option then?
uninstalling is a bit over the top"
I bought SQK PROMPT from Redgate.
You get the whole SQLBELT Tool as install, and I installede the lot.
one of them was the culprit, and i had no idea of which.
the real trick was to use:
ALTER DATABASE hjem_bb SET OFFLINE WITH ROLLBACK IMMEDIATE;
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply