Cannot restore new DB

  • 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

  • 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

  • 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

     

  • 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

  • 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

  • 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

  • Edvard Korsbæk wrote:

    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" 😉

  • This was removed by the editor as SPAM

  • It helped me, Thank you so much for the info.

  • 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?

  • "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