sql native backup, does it offer any compression?
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?
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?
imitchie
none at all
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i meant to also say that the backup file contains everything from both DATA + LOG files
none as far as i know but take a look at http://searchstorage.techtarget.com/generic/0,295582,sid5_gci1204216,00.html
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
http://www.sqlservercentral.com/articles/Product+Reviews/litespeedforsqlserver/1862/
>Will the size of
Not really, backups will be smaller compared to the actual size
ASKER
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???
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
ASKER
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??
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
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
ASKER
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
SQL Server 2008 includes built-in compression as an option when you do a backup.
