Restores over an existing database fail when other sessions still use it. Three methods fix the restore failed error in SQL Server. Ending sessions with KILL and restricting access with SINGLE_USER are free and finish in seconds. Both need the right permission. Recovery software handles damaged backups. Restore from the master database and set MULTI_USER mode afterwards.
Your restore of HarborDB stops at night and SQL Server reports that exclusive access could not be obtained. Reports and a forgotten SSMS window still use the database.
Search results suggest a service restart. That step disconnects every database on the instance and not just HarborDB.
DBAs who must fix the SQL Server restore failed error get the safe order here. Stay tuned for the single user trap that hides in most scripts.
Microsoft states in the SSMS restore guide that restores can fail when active connections exist.
Why Does the Restore Failed Error Appear in SQL Server?
Three situations occupy the database:
- Open query windows. Your own SSMS windows connect to HarborDB and count as users.
- Application sessions. Services and reports connect with their own logins.
- Agent tools. Monitoring and backup agents open sessions as well.
Match your situation to a method.
| Situation | Fix | Method |
|---|---|---|
| You can name the sessions | End them with KILL | #1 |
| Sessions are many or unknown | Restrict access with SINGLE_USER | #2 |
| No sessions remain and the restore still fails | Damaged backup | #3 |
3 Methods to Fix SQL Database Restore Failed Error
Methods #1 and #2 need no purchase and work in seconds. Method #3 starts from the BAK file.
| Feature | End Sessions | Restrict Access | Recovery Software |
|---|---|---|---|
| Price | No fee | No fee | Scan without a fee and export with a license |
| Best for | A few known sessions | Busy servers | Damaged BAK files |
| Needs | ALTER ANY CONNECTION permission | ALTER permission on the database | Windows PC and the BAK copy |
| Main limit | Long rollbacks | Single user trap | Trial export limit |
Method #1: Find and End the Sessions With KILL (Free, Needs ALTER ANY CONNECTION)
List the sessions that use HarborDB first. Microsoft documents the columns in sys.dm_exec_sessions. They include session ID and login name and host name and program name and status.
SELECT session_id, login_name, host_name, program_name, status
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(N'HarborDB')
AND is_user_process = 1;Ask the owners to stop their work when they are known. End the rest with one statement per session ID.
KILL 53;Per the KILL reference KILL ends a user process by its session ID. KILL requires ALTER ANY CONNECTION permission and members of sysadmin and processadmin have it. Switch your own query window to master and start the restore.
Microsoft states that KILL cannot end your own process and cannot end system processes. Sessions with much work to undo take time to finish.
Method #2: Restrict Access With SINGLE_USER (Free, Needs ALTER Permission)
Many sessions or unknown sessions call for one statement. Per the ALTER DATABASE set options reference SINGLE_USER means only one user connects at a time. Its termination clause decides what happens to the other sessions.
USE master;
GO
ALTER DATABASE HarborDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE HarborDB
FROM DISK = N'D:\Backups\HarborDB_Full.bak'
WITH RECOVERY;
GO
ALTER DATABASE HarborDB SET MULTI_USER;
GOROLLBACK IMMEDIATE rolls back incomplete transactions at once. Without a termination clause the statement waits until every other user disconnects. ROLLBACK AFTER 60 SECONDS gives sessions time to finish and NO_WAIT fails the request when the switch cannot finish at once.
SSMS has the same action in the restore dialog. Select Close existing connections on the Options page. Per Microsoft the box sets single user mode before the restore and multi user mode afterwards.
Per Microsoft a database remains in SINGLE_USER mode when the session that set it signs out. Another login can take the only slot. Check the mode when a script stops before its last statement.
Method #3: Recover a Damaged Backup With EmailDoctor (Scan Free, Export Licensed)
Restores fail for a second reason when no session uses the database. Damaged BAK files are a common cause.
EmailDoctor SQL Backup Recovery scans the file and lists its tables without touching HarborDB. Product notes say a PC with no server instance can scan the file while the export needs a server login.
Copy the BAK file before you start. Product notes cap the trial at 25 records per table.
Scan the Damaged BAK Copy
- Double click the software icon on the desktop.
- Click Open and press Browse for the HarborDB_Full.bak copy.
- Tick the Auto detect SQL server file (.bak) version box.
- Press Recover and wait for the scan.
- Click a table on the left to preview its rows.
Send the Tables to SQL Server
- Select Export and choose SQL Server Database.
- Type the Server Name and choose the Authentication mode that matches your login.
- Select Create New Database and name it HarborDB_Recovered.
- Choose With Schema & Data and press Export.
- Open SSMS and refresh Databases to see HarborDB_Recovered.
Which Problems Appear During the Fix?
Five problems appear in most restore failed error fixes:
| Problem | Reason | Fix |
|---|---|---|
| Sessions list is empty or partial | The login lacks the view permission | Grant VIEW SERVER STATE on SQL Server 2019 and earlier |
| KILL takes long | A long transaction rolls back | Check progress with KILL session_id WITH STATUSONLY |
| KILL is refused | The ID is your own session or a system process | End only user sessions |
| ALTER DATABASE waits | No termination clause exists and sessions remain | Add WITH ROLLBACK IMMEDIATE |
| Another login takes the only slot | The script stopped before MULTI_USER | Set MULTI_USER and check the mode |
Microsoft names VIEW SERVER PERFORMANCE STATE for SQL Server 2022 and later. Switch to Method #3 when every fix fails and work on a duplicate.
Frequently Asked Questions
Must Other Sessions End Before a Restore?
Yes. Per Microsoft restores over an existing database can fail while other sessions use it. End the sessions or restrict access first.
Can KILL End My Own Session or a System Process?
No. Microsoft states that you cannot kill your own process and that system processes cannot be ended.
Can KILL Take a Long Time to Finish?
Yes. Per Microsoft a rollback of much work can take time. STATUSONLY reports rollback progress for a session that is already ending.
Does a Restore Failed Error Mean a Damaged Backup?
No. Open sessions on the database cause most restore failed errors of this type. Damaged BAK files give other messages.
Does KILL Need Special Permission?
Yes. Microsoft requires the ALTER ANY CONNECTION right and includes it in sysadmin and processadmin membership.
Does SINGLE_USER Mode End When I Sign Out?
No. Microsoft states that the database remains in SINGLE_USER mode after the session that set it signs out.
Does ALTER DATABASE Wait Without a Termination Clause?
Yes. Per Microsoft the statement waits until every other user disconnects.
Does the Sessions View Show Every Session?
Yes with permission. Microsoft names VIEW SERVER STATE for SQL Server 2019 and earlier and VIEW SERVER PERFORMANCE STATE for SQL Server 2022 and later.
Can Recovery Software Restore a Damaged Backup?
Yes. Recovery software opens the BAK file and sends its tables to a new database. Compare the preview with your expectations first.
Is the Trial Export Limited?
Yes. Product notes cap the trial at 25 records per table. Full licenses export every record.
Author’s Verdict
Restore failed errors of this kind mean other sessions use the database. Find them first and end only the sessions you can name.
Use SINGLE_USER with ROLLBACK IMMEDIATE on busy servers and set MULTI_USER at the end. Recovery software saves the data when the backup is damaged.
Restarting the service remains the last resort because it disconnects every database.
- Restores over an existing database fail while other sessions use them.
- KILL ends named sessions and requires ALTER ANY CONNECTION permission.
- SINGLE_USER with ROLLBACK IMMEDIATE restricts access on busy servers and needs a MULTI_USER statement when a script stops early.
- Close existing connections does the same in the SSMS dialog.
- Restore failed error messages end once the sessions end.
- Recovery software saves the tables of a damaged BAK file in a fresh database.
- Check every statement on a duplicate first.
Next Read: Restore a SQL Database Without the Transaction Log File
Also Read: Restore a SQL Database From a BAK File | Restore a SQL Database With a Different Name
