Restore Database Wizard Slow to Open in SSMS

The Restore Database Wizard is slow to open when msdb holds years of backup history. The wizard reads that history before it draws the first screen. The fix has three parts. Count the history, delete the old part in small steps, and keep it short from then on.

Why the Restore Database Wizard Reads History

SQL Server writes one row to the msdb database for every backup it takes. It also keeps rows for every file inside the backup and for every restore. The Restore Database wizard reads those rows to build the list of backup sets you can restore. Nothing deletes them by default, so a busy server collects them for years.

A server with 15-minute log backups writes about 96 history rows a day for one database. That is 24 hours times 4. Across many databases and several years, the list gets long. The wizard is the first place you notice, because it is the one screen that reads all of it.

Count the History

Start by measuring. This query gives the total number of backup rows and the date of the oldest one.

SELECT COUNT(*) AS BackupRows, MIN(backup_start_date) AS OldestBackup
FROM msdb.dbo.backupset;

This development server holds 221 rows going back to November 2025, which is small. Your numbers will differ. On a production server, a count far above this one tells you why the wizard stalls. Some advice says the history tables have no useful indexes. On SQL Server 2025, backupset has five. They are the primary key plus indexes on database name, finish date, media set and backup set ID. So an index isn’t the first thing to add.

Measure One Database

To see how history builds up, the demo database takes 40 backups to the NUL device. That is a bit bucket, so no file is written, but SQL Server still records each backup in msdb. COPY_ONLY keeps the backups out of any real backup chain.

IF DB_ID(N'RestoreWizardHistoryDemo') IS NULL CREATE DATABASE RestoreWizardHistoryDemo;
GO
DECLARE @i int = 1;
WHILE @i <= 40
BEGIN
    BACKUP DATABASE RestoreWizardHistoryDemo TO DISK = N'NUL' WITH COPY_ONLY;
    SET @i += 1;
END;
GO
SELECT COUNT(*) AS BackupRows
FROM msdb.dbo.backupset
WHERE database_name = N'RestoreWizardHistoryDemo';

The Messages tab fills with 40 groups of progress lines. The count is 40 rows. The file table backupfile holds 80 more, a data file and a log file for each backup. That is why history grows faster than the number of backups.

Back Up msdb First

History deletion can’t be undone, and some teams keep that history on purpose. Ask the owners of the databases whether they need it. Then back up msdb. The backup is your way back. This statement writes a full msdb backup file, so the demo doesn’t run it. Use your own folder.

BACKUP DATABASE msdb TO DISK = N'D:\SqlBackups\msdb_before_history_cleanup.bak' WITH CHECKSUM, INIT;

Delete It in Steps

The built-in procedure for this is sp_delete_backuphistory. It deletes history older than a date, for every database on the server. Run it with an old date, so the first call touches only a little.

On a server with years of history, one call with a recent date deletes everything at once. A large delete runs as one transaction. It grows the msdb log, and it can grow tempdb too. Monthly steps keep each transaction small. Each call is a separate unit of work, so you can stop between calls. This script reads the oldest date in backupset and prints one statement for each month up to your retention. It changes nothing. You review the list and run the statements in order.

SET NOCOUNT ON;
DECLARE @keepFrom date = DATEADD(DAY, -90, CAST(SYSDATETIME() AS date));
DECLARE @first date = (SELECT CAST(MIN(backup_start_date) AS date) FROM msdb.dbo.backupset);
WITH steps AS (
    SELECT DATEADD(MONTH, 1, @first) AS cutoff
    UNION ALL
    SELECT DATEADD(MONTH, 1, cutoff) FROM steps WHERE DATEADD(MONTH, 1, cutoff) < @keepFrom
)
SELECT CONCAT(N'EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = ''', CONVERT(char(10), cutoff, 120), N''';') AS StatementToRun
FROM steps
WHERE cutoff < @keepFrom
UNION ALL
SELECT CONCAT(N'EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = ''', CONVERT(char(10), @keepFrom, 120), N''';')
WHERE @first < @keepFrom
ORDER BY 1
OPTION (MAXRECURSION 1000);

The server here keeps 90 days. Choose your own retention. A business that must restore to any day of the last year needs a year of history. Its retention is longer.

To clean one database only, such as a dropped database, call sp_delete_database_backuphistory with its name. It removes every history row for that name.

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'RestoreWizardHistoryDemo';
SELECT COUNT(*) AS BackupRowsAfter
FROM msdb.dbo.backupset
WHERE database_name = N'RestoreWizardHistoryDemo';

Keep It Short

A cleanup that runs once helps for a while, and then the history grows back. Run it every week at a quiet hour. Count the rows before and after the first few runs, so you know the job does its work. A maintenance plan has a History Cleanup task for this. Without SQL Server Agent, as on Express, call the procedure from a scheduled task with sqlcmd.

A stored procedure parameter can’t be an expression, so compute the date into a variable first. The next two statements delete history older than 90 days for every database. The demo doesn’t run them, because that would delete real history on this server. A run with a date five years back deleted nothing and gave no error.

DECLARE @oldest datetime = DATEADD(DAY, -90, GETDATE());
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest;

Put the same two statements in the scheduled job.

You could argue that deleting history is risky, because the wizard builds its list from it. It does. A restore from a known file in T-SQL needs none of it, and the backup files themselves stay untouched. Delete only history older than any backup you would still use.

When the History Is Small

When the history is short and the wizard is still slow, the cause is elsewhere. Open the wizard from SSMS on another computer. If it opens fast there, look at the first computer’s own SSMS install or its list of drives.

What to Remember

Count the rows first. Delete in monthly steps from the oldest date. Keep a retention you can defend, and schedule the cleanup. Then the Restore Database Wizard opens quickly again. Run the cleanup script when you finish with the demo.

USE master;
GO
IF DB_ID(N'RestoreWizardHistoryDemo') IS NOT NULL
BEGIN
    ALTER DATABASE RestoreWizardHistoryDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RestoreWizardHistoryDemo;
END;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'RestoreWizardHistoryDemo';

A slow wizard is not a broken SSMS, it is a history nobody cleaned.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.