Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Reducing Size Of SQL Backup?

Tags:

sql

sql-server

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?

like image 803
YodasMyDad Avatar asked Aug 20 '09 09:08

YodasMyDad

People also ask

Can you compress a SQL backup file?

SQL Backup Master allows you to optionally compress and/or encrypt your database backups before sending them to one or more backup destinations. Compression method - Choose between Disable (the archive file is created), Zip, and 7-Zip (LZMA) compression methods.

Should you compress SQL backups?

Therefore, taking a compressed backup leads you to likely see less storage being used for the backup file compared to other backups of the same database. This is typically the goal for backup compression to reduce storage space consumed by backups.

How do I shrink a large SQL log file?

Shrinking the File You can do it through management studio by right clicking the database, selecting All Tasks, Shrink, then choose Database or Files. If I am using the Management Studio interface, I generally select Files and shrink only the log file. This can also be done using TSQL.

1 Answers

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

like image 74
marc_s Avatar answered Sep 21 '22 15:09

marc_s
Sign in to Comment

Related questions

Select specific columns from table in SQLAlchemy
Inner join returning duplicate records access sql
Using substr to trim string on Oracle
Oracle SQL "SELECT DATE from DATETIME field "
Change Date Format(DD/MM/YYYY) in SQL SELECT Statement
postgresql: jsonb update multiple keys in one query
Bind a column default value to a function in SQL 2005
Creating SQL table using dynamic variable name
What is passing parameters to SQL and why do I need it?
sqlite LEFT OUTER JOIN multiple tables
Update one table based upon SUM(values) in another table on multiple criteria
Join different tables based on condition
Updating multiple rows with different primary key in one query in PostgreSQL?
Return only numeric values from column - Postgresql
Java/Hibernate - Exception: The internal connection pool has reached its maximum size and no connection is currently available
pyodbc the sql contains 0 parameter markers but 1 parameters were supplied' 'hy000'
Redshift query between date
Postgres: missing FROM-clause entry for table
How hard is it to incorporate full text search with SQL Server?
Getting number of fields in a database with an SQL Statement?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!