SqlServer2005, restore much slower than backup - it that to be expected?

Question

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).

Answer 1

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

Plus. restores actually do more work ;)

Answer 2

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).

Answer 3

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.