SQL Server Performance

RESTORE datbase

Discussion in 'SQL Server 2005 Log Shipping' started by jasmin123, Apr 12, 2007.

  1. jasmin123 New Member

    I have to move a database from one location to different
    server. We will be doing that this weekend. The size of the db is 115 GB. It
    normally takes 7-8 for full backup. So we will be using that backup to
    restore that DB to differnent location.
    I tried restroing directly from backup server but it takes forever, since
    one is in Omaha and other in VA. Even after 8 hrs of restoration it was not
    done, so i had to leave my machine at work and let it restore. But when i
    went back next day the server had kicked me out for being inactive for long
    time plus the restore was not finished yet. The Database was showing as
    loading.
    So i was wondering if there is any smart way to do so. Less time...
    Right now I am zipping that backup file and will copy that zipped file to
    local machine and unzip it and try to restore from there, but copying itself
    is taking forever. and it has already failed 3 times
  2. ndinakar Member

    Yes thats the usual way to to - backup, zip, copy, unzip and restore.
    but 7-8 hrs to backup a 115 GB is really long. Are you using the native SQL backup or any third party tools? There are some really good third party tools that do a wonderful job of zipping ( to almost 80% of original size). so copying a smaller file can take lesser time. Looks like your limitation is on the hardware side.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. jasmin123 New Member

    I'm using native sql backup
  4. MohammedU New Member

    It is one time you can take the help the trial vesrions like LiteSpeed which compresses the backup to upto 80% and backup will be faster...
    115 GB database backup size will be around 20-30GB depends on the compression level you use...
    And take the backup to multiple files for easy moving from one location to another... if you use native or third party backups...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. satya Moderator

    Due to the sheer size of the database, it is expected the relevant time on the restore operations. If this is an ongoing operation or task on the enviornment then take help of third party tools. Do not attempt to stop the process as it will invalidate the database consistency or might mark the database as suspect after the restore.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page