• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

SQL 2000 Restore taking a long time

We are a medical facility and we will need to recover STAT

We are trying to restore our SQL 2000 database using the comand below


After 22 hours we recieved a message that its 10 percent done.

The database is large. 1.5 TB and normally it take over 6 hours just to back up.

We have done some research and we think this may be the problem of why this is taking so long:

This can happen if the log file has grown several times (usually as a result of very small auto-growth setting). As a result, the log will have several 1000 to several million VLFs (you can read more about VLFs in Transaction Log Physical Architecture). The first phase of recovering a database is called discovery where all the VLFs are scanned (in serial and single threaded fashion) before actual recovery starts. Since this happens much before the analysis phase, there are no messages indicating the progress in the SQL Server error log. Depending on the number of VLFs this initial discovery phase can take several hours even if there are no transactions in the log that need to be processed.

My questions are:

Has anyone had a restore take this long?
Is there a way for us to tell if anything is happening?
Can we believe the counter that says its 10 % complete?
Do we let the restore continue to run?
Are there any alternatives?

Thank you
1 Solution
A large file takes a long time to restore from a full backup.
The issue is also with the resources I.e. disks that are in use, system spec.

Where is it being back up to! A tape library?
What is the read/write speed from where the data is.
Vitor MontalvãoMSSQL Senior EngineerCommented:
The restore finnished? I wouldn't believe too much in the complete percentage because usually after 50, 60% it runs faster.

Good luck
If you're doing it for real now then there's nothing you can do but wait.

In future you need to change your settings for how the database and log file grow - give them both a decent size so they almost never need to grow (keep log and data on different drives - if nothing else it makes size control easier). Doing backup taking 6 hours seems very long: do it to another hard drive and then out to something like tape, or else make it a removable hard drive, like eSata.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure the performance of the storage is very important here. You can split the database in more than one datafile, so SQL Server can make backups in parallel (one core per datafile). You can start for example with 4 datafiles of 400GB each (1.6TB in total). Don't forget that data need to be balanced between those 4 datafiles.

Good luck
Marten RuneCommented:
For this database maybe you should consider backing up using another strategy.
Either by using filegroup backups (is considerable more complicated)
By backing up to multiple files at once, giving greater I/O both when backing up, and when restoring.

You should also take care of those VLF's. You seem to have read up on it, do you know how to mend them!

Ryan McCauleyCommented:
As it's taking a really long time on the hardware you're targeting, can you also attempt a second restore somewhere else at the same time? If so, perhaps you have a more powerful (or less busy) target server you can use (or even a high-end development workstation). If the backup is on tape now, perhaps pulling it to disk first might improve restore speed - you wouldn't want to interrupt the restore you have going now, but hopefully it's on disk already.

If you can restore it to another location somewhere in less time, you can ship all the data to your restored servers, or at the very least, redirect clients to the temporary holding place while you finish the main server and get it up to date.

As others have mentioned, it seems like you've done your research and know how to improve things moving forward, but hopefully we have something that can help you now!

Good luck!

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now