About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
Error 3101, "Exclusive access could not be obtained because the database is in use", means at least one other session still has the target database open, so the restore cannot take the exclusive access it needs. A typical culprit is an application, report, job or open query window connected to the database you are overwriting. The safe fix is to identify those sessions, take the database to single-user mode in a controlled way, restore, and put it back to multi-user.
What the error means
Microsoft lists error 3101 in its Database Engine error reference for the 3000 range as severity 16 with the message "Exclusive access could not be obtained because the database is in use." The SSMS restore page (page metadata date 2023-12-08) shows the same message, as raised through SQL Server Management Objects (SMO), when you restore over an existing database and do not tick Close existing connections to destination database. It says: "Restore operations may fail if there are active connections to the database."
The documentation for the RESTORE statement (page metadata date 2023-05-18) says something that looks like the opposite: "During an offline restore, if the specified database is in use, RESTORE forces the users off after a short delay." Both statements are on Microsoft's pages, and Microsoft does not explain how they fit together. In practice the SSMS page shows the restore failing with 3101, so treat it as a real condition and prove which sessions are involved on your own instance before you act. See "Risks and limits".
Two terms matter for the fix, both from the ALTER DATABASE SET options page (page metadata date 2026-06-29):
SINGLE_USERallows one user at a time. If other users are connected, theALTER DATABASEstatement is blocked until they disconnect, unless a termination clause overrides this. The database stays in single-user mode even if the user who set it signs out, and then "a different user, but only one, can connect".RESTRICTED_USERallows only members ofdb_owner,dbcreatorandsysadmin, with no limit on their number.
The termination clause decides when incomplete transactions are rolled back: ROLLBACK AFTER n SECONDS, ROLLBACK IMMEDIATE, or NO_WAIT. If the clause is omitted, "the ALTER DATABASE statement waits indefinitely if there's any lock on the database."
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| Applications or services still connected | Sessions with the database as current database, with a recent last_request_end_time |
Query sys.dm_exec_sessions joined to sys.databases (below) and read program_name and host_name |
| Your own query window is using the database | One of the sessions is your own, or a colleague's, in SSMS | Check program_name and login_name against your own connection |
| Open transaction held by an idle session | Session status sleeping with open_transaction_count above 0 |
The same query; the column is documented on the sessions page |
| A long-running request in the database | Session status running |
sys.dm_exec_requests for that session_id |
| Database left in single-user mode by an earlier step | user_access_desc is SINGLE_USER in sys.databases, and one session holds the slot |
sys.databases, then identify the one session |
| A restore already running or half-finished against the same name | A restore request in sys.dm_exec_requests |
command and percent_complete (documented for RESTORE DATABASE) |
Prove the cause before changing anything
These queries use column names documented in the Microsoft pages cited, and none has been run or lab-tested. They are read-only.
- See who is using the database. The
database_idcolumn of sys.dm_exec_sessions (page metadata date 2026-11-02) is the "ID of the current database for each session". On SQL Server 2019 and earlier you needVIEW SERVER STATEto see all sessions, and on 2022 and laterVIEW SERVER PERFORMANCE STATE:
SELECT s.session_id, s.login_name, s.host_name, s.program_name,
s.status, s.open_transaction_count, s.last_request_end_time
FROM sys.dm_exec_sessions AS s
JOIN sys.databases AS d ON d.database_id = s.database_id
WHERE d.name = N'Sales'
AND s.session_id <> @@SPID;
- Check the access mode of the database. sys.databases (page metadata date 2026-04-27) returns
user_access_desc(MULTI_USER,SINGLE_USERorRESTRICTED_USER) andstate_desc:
SELECT name, state_desc, user_access_desc
FROM sys.databases
WHERE name = N'Sales';
- For a session that is running, look at what it is doing in sys.dm_exec_requests (columns
session_id,command,blocking_session_id,percent_complete).
One limit: database_id is only the session's current database. A session whose current database is master but which is querying Sales through a three-part name will not appear in query 1. If query 1 returns nothing and the error persists, the lock-level views are the next place to look.
Fix
Choose the least destructive option that fits. Before any option that disconnects people, tell the application owner. Rolled-back transactions are lost.
Option 1: SSMS. On the Options page of the Restore dialog, tick Close existing connections to destination database. Per the SSMS page, this "sets the database to single user mode before the restore operations, and sets the database to multi-user mode when complete". The same page shows 3101 as the result of not ticking it.
Option 2: T-SQL, single-user with rollback. Take a tail-log backup first if the database is online, uses the full or bulk-logged recovery model, and you need the work since the last log backup. The RESTORE page says restoring without it "results in an error" unless REPLACE or STOPAT is used. The Microsoft example for the access change is:
USE master;
GO
ALTER DATABASE [Sales]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- run the RESTORE here, from the same session if you can
ROLLBACK IMMEDIATE disconnects the other sessions and rolls back their open transactions, so their uncommitted work is discarded. ROLLBACK AFTER 30 SECONDS gives short transactions time to finish. Because the database stays single-user after you sign out, and "a different user, but only one" can then connect, run the restore from the same session straight away. Applications that reconnect automatically can take the single slot, so a practical step is to pause them first.
After the restore, set the access mode back and check it:
ALTER DATABASE [Sales] SET MULTI_USER;
Then run the sys.databases query above and confirm user_access_desc is MULTI_USER. A database left in SINGLE_USER blocks every other user, so do not skip this step.
Option 3: end specific sessions. The KILL page (page metadata date 2025-07-09) says KILL requires ALTER ANY CONNECTION (included in sysadmin and processadmin), you cannot kill your own session, and a session with much to undo can take a long time. Use KILL <session_id> WITH STATUSONLY to read rollback progress. Microsoft warns that re-running KILL risks hitting a new process that reuses the session ID. Never kill system processes.
Do not add REPLACE to get past 3101. It is a different control. Per the RESTORE arguments page (page metadata date 2022-05-10), REPLACE deletes the existing database and overrides the safety check against overwriting a different database and the requirement to back up the tail of the log, so committed work can be lost. REPLACE does not release other sessions.
Example (illustrative). On SQL01, a restore of Sales from \\FS01\SQLBackups\Sales_full.bak fails with 3101. The sessions query returns session 61 (login_name CONTOSO\svc-report, host_name APP01, program_name ReportingApp, status sleeping) and session 74, an SSMS query window belonging to the administrator's colleague. After the application owner pauses the reporting service, the administrator takes a tail-log backup, sets SINGLE_USER WITH ROLLBACK IMMEDIATE, restores, sets MULTI_USER, and re-enables the service.
Confirm the fix
sys.databasesshowsstate_descONLINEanduser_access_descMULTI_USER.- The restore is recorded in
msdb. The restorehistory table (page metadata date 2017-03-03) holds one row per restore withrestore_type(D = database, L = log),recovery(1 = RECOVERY, 0 = NORECOVERY) andreplace(1 if REPLACE was used). Rows exist only on the instance that ran the restore and are pruned bysp_delete_backuphistory. - The application owner runs a real transaction and confirms the data matches the restore point. A restore that completes does not prove the right data came back.
What to watch afterwards
- Schedule restores for a window in which the application is stopped, rather than relying on disconnecting sessions at the last moment.
- Alert on any database whose
user_access_descis notMULTI_USERoutside a maintenance window. - If the same sessions reappear every time, find which service reconnects and give it a maintenance state.
- Update the restore runbook so that the tail-log backup step is explicit.
Read next
Related articles you may want next.
- Backup & RecoverySQL Failure Before the Last Log Backup: Is a Tail-Log Backup Possible?Decide whether to preserve the log tail, stop writes and escalate before any restore overwrites recovery evidence.Read article
- Backup & RecoverySQL Point-in-Time Restore: Plan and Verify the Backup ChainCheck native SQL backup chains, select a recovery point and rehearse a separate restore without confusing readable backups with recovered service.Read article
- TroubleshootingThe Database Restored, but the Application Cannot Log InTrace SQL logins, database users and SID mappings after a restore, and validate application access without broadening application privileges.Read article
Risks and limits
- These queries and statements have not been run or lab-tested. Test them on a non-production instance first.
- Conflicting guidance: the RESTORE page says an offline restore forces users off after a short delay, while error 3101 and the SSMS page show restores failing for exclusive access. Microsoft does not reconcile the two, so test on your own instance.
ROLLBACK IMMEDIATEandKILLdiscard uncommitted work. A restore over an existing database discards everything after the restore point. A tail-log backup is the protection.- Always On, log shipping and mirroring databases have additional rules not covered here.
Sources
- Database Engine events and errors, 3,000 to 3,999 — Microsoft Learn, date not shown, accessed 16 November 2026
- Restore a Database Backup Using SSMS — Microsoft Learn, page metadata date 2023-12-08, accessed 16 November 2026
- RESTORE (Transact-SQL) — Microsoft Learn, page metadata date 2023-05-18, accessed 16 November 2026
- RESTORE Arguments (Transact-SQL) — Microsoft Learn, page metadata date 2022-05-10, accessed 16 November 2026
- ALTER DATABASE SET Options (Transact-SQL) — Microsoft Learn, page metadata date 2026-06-29, accessed 16 November 2026
- sys.dm_exec_sessions — Microsoft Learn, page metadata date 2026-11-02, accessed 16 November 2026
- sys.dm_exec_requests — Microsoft Learn, page metadata date 2026-11-02, accessed 16 November 2026
- sys.databases — Microsoft Learn, page metadata date 2026-04-27, accessed 16 November 2026
- KILL (Transact-SQL) — Microsoft Learn, page metadata date 2025-07-09, accessed 16 November 2026
- restorehistory (Transact-SQL) — Microsoft Learn, page metadata date 2017-03-03, accessed 16 November 2026
This article was originally published on .