Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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

Backup of data using SQL Server 2008 R2 Scheduler:

When I took the backup of the data using the SQL Server 2008 R2 scheduler

  1. The size of the data was almost 10.5 GB.
  2. The number of files that were backup were 30 items/files

Backup of data using SQLBackupAndFTP software:

When I took the backup of the data using the SQL Server 2008 R2 scheduler

  1. The size of the data was almost 822 MB.
  2. 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 Sserver 2008 R2 on Local Disk and on Network Share:

I then configured two jobs:

  1. One for backup on the local disk drive
  2. 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.

share|improve this question
1  
Could you show the commands you are using so they can be compared? (First idea: some of the backups have compression enabled.) –  Richard Nov 26 '11 at 14:32
    
umm..how can we notify the OP that his question was answered although he's not a user of our site? :-) –  Marian Nov 30 '11 at 13:08
    
thanks alot for the help :) i will check in the light of the answers what is the main issue. thanks once again all :) –  user6677 Feb 14 '12 at 10:49
add comment

migrated from stackoverflow.com Nov 26 '11 at 16:14

This question came from our site for professional and enthusiast programmers.

2 Answers

SQL Server, since its 2008 version, has a feature of compressing data in backups. But by default the SQL Server backups are not compressed.

In order to user this feature you have to enable native compressed backups manually in Management Studio - at Server Properties, in the backups wizard or in the BACKUP DATABASE command (the COMPRESSION keyword). After trying compression for one backup compare it to the files of the other tool and I'm sure you'll see that they are close.

share|improve this answer
add comment

You say you are "using SQLDATABACKUPANDFTP Software" if this software is from http://sqlbackupandftp.com/ then it says it "Zips, Encrypts and FTPs the database backups" which would explain much of the difference.

I'm afraid your screenshots didn't appear so I can't comment on the other difference.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.