Unable to restore MSSQL database: Exclusive access could not be obtained because the database is in use

Created:

2017-02-10 20:26:20 UTC

Modified:

2017-08-15 10:13:02 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Unable to restore MSSQL database: Exclusive access could not be obtained because the database is in use

Applicable to:

  • Plesk Onyx for Windows

Symptoms

Unable to restore MSSQL database from a valid backup.

Restoration from Plesk UI fails with error:

The following objects were not found in the backup file: example_database:mssql:.\MSSQLSERVER2016::example.com

Restoration from CLI fails with error:

Failed deployment of database example_database (domain example.com) 
Unable to restore MS-SQL database 'example_database' to MS-SQL server '.\MSSQLSERVER2016'. Error info: 
Exclusive access could not be obtained because the database is in use.

Cause

Database is in use.

Resolution

1. Unpack database dump from backup file like C:\Program Files (x86)\Plesk\Backup\example.com\databases\example_database_2\backup_dbdump_1702092100.zip

2. Restore database manually from MSSQL Management Studio by running the following query:

USE [master]
GO 
-- section below kills all active connections to database
DECLARE @kill varchar(8000) = ''; 
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' 
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('example_database')
EXEC(@kill);
-- section below switches database to single-user mode in order to prevent any connection during restoration
ALTER DATABASE [example_database] SET Single_User WITH Rollback Immediate
GO
-- section below restores database from the specified backup file
RESTORE DATABASE [example_database] FROM DISK = C:\Program Files (x86)\Plesk\Backup\example.com\databases\example_database_2\tmp141.tmp'
WITH REPLACE
GO
-- section below switches database to multi-user mode
ALTER DATABASE [example_database] SET Multi_User
GO
Have more questions? Submit a request
Please sign in to leave a comment.