0

I'm practicing a SharePoint 2010 to 2013 migration and having trouble restoring a database. After backing up a database on a SP2010/SQL 2008 server, I'm trying to restore it to a database I've freshly created on my SP2013/SQL 2012 server.

Though the UI, I've taken the destination database offline. I select the .bak file, select my destination database, and attempt the restoration. I get an error: Exclusive access could not be obtained because the database is in use.

Why is this happening? I've taken the database offline, I don't see how it could possibly be in use.

In the restoration window, I've tried going to Options and selecting Overwrite the existing database (WITH REPLACE) as well as Close existing connections to destination database.

Still the same error message. How do I get past this error?

1
  • Call me stupid but why would you restore the database to an existing database? Why not restore it to a new database?
    – joeqwerty
    Jan 8, 2014 at 22:27

2 Answers 2

4

Are you the person who is currently using the database? :)

Try running this before you do the restore:

USE master
Go
ALTER DATABASE YourDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

That should kick everyone off. If not, you're definitely the person currently using the database. Close any SSMS windows using the database and run:

ALTER DATABASE YourDB
SET offline
9
  • I am unquestionably the only person to be using the server and the database. I have SSMS open just to open the restoration window. Also, my database is already offline. Anyway, the issue persists after using your script to set it to Single User.
    – tnw
    Jan 8, 2014 at 21:04
  • I ran through the commands on a DB of my own to double-check, and until I closed the SSMS window where I'd accessed my test DB, the restore failed as "in use." This session didn't show up in sp_WhoIsActive, sp_who, or sp_who2, but I was still blocked. Bring the DB back online and close all SSMS windows and try again and see if that does the trick for you. Jan 8, 2014 at 21:16
  • Thanks for your quick response! I'm unsure how I can restore the database without using SSMS...
    – tnw
    Jan 8, 2014 at 21:26
  • Close any open windows you have and create a new one that's not connected to that database. (It's not your default login database, is it?) Then run the restore. Jan 8, 2014 at 21:32
  • I'm unclear on your instructions. I've tried opening up the restoration window and disconnecting from the server in SSMS then running the restore... still the same issue.
    – tnw
    Jan 8, 2014 at 21:39
0

A number of issues here and things I didn't mention which were part of the problem. This was a database backup taken from a SQL 2008 instance and being restored on a SQL 2012 instance. This bit of T-SQL finally did it:

RESTORE DATABASE [WSS_Content_Migrated] 
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\Backup\WSS_Content.bak' WITH 
MOVE 'WSS_Content' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\Backup\WSS_Content.mdf',
MOVE 'WSS_Content_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\Backup\WSS_Content.ldf'
, REPLACE

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .