Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: The fast way to restore database

    Hello, everyone:

    My database backup files are 3-5GB. Restoring always take over 20 minutes. Is there the fast way to restore the big database?

    Thanks

    ZYT

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you have not already, make sure that the backup file, and the destination are on two separate physical drives.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    The SQL restore process is bound by the limitations of your file system; a faster file system will enable you to restore more quickly.

    Some things you could do to improve the speed of the disks/file system:

    1. Move the backup files to a separate physical drive

    2. Make sure that the data files and log files are on separate physical drives. If possible, ensure that the log files are on a RAID-1 (or RAID 10) drive system.

    3. Use multiple RAID controllers to separate the IO to the backup drives and the data/log drives.


    I just completed taking my own advice on #3 and installed two U160 RAID controllers on my 200 GB test database. Things did not go exactly as I had planned during the installation and I ended up being forced to restore a backup of the database. It took a little over 4 hours to complete. This, however, is considerably better than the 18 hours it took when both external SCSI arrays were cabled to a single RAID controller.

    Regards,

    hmscott

    Quote Originally Posted by yitongzhang
    Hello, everyone:

    My database backup files are 3-5GB. Restoring always take over 20 minutes. Is there the fast way to restore the big database?

    Thanks

    ZYT
    Have you hugged your backup today?

  4. #4
    Join Date
    Mar 2003
    Posts
    223
    Thanks for reply.

    I put backup file in another hard drive from destination database. It is slow. How do you think dettach / Attach database? Is it possible for production?

    Thanks

    ZYT

  5. #5
    Join Date
    Jun 2005
    Posts
    79
    There's not much point having your backup on the same drive as the database is there - what if your drive goes bad?

    You need to put you backup on fast hardware - nothing else will speed up the process.
    Paul Randal
    Dev Lead, Microsoft SQL Server Storage Engine

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    In order to use attach detach (which I DON'T recommend), you would have to ensure:

    1. All users are logged out and the database is "free"

    2. Detach the database/copy the files to backup location

    When it comes time to restore the database, you would still incur the time penalty of copying the files from the backup location to the correct destination.

    Also, with detach/attach, you lose any ability to make a point-in-time restore. It would be better (in my opinion) to invest some $$$ in faster disks.

    Regards,

    hmscott
    Quote Originally Posted by yitongzhang
    Thanks for reply.

    I put backup file in another hard drive from destination database. It is slow. How do you think dettach / Attach database? Is it possible for production?

    Thanks

    ZYT
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •