Post reply

SQL2K DB Restore from Device Issue

  • April 7, 2005 at 2:18 pm

    #109462

    I recently altered our backup procedures so we're going to a shared disk on the cluster first, THEN tape. I was hoping for a faster recovery scenario when we need it. However, any restore I do off of disk is at least 7 times longer than it took to backup the database. Environment is clustered windows 2003 servers running SQL Server 2000, with 6 SQL instances split between the 2 servers. Was wondering what might be the cause of this and if anyone else has ever seen it?

    Here's an example of my problem:

    Created a backup device that maps to a BAK file that resides on the same instance that I want to retore database X to. It takes 1 second to back up database X, but 7 min and 41 seconds to restore it. Doesn't matter if I'm running Perfmon to view server activity during the restore, what time of day it is, or if it's the only instance on the server. If I wanted disk restores to take this long, I would've stuck with tapes

    Any ideas?

    Thanks,

    Jennifer

  • April 8, 2005 at 3:14 am

    #551402

    How big is the database that you're backing up?

    If it's around the 5GB mark but has very little data in it that would explain why the restore is taking so long.  When SQL does a restore it not only restores the data that was backed up but also initialises the rest of the blank space in the database.

    If you had a 20GB database with 1 GB of data the restore would take that much longer because it has to initialise another 19GB before recovering the database for you.

    Hope that explains things enough , if this isn't the problem you're seeing then feel free to repost and i'll try to help some more

  • April 8, 2005 at 8:02 am

    #551519

    Thanks Mike for the reply. The database is a little over 3 GB in size with only 303 MB of data so your answer makes sense. I had just hoped it would be a little faster.

    Jen

  • April 8, 2005 at 11:54 am

    #551663

    it depends a lot on the server and disk subsystem too, is the mdf on a raid 5 set?

  • April 8, 2005 at 12:13 pm

    #551669

    No, both our log files and data files reside on striped and mirrored Raid 10 sets (not the same ones though).

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply