CODEWITHSUNDEEP
sqlsql-server
YodasMyDad
YodasMyDad

Reputation: 9475

Reducing Size Of SQL Backup?

I am using SQL Express 2005 and do a backup of all DB's every night. I noticed one DB getting larger and larger. I looked at the DB and cannot see why its getting so big! I was wondering if its something to do with the log file?

Looking for tips on how to find out why its getting so big when its not got that much data in it - Also how to optimise / reduce the size?

Upvotes: 13

Views: 44674

Answers (6)

marc_s
marc_s

Reputation: 754468

Several things to check:

  • is your database in "Simple" recovery mode? If so, it'll produce a lot less transaction log entries, and the backup will be smaller. Recommended for development - but not for production

  • if it's in "FULL" recovery mode - do you do regular transaction log backups? That should limit the growth of the transaction log and thus reduce the overall backup size

  • have you run a DBCC SHRINKDATABASE(yourdatabasename) on it lately? That may help

  • do you have any log / logging tables in your database that are just filling up over time? Can you remove some of those entries?

You can find the database's recovery model by going to the Object Explorer, right click on your database, select "Properties", and then select the "Options" tab on the dialog:

alt text

Marc

Upvotes: 18

Chaoix
Chaoix

Reputation: 1360

7zip your backup file for archiving. I recently backed up a database to a 178MB .bak file. After archiving it to a .7z file is was only 16MB. http://www.7-zip.org/

If you need an archive tool that works with larger files sizes more efficiently and faster than 7zip does, I'd recommend taking a look at LZ4 archiving. I have used it for archiving file backups for years with no issues: http://lz4.github.io/lz4/

Upvotes: 0

Michael P
Michael P

Reputation: 141

If it is the backup that keeps growing and growing, I had the same problem. It is not a 'problem' of course, this is happening by design - you are just making a backup 'set' that will simply expand until all available space is taken.

To avoid this, you've got to change the overwrite options. In the SQL management studio, right-click your DB, TASKS - BACKUP, then in the window for the backup you'll see it defaults to the 'General' page. Change this to 'Options' and you'll get a different set of choices.

The default option at the top is 'Append to the existing media set'. This is what makes your backup increase in size indefinitely. Change this to 'Overwrite all existing backup sets' and the backup will always be only as big as one entire backup, the latest one.

(If you have a SQL script doing this, turn 'NOINIT' to 'INIT')

CAUTION: This means the backup will only be the latest changes - if you made a mistake three days ago but you only have last night's backup, you're stuffed. Only use this method if you have a backup regime that copies your .bak file daily to another location, so you can go back to any one of those files from previous days.

Upvotes: 14

Arrabi
Arrabi

Reputation: 3768

as you do a daily FULL backup for your Database , ofcourse it will get so big with time . so you have to put a plan for your self . as this 1st day: FULL / 2nd day: DIFFERENTIAL / 3rd day: DIFFERENTIAL / 4th day: DIFFERENTIAL / 5th day: DIFFERENTIAL

and then start over .

and when you restore your database , if you want to restore the FULL you can do it easily , but when you need to restore the DIFF version , you backup the first FULL before it with " NO-recovery " then the DIFF you need , and then you will have your data back safely .

Upvotes: 0

John Sansom
John Sansom

Reputation: 41819

It sounds like you are running with the FULL recovery model and the Transaction Log is growing continuously as the result of no Transaction Log backups being taken.

In order to rectify this you need to:

I suggest reading the following Microsoft reference in order to ensure that you are managing your database environment appropriately.

Recovery Models and Transaction Log Management

Further Reading: How to stop the transaction log of a SQL Server database from growing unexpectedly

Upvotes: 3

Darknight
Darknight

Reputation: 2500

One tip for keeping databases small would be at design time, use the smallest data type that you can use.

for Example you may have a status table, do you really need the index to be an int, when a smallint or tinyint will do?

Darknight

Upvotes: 0

Related Questions