Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My backup/restore was getting sluggish (different story), but looking at the duration of backup and restore, I see that the backup completes in a few seconds, while restoring completes in MINUTES. This is a surprise to me - any suggestions? All comments most appreciated!

Anders, Denmark

The setup is as follows: I backup the 'production' database to a file on the local harddrive, then restore the backup to the 'test' and 'unittest' databases respectively.

Backup/restore script:

    BACKUP DATABASE [Onsdagspool.Prod] TO  
        DISK = N'C:\_UnderBackup\DataBackup\Onsdagspool.Prod.bak' 
        WITH NOFORMAT, INIT,  
        NAME = N'Onsdagspool.Prod-Full Database Backup', 
        SKIP, NOREWIND, NOUNLOAD,  STATS = 10

    RESTORE DATABASE [Onsdagspool.Test] FROM  
        DISK = N'C:\_UnderBackup\DataBackup\Onsdagspool.Prod.bak' 
        WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
    RESTORE DATABASE [Onsdagspool.UnitTest] FROM  
        DISK = N'C:\_UnderBackup\DataBackup\Onsdagspool.Prod.bak' 
        WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

Below the output from backup/restore process:

    Processed 4056 pages for database 'Onsdagspool.Prod', file 'ASPNETDB_8406dd98c17
    54e1881b70937978ae08c_DAT' on file 1.
    100 percent processed.
    Processed 2 pages for database 'Onsdagspool.Prod', file 'ASPNETDB_TMP_log' on fi
    le 1.
    BACKUP DATABASE successfully processed 4058 pages in 3.266 seconds (10.176 MB/se
    c).
    10 percent processed.
    20 percent processed.
    30 percent processed.
    40 percent processed.
    50 percent processed.
    60 percent processed.
    70 percent processed.
    80 percent processed.
    90 percent processed.
    100 percent processed.
    Processed 4056 pages for database 'Onsdagspool.Test', file 'ASPNETDB_8406dd98c17
    54e1881b70937978ae08c_DAT' on file 1.
    Processed 2 pages for database 'Onsdagspool.Test', file 'ASPNETDB_TMP_log' on fi
    le 1.
    RESTORE DATABASE successfully processed 4058 pages in 240.773 seconds (0.138 MB/
    sec).
    10 percent processed.
    20 percent processed.
    30 percent processed.
    40 percent processed.
    50 percent processed.
    60 percent processed.
    70 percent processed.
    80 percent processed.
    90 percent processed.
    100 percent processed.
    Processed 4056 pages for database 'Onsdagspool.UnitTest', file 'ASPNETDB_8406dd9
    8c1754e1881b70937978ae08c_DAT' on file 1.
    Processed 2 pages for database 'Onsdagspool.UnitTest', file 'ASPNETDB_TMP_log' o
    n file 1.
    RESTORE DATABASE successfully processed 4058 pages in 240.656 seconds (0.138 MB/
    sec).
share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

Expected. Backups are assumed to be rare disaster scenarios - restores are regular operations.

Plus. restores actually do more work ;)

share|improve this answer
    
Makes sense (I take it you got that backwards) - is there a way that I can even it out? Make the total operation go faster? –  Anders Juul Aug 19 '10 at 17:47
    
No, because at the end it IS more complex - the system has to actually recreate the files, even the empty parts. So, no way I know of. –  TomTom Aug 19 '10 at 19:22
add comment

Yes. Backup is just a bytes copy operation (copy from MDF/NDF/LDF files into the BAK file). Restore is similar copy in the other direction (from BAK into respective MDF/NDF/LDF files) plus running recovery. Running recovery depends on the size of log to process and is relatively slow. And, as a bonus, restore might have to resize the database files and 0-initialize them, which may be another time consuming operation. So there you have it, restore will always be slower than the corresponding backup (bare differences in hardware etc).

share|improve this answer
add comment

If you are on SQL2005 or higher and your restore operation includes database creation, make sure you enable Instant file initialization which "allows file allocation requests to skip zero initialization on creation. As a result, file allocation requests can occur instantly - no matter what the file size". That means you can do fast restores. Check this blog for detailed explanation.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.