Introduction:

Have you ever tried to restore a SQL Server database from disk using the WITH REPLACE option in single user mode, only to have it get stuck in the “Restoring…” state for a long time? This can be a frustrating experience, especially if you’re under pressure to get the database back online quickly. In this article, I’ll share some common reasons why this may happen based on my own trials and tribulations, and provide some tips to help you troubleshoot and resolve the issue. By the end, you’ll have a better understanding of what might be causing the hang and how to get your database restored successfully.

Key reasons for restore getting stuck:

1. Pending transactions in the database being replaced: If there are active transactions in the destination database that have not yet committed or rolled back when you try to restore with replace, SQL Server will wait for those to complete first. I once spent hours scratching my head trying to figure out why my restore was hanging, only to finally discover a transaction had been left open in another session connected to that database!
2. Orphaned users connected to database in single user mode: When you set the database to single user mode, sometimes there may still be other sessions connected, such as background processes or other users who had previously connected. SQL Server won’t be able to obtain an exclusive lock on the database to perform the restore until those sessions disconnect.
3. Network or I/O issues causing slow data transfer: Restoring a large database can take a significant amount of time if the backup file has to be transferred over the network or read from a slow disk. Any network interruptions or I/O bottlenecks can cause the restore to appear stuck. I remember once a restore seemed to hang for over an hour with no progress – turns out someone had accidentally unplugged the network cable to that server!
4. Resource contention with other processes: If your SQL Server is under heavy load from other queries or maintenance tasks, there may not be enough CPU, memory or I/O resources available for the restore to make progress. It has to compete with all the other tasks, which can make it seem like it’s stuck.

Tips to troubleshoot and resolve:

• Use sp_who2 or sys.dm_exec_requests to check for any other active sessions connected to the database being restored. If found, you may need to kill those sessions or wait for them to disconnect.
• Check Windows event logs and SQL Server error logs for any messages indicating network or I/O errors. Work with network and storage admins to investigate potential issues.
• Monitor resource utilization using tools like Resource Monitor or sys.dm_os_wait_stats to see if the restore is being starved of needed resources. Consider temporarily reducing load on the server.
• As a last resort, you may need to restart the SQL Server service to kill any hung sessions. But be careful as this will affect other databases too!

Quick Fix:

RESTORE DATABASE <database name> WITH RECOVERY

This brings the database online and no new transaction logs can be applied.

Script to kill connections and restore:

If you determine that killing the existing connections is necessary to proceed with the restore, you can use the following script:

-- Replace 'YourDatabaseName' with the actual name of your database
USE master;
GO

-- Kill all active connections to the database
DECLARE @DatabaseName nvarchar(128) = 'YourDatabaseName';
DECLARE @SQL nvarchar(max);

SELECT @SQL = COALESCE(@SQL, '') + 'KILL ' + CAST(spid AS nvarchar) + '; '
FROM sys.sysprocesses
WHERE dbid = DB_ID(@DatabaseName);

EXEC sp_executesql @SQL;
GO

-- Set the database to SINGLE_USER mode
ALTER DATABASE YourDatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Perform your restore operation here
RESTORE DATABASE YourDatabaseName 
FROM DISK = 'path_to_your_backup_file'
WITH REPLACE;
GO

-- Set the database back to MULTI_USER mode
ALTER DATABASE YourDatabaseName
SET MULTI_USER;
GO

This script first kills all active connections to the database, then sets it to single user mode, performs the restore operation, and finally sets it back to multi user mode.

Be very careful when using this approach though! Killing connections can disrupt any work those sessions were doing and cause data loss. Make sure you’ve exhausted other troubleshooting options first and that all stakeholders are aware of the impact.

Conclusion:

Restoring a database can become an exercise in patience when it gets stuck. But armed with knowledge of common culprits and some handy troubleshooting techniques, you’ll be better prepared to sleuth out the cause and get your database back in action. The next time you’re staring anxiously at the “Restoring…” message, take a deep breath and work through these steps. And remember, you’re not alone – we’ve all been there!