Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql native backup, does it offer any compression?

Posted on 2007-11-23
13
Medium Priority
?
449 Views
Last Modified: 2008-02-01
Hi,
If I'm doing sql server native backup, as compare to using 3rd party agent such ( legato / litespeed ) will I have an advantages in term of backup size?  will the size of db backup file  equivalent with database size?
0
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +4
13 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20337760
none at all
0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20337764
below is not 100% accurate, but serves to understand what is going on
if you do a native SQL Server backup, what you get is a FULL copy of data, equal to the used space in the database. for example, if you run sp_spaceused and get

database_name, database_size, unallocated space  
test, 2.68 GB, 0.91 GB

the size of your backup is database_size - unallocated. it makes a copy of all data, less the unallocated space.
usually, zipping the backup will compress it to 15-20% it's size.  3rd party agents usually include compression, but when put against native+zip, the results are quite similar (backup size)
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20337765
i meant to also say that the backup file contains everything from both DATA + LOG files
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 31

Expert Comment

by:James Murrell
ID: 20337769
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20337777
Litespeed offers more compressed & faster backups
http://www.sqlservercentral.com/articles/Product+Reviews/litespeedforsqlserver/1862/

>Will the size of

Not really, backups will be smaller compared to the actual size
0
 

Author Comment

by:motioneye
ID: 20337844
so we were saying that native sql backup will only copy those data + log with excluded any unallocated space in data files and log file???
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20337860
motioneye, that's right. winzip is not the best compressor. 7zip and winrar and some others can do better if you really need to cut down on size
0
 

Author Comment

by:motioneye
ID: 20337869
ok is there any option that I can use to backup the database and run any vbs or wsh scripts to compress the files?? will dts work on this??
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20337883
If you look in Books online, you will see a good description of the BACKUP command and, actually, this will tell you what it does.    Basically, you can do a FULL, DIFFERENTIAL or TRANSACTION LOG backup via the BACKUP command.   It really is better if you check out in books online what these backups do and how they relate to each other, expecially in terms of when the backups are restored, using the RESTORE command of course.

I think your question needs turning around a bit.   For example, why would anyone not use SQL BACKUP?   Personally, I much prefer using SQL's builtin BACKUP and RESTORE compared to any 3rd party backup.  It works well, does what it's designed to do and has never let me down.  I don't have to worry about 3rd party licencing either... I remember enquiring about LiteSpeed and they charge for client licences.    In our case, that would mean purchasing client LiteSpeed licences for a lot of desktops and laptops.   SQL Client is free.  

The main  reasons to consider a 3rd party backup solution for SQL Server might be speed, encryption, compression.    For example, if you had huge database that simply took too long to backup even out-of-hours, you would probably want to look at something like LiteSpeed.   Similarly, if the business had highly senstive data that they wanted to protect, they would have to consider encryption for the  backups as well as everything else.    Unfortunately, the biggest weekness of SQL BACKUP is its lack of builtin encryption.   However, there's nothing stopping a DBA from implementing a 3rd party encryption tool to encrypt the backup files.  Finally, with regard to compression, SQL BACKUP doesn't do any (as per imitchie's comment).    Despite these drawbacks, I love SQL BACKUP.

AustinSeven

0
 

Author Comment

by:motioneye
ID: 20337896
hmmm... basically I'm not looking for any 3rd party product, just want to know how do I integrate vb scripting to compress the *.bak after we had done with backup.. I guess to incorporate this it may require a dts knowledge and as well vbs scripting
0
 
LVL 22

Expert Comment

by:dportas
ID: 20338813
SQL Server 2008 includes built-in compression as an option when you do a backup.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20338836
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20340688
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question