SQL Server - Restore From a [large] Full Backup file

travisGatesMcGee@hotmail.com 21 Reputation points
2020-08-19T17:10:13.903+00:00

Wanted to do a regular backup from a repository file server.
My Logic says ... you should first copy the file to a local mounted volume on the server and then run the restore.
However, after testing a few times, we found out that if you do the restore directly from the remote file server (say 180 min), it is significantly faster than copying the file (350 min) and then doing a local restore (10 min).

Why would that be?
Is it possible that SQL Server may not need everything (bytes, sectors, etc. out of a compressed backup files?

Azure Database Migration service

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,191 Reputation points
    2020-08-26T15:06:27.12+00:00

    Good day @travisGatesMcGee@hotmail.com

    A backup file can include tens of backups and not only a single backup.

    When you back up a database to an existing media set, then you can controls whether the backup operation appends to or overwrites the existing backup sets. The default is append.

    This means that your backup file might includes multiple backups and the size of the backup's file might be tens times more than the size of the single backup which you want to use. In this case restoring the backup directly from the file does not need to pass the entire file from the storage to the server, while transfering the file does transfer the entire file.

    I have no idea if this your case, since you did not provided enough information, but this is a very common case and it fits your description.

    You can check the content of the backup file to confirm how many backups are there and get more information on the backups in the file using the command: "RESTORE HEADERONLY"

    For example: 

    RESTORE HEADERONLY   
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\YourBackupFileName.bak'   
GO
    0 comments
  2. travisGatesMcGee@hotmail.com 21 Reputation points
    2020-08-29T02:54:27.167+00:00

    Hi piituach

    Of course, it is a single file ... compressed about 200 Gigs, single Full Backup file copy file server to db server via Windows file copy (with small overhead) over regular WAN between Boston and Houston

    Tried it many times.... straight restore is always faster from copy first and then restore.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.