1

I have two databases. One is production (A), the other is development (B).

I use to be able to restore database from a backup.BAK file of A in B by using SSMS > right clicking databases > restore database and using the appropriate settings.

Today I tried the following and in every situation I got the same error "Exclusive access could not be obtained because the database is in use"

  • Took database B offline (and selected option to drop all connections)
  • Tried with database B online and choosing option to drop active connections with risk of being left in single user mode (got stuck in single user mode and restore still failed with same error).
  • Tried with database B online and without choosing option to drop active connections.
  • Tried with database B online and with option to overwrite the existing database and without and with and without option to close existing connections to destination database.

I'm using SSMS 2017 and just looking for a simple way to restore database B with a copy of A. Anybody know a way to resolve?

0

I would try:

USE master;
GO
ALTER DATABASE B SET OFFLINE WITH NO_WAIT

Note this will make the database inaccessible but guess that doesn't matter if you are going to restore over the top of it anyway.

If that fails then:

USE master;
GO
ALTER DATABASE B SET OFFLINE WITH ROLLBACK IMMEDIATE

But if a big transaction is in progress then it could take a long time to rollback.

| improve this answer | |
0

Copy .bak file from your production to development environment and Restore a full database backup .bak file to development server.

Set DB in Single usermode and restore backup:

USE master
GO
ALTER DATABASE DB_Name
SET SINGLE_USER
--This rolls back all uncommitted transactions in the db.
WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE YourDB 
FROM DISK = 'D:BackUpYourBaackUpFile.bak' 
WITH REPLACE

Manually Restore an MSSQL Database in SSMS:

Log onto SQL Server Management Studio:

  • Navigate to the database you wish to restore.
  • Right Click > Tasks > Restore > Database.
  • Once on the General tab set the source to Device > Select Backup Device > Add > Navigate to the .bak file you're restoring to.
  • Ensure The Destination Database is the correct one.
  • Select the Options tab.
  • Check Overwrite the existing database (WITH REPLACE) and Close existing connections to destination database.
  • Uncheck Take Tail-Log Backup Before Restore.
  • Click OK.
| improve this answer | |
0

Thanks to CR241 for the hint about the tail-log. The following is what i ended up doing. This also works in the case where you want to create a different database based on a db backup:

  1. Right click “Databases” in the object explorer.
  2. Select "Restore database..."
  3. Click "Device" radio and then the browse button to select your backup file.
  4. MAKE SURE TO SET DATABASE NAME TO CORRECT TARGET NAME
  5. Click options
  6. Check “Overwrite the existing database (With Replace)
  7. Uncheck “Take tail-log backup before restore" (this is issue most of time, even if creating db based on a backup of a different db.
  8. Click ok.
| improve this answer | |
  • Was step 4 your problem? – RDFozz Aug 17 '18 at 22:16
  • No, i only highlighted step 4 because if you get that wrong and the backup was of a good db that has had changes since the last backup, you'll overwrite the production db with old data. – user158748 Aug 20 '18 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy