SQL backup size different on network vs local

bukkybu 346 Reputation points
2023-03-21T19:15:16.5466667+00:00

I'm running a FULL SQL Server backup job and writing it to two locations (one local and one network). The backup on the two locations is not the same size. Does the network vs local affect the size of the backup?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,691 questions
Accepted answer
  1. Abdul Sajid Mohammed 470 Reputation points Microsoft Employee
    2023-03-21T20:29:03.69+00:00

    Hello @bukkybu

    Thanks for posting your question here.

    To answer your question:

    YES, the network vs local storage can affect the size of the backup file. When you write a backup to a network location, the data is sent over the network to the destination, which can cause additional overhead and network latency compared to writing the backup to a local disk. This can result in a slightly larger backup file size, as the backup file may contain additional metadata or headers related to the network transfer.

    However, the difference in backup file size between the local and network storage should not be much higher, especially if the same compression level and backup options are used for both destinations. If you are seeing a significant difference in backup file size, it may be worth investigating further to ensure that the backup job is configured correctly and that both destinations are using the same backup options.

    https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-devices-sql-server?view=sql-server-ver16#NetworkShare

    Hope this helps. Please let us know if any questions.

    Thankyou.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2023-03-21T21:53:08.7566667+00:00

    Hm, could it be that in one of the cases, you are writing to an existing backup file? (In that case you are appending to the backup file, unless you use the INIT option.)

    0 comments
  2. Seeya Xi-MSFT 16,436 Reputation points
    2023-03-22T05:46:50.52+00:00

    Hi @bukkybu ,

    Compression and encryption can both affect the size of the backup file. If different compression or encryption settings are used for the local and network backups, this can result in different backup sizes.

    Different compression settings can result in different levels of compression, which can affect the size of the backup file. For example, if the backup is compressed using the "No Compression" option, the backup file will be larger than if it is compressed using the "Compress Backup" option. Therefore, if different compression settings are used for the local and network backups, this can result in different backup sizes.

    Different encryption settings can result in different levels of encryption, which can affect the size of the backup file. For example, if the backup is encrypted using the "No Encryption" option, the backup file will be smaller than if it is encrypted using the "Encrypt Backup" option. Therefore, if different encryption settings are used for the local and network backups, this can result in different backup sizes.

    I hope this could inspire you.

    Best regards,

    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".