While performing Restore Database task in SQL 2000 from Enterprise Manager it may hang or appears hung at times. However you may not find any specific errors in the SQL server error log, application log and system event log.





The reason why "restore database" takes a long time to come up or appears hung is because of huge number of records present in backupset table of the MSDB database, and for each backup_set_id, the restore database task will loop in backupmediafamily.






To identify the number of records in the backupset table (present in the MSDB database-> MSDB.dbo.backupset), you can use the following query,


select a.database_name,a.backup_set_id,a.media_set_id,a.type,b.physical_device_name,a.backup_finish_date,a.server_name from msdb..backupset a

join msdb..backupmediafamily b

on a.media_set_id = b.media_set_id

join msdb..backupfile c

on a.backup_set_id = c.backup_set_id

where a.database_name = 'YOUR_DB_NAME'

order by a.database_name,a.backup_set_id


Take backup of MSDB and remove the old records from backupset, backupfile and backupmediafamily tables (please be sure that you take a backup of these tables / database before doing the cleanup). To remove the entries either use DELETE to remove specific records or TRUNCATE to empty the table.

If you want to look at the actual queries which are fired in the background by Enterprise Manager while performing the restore task you can run a profiler trace.




SE, SQL support

Reviewed by

Akbar Farishta & Sudarshan Narasimhan 

TL, SQL support