SQL Backup size

vyanku

I take the SQL database backup (full) it is near about 9 GB.
If I take native backup it is near about 1.5 GB.
I am using SQL 2005.Navision 5.0 SP1 .The database size in SQL is 13GB.
I had already truncate the log file.
How I can reduce the SQL backup size. :-k

bbrown

Why do you need to reduce the size?

The Navision backup only contains the data. It does not include key/index info or database/table structures. The SQL does incluse this. If your database is heavily indexed (a whole other discussion) then that could account for this difference.

David_Singleton

How about winzip or winrar.

garak

Why do you not use a "pack program " like winzip, WinAce, WinRar etc ?There you can also split the backupfile to many archives...

vyanku

winzip, winrar are ok. But why it shows such a large size of backup? while it is showing only 1.5 gb for Nav backup with application objects.
Is it any wrong in making backup?
In Native backup it shows errors of table locking thats why I suggest SQL backup to our client. But now our client shows us size issue ](*,)

garak

what for an backuptype you select? Full? Incl. your log file(s) :?:
How large are your log file(s) :?:

vyanku

I am selecting full type of backup.
My .ndf file is 13GB
.ldf file is 234mb
.mdf file is 678mb

Slawek_Guzek

First look at fragmentation in your database. Even If you have only one record on page in the database the entire page goes to backup (so say you have rec 100 bytes long, and due to massive fragmentation you have just a few records on single database page, yet the whole 8kb page have to go to backup file).

Optimising all tables and indexes and decreasing fragmentation will greatly reduce size of your SQL backup (and speed up your database), but even if database is perfectly optimised SQL backup file will be still much bigger that native backup - as bbrown said SQL backup ALWAYS contains everything, data, indexes, users, permission objects etc, while native contains table data only, or data and table definitions.

Regards,
Slawek

David_Singleton

vyanku wrote:

I am selecting full type of backup.
My .ndf file is 13GB
.ldf file is 234mb
.mdf file is 678mb

](*,) ](*,) ](*,) ](*,) ](*,)

Looks like another Autoshrink. [-X

vyanku

But autoshrink is false in properties of the database. :-k

David_Singleton

vyanku wrote:

But autoshrink is false in properties of the database. :-k

AH ok sorry :oops: .
How often do you backup your log file to keep it so small?

garak

the log could also be so small if the "Recovery Model" is Simple.
Is this so vyanku or did you shrink the log before or take you ever x minutes a log backup and in your database also not many transactions?

bbrown

David Singleton wrote:

vyanku wrote:

But autoshrink is false in properties of the database. :-k

AH ok sorry :oops: .
How often do you backup your log file to keep it so small?

There could be a maintenance job that's shrinking the log.

garak

so why is the fbk smaller then the bak file.
The native backup utility has also a compres. algorithm to compress the datas. Also the fbk doesn't store any indexes.
It stores only the datas and objects (if selected). And the index information! The indexes will rebuild during restoring.
The 2005 hasn't this algorithm and the sql backup is only a "copy" of all datas, indexes and so on. thats the reason why the sql backup has the "same" size like the database self.

EDIT:
With 2008 there is a compression with the option "WITH COMPRESSION" that you can set.
Search here the msdn for "backup compression".

Regards

garak

a little "Trick" the get the backup files small on the HDD is:

go to the properties of the Folder where youre backupfiles should store (like \MyBackupfolder).
Press there "Advanced" Button and check the option to "Compress to save disk space" and "Folder is ready for archiving."
If you have a subfolder or subfolders behind your \MyBackupFolder you would be prompted for enabling also the subfolders.
Note: It's better to create one subfolder before enabling this archive!

now, when you store the Backup to \MyBackupfolder and take a look to the *.bak files you will see the following:

Filesize: 9GB (for example)
File size on Disk: 3 GB (for example).
<- this is used only for saving space on the disk

:!: :!: Never activate the compression on the folder / drive where windows runs. It's not the best for the systemperformance ;-)

Regards

DenSter

vyanku wrote:

But why it shows such a large size of backup? while it is showing only 1.5 gb for Nav backup with application objects.

A SQL Server backup is essentially just a copy of the data from your database. So if your SQL database is 13GB, your backup will also be about that size. This includes the data, the indexes, everything. When a SQL Server backup is restored, it simply copies the indexes from the backup file, it doesn't have to recreate them.

A NAV backup is the app table objects and the data in it. This does NOT include any indexes, only the data. The indexes are created when the database is restored, based on the index definition within the table objects. Because the NAV backup file does not contain the indexes themselves, its size is significantly smaller.

The fact that your 1.5GB NAV backup creates a 13 GB database is a pretty good indication that this database has massive amounts of data in indexes. You need to have a SQL Server expert come in and help you tune them.

garak

@vyanku:

Also you can read the Onleine Help. Go to Tools -> Backup -> HELP button and read there the Description and follow there also the links.
For more information about backups in NAV, see the Installation and System Management manual for either Native Backup or the SQL Backup Option. <- On Product CD / DVD

Regards

David_Singleton

bbrown wrote:

David Singleton wrote:

vyanku wrote:

But autoshrink is false in properties of the database. :-k

AH ok sorry :oops: .
How often do you backup your log file to keep it so small?

There could be a maintenance job that's shrinking the log.

That's what I meant. I was not implying that they had a person employed 24 hours a day manually creating log file backups. Mind you from some of the things I am seeing these days... :whistle:

bbrown

David Singleton wrote:

bbrown wrote:

There could be a maintenance job that's shrinking the log.

That's what I meant. I was not implying that they had a person employed 24 hours a day manually creating log file backups. Mind you from some of the things I am seeing these days... :whistle:

That's what I thought you meant. My response was more to the comment about "auto shrink" not being checked. I should have quoted that post instead. Sorry for the confusion. :oops:

I can understand your comment "...some of the things I am seeing..". I spend 15 years doing hardware and systems integration before my NAV days. I saw lots of things back then but I think I see even more today. Back then I could understand it a bit, as much of the technology was "bleeding-edge" and there weren't a lot of "experts" that truly understood it. But today much of this technology is mainstream and there are competent and knowldgeable resources. Much the same can be said about the real-world. But customers still continue to believe they can save money by not accepting (and paying for) the proper advice.

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

David Singleton wrote:

How about winzip or winrar.

Or even better : 7-zip. Already some years I only use 7-zip.

DenSter wrote:

The fact that your 1.5GB NAV backup creates a 13 GB database is a pretty good indication that this database has massive amounts of data in indexes. You need to have a SQL Server expert come in and help you tune them.

Or a very low fillfactor but this is quite unlikely because the default is 100%.

stryk

If the BAK size still is a problem - even after all the suggested optimizations - well, there are 3rd party tools to implement some kind of "Backup Compression" even with SQL Servre 2005; for example: http://www.quest.com/litespeed-for-sql-server/

David_Singleton

The backup is NINE GIG that's less than most mobile phones have these days.


Seriously if your client has an issue in handling 9gig in this day and age, then you are going to have many more serious issues down the track to worry about.

Just get more local drive space and move on.

Believe me if you see this as a problem, I can only dread all the other mistakes that were made in this implementation.

garak

I belive, that he doesn't have a problem with the size.
I think he will only know why the native backup has a other size then the sql backup.

But i agree, handling 9gig in this day should not be a problem.

David_Singleton

garak wrote:

I belive, that he doesn't have a problem with the size.
I think he will only know why the native backup has a other size then the sql backup.

But i agree, handling 9gig in this day should not be a problem.

I am maybe being to subtle then
The point is that if the OP had said something like:

"Hi I am brand new to Navision and want to start learning can some one tell me...." then I would agree, but in this case the poster only found this out when the customer told them. This is pretty basic Navision stuff, and is clearly covered in the certification exams that someone at the Partner would have to have passed for the company to become certified.

How do things get this far. Partners should not think of their clients as a school where they learn and get paid at the same time. Sorry but I just don't like this.

Vyanku, you should not be experimenting on your clients production system, you should be sending out your SQL experts to plan this with the client, so they have the answers.

garak

David you know, in this respect, you have my full consent.

David_Singleton

garak wrote:

David you know, in this respect, you have my full consent.

Thanks, though I have probably unfairly to Vyanku taken this thread off topic.

vyanku

Thaks Guys. I got my answer for why sql database backup size is more than nav backup size