Reducing Size Of SQL Backup?

Question

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?

Answer 1

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

Answer 2

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:

• Take a transaction log backup. (See: BACKUP(TRANSACT-SQL) )
• Shrink the transaction log file down to an appropriate size for your needs. (See:How to use DBCC SHRINKFILE.......)
• Schedule regular transaction log backups according to data recovery requirements.

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

Answer 3

Hello . 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 .