Friday, March 23, 2012

Problems with restore

I am trying to restore a database and when I try to use the GUI restore
function it just hangs. I can restore through query analyzer without a
problem, and all other functions of Enterprise Manager are working correctly.
I have the same problem from any machine I connect with through Enterprise
Manager.
Has anyone else had this problem before? I am on SP3a of SQL Server2000
with Windows 2000, but have never had a problem before.
Thanks...
Dan,
haven't seen this problem, but you might want to open up another Enterprise
Manager and run a sp_who2 to try to see if this sheds any light on the
blocking process.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I have also experienced a similar problem - thus my searching here.
Anyway - I have found that if I wait long enough it WILL come back and
show me the restore options.
I find this issue when I select the DB and right click and select ALL
TASKS and Select RESTORE. That is where it hangs. I was trying to
restore a 15GB DB last night and it took 13 minutes for the restore
options to finally appear.
I suspect it has something to do w/ either the size of the actual DB or
size of log file or size / quantity of log files / backups is has
retained.
We only keep about 24 hours of logs / backup files in their origianl
location where SQL dropped them. Maybe it's hanging on trying to find
them but they are not there.
Still looking for a solution to bringing up the restore options quicker
than what is current.
Good luck -
|||Well - Problem solved for my issue.
Recap - my issue was SQL hanging for some time while performing ALL
TASKS / RESTORE via Enterprise Manager.
I searched and found a reference to a stored procedure called:
sp_delete_backuphistory
To use it in Query Analyzer simply enter:
EXEC sp_delete_backuphistory '2006-10-01'
where '2006-10-01' is the date to be used to delete prior backup
history records.
When you try to RESTORE - it appears SQL goes out to lunch to query all
the backup history records. If you purge / delete old history that is
not needed SQL will be much more responsive to poping up the restore
option dialogue window. In my case it went from waiting 13 minutes
down to a mere 8 seconds.
again - good luck -
sql

No comments:

Post a Comment