Difference in Size of SQL Server 2008 R2 backup on Local disk and on Remote Network Share

  • Difference in Size of SQL Server 2008 R2 backup on Local disk and on Remote Network Share

    Hello All,

    I wanted to take backup of all SQL SERVER 2008 R2 databases backup on network share, i already have a backup schedule for backup on local disk. I have tried two methods:

    Using SQLDATABACKUPANDFTP Software Using SQL Server 2008 R2 maintenance Job

    Using both above methods for the backup on network drive, there is hell alot of difference of the size of data as compared to backup on local disk. Please see details below

    ============================================================== PROBLEM AND DIFFERENCE OF DATA BACKUP USING SQL SERVER TASK SCHEDULER AND SQL SERVER DATABASE BACKUP USING SQLDATABACKUPANDFTP SOFTWARE ==============================================================

    ============================================================== BACKUP OF DATA USING SQL SERVER 2008 R2 SCHEDULER ============================================================== When i took the backup of the data using the SQL Server 2008 R2 scheduler

    The size of the data was almost 10.5 GB.

    The number of files that were backup were 30 items/files

    ============================================================== BACKUP OF DATA USING SQLDATABACKUPANDFTP SOFTWARE ============================================================== When i took the backup of the data using the SQL Server 2008 R2 scheduler

    The size of the data was almost 822 MB.

    The number of files that were backup were 28 items/files

    I really wonder what has been done, the backup files in both cases have extension .bak and no compression is done.

    ============================================================== BACKUP OF DATA USING SQL SERVER 2008 R2 on Local Disk and on Network Share ==============================================================

    I then configured two jobs:

    One for backup on the local disk drive

    One for the backup on the network share

    To my surprise, there is still difference of Size in data and files, please see screen shots below. I really wonder what is happening and why is there so much difference in the size of data.

    Any body has any idea, please feel free to comment and share your knowledge.

  • could the backup job be writing additional backups to the same file? ( does the script feature WITH INIT or not)

    you might have a big 10 gig file with 10+ backups inside it, instead of just a single backup of 877meg that you are expecting.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • there are almost 30 databases on the SQL Server Management Studio. When i take backup, the 30 files have extension .bak

    So now the question is why is the size of the database files so different??

    please share!

  • As Lowell suggested, your backup files might be containing multiple backup sets. See here for details.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Viewing 4 posts - 1 through 3 (of 3 total)

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