Backup size

Question

Backup size

caz100

SSCommitted

Points: 1827
More actions
September 24, 2020 at 5:24 pm

#3790751

Hi
Is it possible to have a database size showing 75 gigs and then when you do a copy only backup without compression the backup size is less than 5 gigs.

The reason I ask is the other day I had a db with 80gigs in size in db properties and the copy only back was 70gigs in size!

Viewing 8 posts - 1 through 7 (of 7 total)

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

jasona.work SSC Guru Points: 50117 More actions · Answer 1

Yes, because the backup is only the actual data in the database, as well as enough info to re-create the database files. So you could have a 1TB size-on-disk but if there's only a couple hundred MB of data, you're going to have a small backup file.

Copy-only or not doesn't make a difference, the only thing the copy-only does is not reset the point that log and differential backups will be built from.

caz100 SSCommitted Points: 1827 More actions · Answer 2

Ok thanks ...I was starting to worry one of them one must be wrong.

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 3

caz100 wrote:

<li style="list-style-type: none;">
Ok thanks ...I was starting to worry one of them one must be wrong.

Heh... you still might have a problem. Compression works well but normally not that well. If your database compresses to only 1/40th of the size during a backup, you may have a shedload of "unused" but allocated space in your database that needs to be cleaned up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

caz100 SSCommitted Points: 1827 More actions · Answer 4

Ok is there anyway to predict the backup size so I know if my (80 db size in db properties) with a 5 gigs backup without using compression is ok?

caz100 SSCommitted Points: 1827 More actions · Answer 5

And to add....

Datafile of 50 gigs 40% free space and log file of 30 gigs 99% free.....so 5 gigs might be ok for backup ?

as_1234 SSCrazy Points: 2859 More actions · Answer 6

Firstly I'm only a Junior/mid level DBA so I could be wrong with this.

Secondly I appreciate it's a copy-only backup so perhaps not part of your normal backup strategy.

Your questions make me wonder whether you test your regular backups. My understanding (and remember I'm only a junior DBA) is that it's advisable to do this quite regularly. It's possible to get caught in "SQL server/SQL agent says the backups complete so it's everything is fine" when actually there is an issue. SQL agent server/agent isn't being inaccurate it just doesn't cover all scenairos.

Apologies if I'm being patronising or if I've missed the point. Also apologises for going off tangent to your question.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

caz100 wrote:

And to add....
Datafile of 50 gigs 40% free space and log file of 30 gigs 99% free.....so 5 gigs might be ok for backup ?

50 GB at 40% free puts the used size around 30GB (20GB free). Compressing 30GB to 5GB is entirely possible...

To validate your backup, you need to restore it - that is the only way to validate any backup. Take that backup file and copy it to another server and try to restore it to another instance of SQL Server. At this size, you can test the restore on a personal workstation as long as you have enough storage for the expanded files. Remember, you are only testing that you can successfully restore the backup and bring that database online.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs