Differential backup is larger than full backup

Question

Differential backup is larger than full backup

sqlfriend

SSC Guru

Points: 52769
More actions
October 17, 2011 at 10:13 am

#258880

We have a database A refreshed from another database B on another server every night.
The database A is a small subset of the database B.
When we see refresh it means we have a daily run SSIS pacakge which first truncates the A database tables, and import data from B database to A.
My question is about the backup for A database, we usually do Full backup on Sunday, daily differential backup. No transaction backup needed for this one, so I set it up as simple recovery mode.
But I see the differential backup is bigger than the full backup, I guess it is probably because every
night the import package make the differential backup big.
And therefore cause disk space low.
For this case, what is the best way or solution to backup the database but not use too much space?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Is your differential backup appending to the same backup file, or is it overwriting, or is it creating a new file each time it is run?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Please remember that each differential backup backs up all changes since the last full backup.

If you are truncating all the tables and reloading each night it makes since that over the week that the differential backup would become bigger than the full backup. In this case, I would simply run a full backup nightly after each load.

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

I use whatever it is as default for diffrential in maintenance plan.

I think it is created a new file each time it run, because I see timestamp on each days backup file like below:

Mydatabase_backup_2011_10_03_210007_1040818.diff

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

Lynn Pettis (10/17/2011)
Please remember that each differential backup backs up all changes since the last full backup.
If you are truncating all the tables and reloading each night it makes since that over the week that the differential backup would become bigger than the full backup. In this case, I would simply run a full backup nightly after each load.

Thanks, that makes sense, currently the full back up is 4 gb, the differential is 5 gb.

We used to do full backup every day and found it use too much space, then changed to differential, and now we found it use even more space.

Any other recommendations besides to do full bacup daily?

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Take a look at Lynns comments in that case. You may be better off to just do daily fulls.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

sqlfriends (10/17/2011)
I use whatever it is as default for diffrential in maintenance plan.
I think it is created a new file each time it run, because I see timestamp on each days backup file like below:
Mydatabase_backup_2011_10_03_210007_1040818.diff

Still, if you are basically doing a truncate and load nightly, I would simply run a full backup nightly after each load.

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

Is there a way to do only schema backup, I mean exclude to backup data?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

I guess you apply scripts to run this... just save those somewhere so you can rerun them.

Plan be would be scripting the whole db or use red-gate diff tools. Not sure if this is automatble.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Depends on what edition of SQL Server 2008 you are running.

If native compression is available, use it. If not, you may want to look at a third party backup solution that will allow compression. My recommendation is HyperBac, now a RedGate product. I used it at a previous employer with not problems and it allows you to use the SQL Server native backup commands.

sqlfriend SSC Guru Points: 52769 More actions · Answer 10

Mostly people use database A is for analysis.

But sometimes they do need to do a little change to the database, for example add a view or sproc that they need.