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

Question

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

miraclesoul

Valued Member

Points: 68
More actions
November 26, 2011 at 6:24 am

#248553

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.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

miraclesoul Valued Member Points: 68 More actions · Answer 2

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!

Ray Mond SSCertifiable Points: 7159 More actions · Answer 3

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.