SQL 2000 Restore taking a long time

Posted on 2012-09-03
Last Modified: 2015-01-05
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
Question by:lmclabs
    LVL 76

    Expert Comment

    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.
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    The restore finnished? I wouldn't believe too much in the complete percentage because usually after 50, 60% it runs faster.

    Good luck
    LVL 16

    Accepted Solution

    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.
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    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
    LVL 20

    Expert Comment

    by:Marten Rune
    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!

    LVL 28

    Expert Comment

    by:Ryan McCauley
    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now