Post reply

Restore from large database backup

  • February 17, 2006 at 6:21 pm

    #111258

    i have about 4 GB database backup file and use SQL SERVER 2000.

    i run my batch script file. it used a long long time to complete.

    how can i write a script to restore in few second.

    my script

    osql -Usa -P -Q "restore database mydatabase from disk = 'mybackup.bak' with replace"

    thank you

  • February 19, 2006 at 6:19 pm

    #621920

    Only possible way to restore database in few seconds is "detach and attach"

  • February 20, 2006 at 4:41 am

    #621966

    Do you have to use the REPLACE option?

    When using the REPLACE option the original database is deleted and the files will be recreated. What is probably taking up all of the time is the creation of these files.

    At the end of the day though, the restore is going to take as long as it takes - unless you're using third-party products.

    Karl
    source control for SQL Server

  • February 20, 2006 at 11:33 am

    #622094

    A 4G database backup is really quite small. A restore of a database this small should only take 2-4 minutes (unless the disks or server are exceptionally slow). As an example I restored a production database to a test server - the backup was 11Gb in size or 1452712 pages. Here's the execution results of the test restore:

    Processed 1452712 pages for database 'TEST_Multi_GB_Database', file 'Multi_GB_Database_Data' on file 1.

    Processed 3 pages for database 'TEST_Multi_GB_Database', file 'Multi_GB_Database_Log' on file 1.

    RESTORE DATABASE successfully processed 1452715 pages in 239.225 seconds (49.746 MB/sec).

    The file name 'TEST_Multi_GB_Database_Data' has been set.

    The file name 'TEST_Multi_GB_Database_Log' has been set.

    The dependent aliases were mapped to the new database owner.

    Database owner changed.

    As you can see I was able to restore an 11 Gb backup in 4 minutes to this test server. Even on heavily used servers my restore throughput hardly ever drops below 1Gb/Minute. You probably need to execute perfmon to find out where the performance deficiencies on your server are.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • February 21, 2006 at 7:07 am

    #622257

    You can speed it up a bit more by "striping" the backup. Break up the backup file into 4 files so that each one is approximately 1GB. SQL will create a separate process for each backup file so it speeds up the backups and the restores.

    BACKUP DATABASE <DBNAME> TO

      DISK = '<filename_1>',

      DISK = '<filename_2>', ........

     

     

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • February 21, 2006 at 7:52 am

    #622287

    Hi, just to give some explanation as to why the restore takes a long time.

    When a backup is restored it creates the "devices" for the database, If you have a database that has 500MB of data but the database files are 5 GIG it will create the whole file before the restore of the data starts.

    There is unfortunately no quick way for creating the "devices" (old SQL 6.5 terminology), the database engine needs to veryfy every page it creates.

    The only way to make it as quick as possible is to decrease the available free space in the database to a low threshold. You should be very carefull when using this approach, if your free space in the file is used it will extend the file according to the parameters set in the database properties and as your machine is taking long to restore the backup it will also trake long to extend the database.

    Something that is worth while to check is if your anti-virus is set to exclude the database files, you could have a problem that the anti-virus is trying to scan the file thatis being used by the SQL server.

    Regards

    Lukas Botha

    Lukas Botha

  • February 21, 2006 at 9:24 am

    #622347

    A few points about striping. For small databases the time savings might not really appear. By small I mean <2-4 Gb databases. You'll have to do some experimentation to find out how many files to stripe to. I've found the number is between 2 and 6 usually. The best time savings that I have achieved is a 50% reduction in backup time. You also have to make sure that your disk subsystem can handle the I/O.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • February 21, 2006 at 8:45 pm

    #622503

    thank you for all comments

  • February 22, 2006 at 7:58 am

    #622617

    Another consideration....are you doing your restore over a network? If so, that could be real slow. You should copy the files to the local drive and restore from there.

    -SQLBill

Viewing 9 posts - 1 through 8 (of 8 total)

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