CODEWITHSUNDEEP
sql-serversql-server-2008-r2backup
user1896017
user1896017

Reputation: 83

Backup of SQL Server 2008R2 database is huge

I am backing up a SQL Server 2008 R2 database with the intention of restoring it elsewhere. When I back it up using the backup wizard (right click tasks, backup ) the size of the resulting backup is much larger than I know the database should be (c45Gb when there is only about 5Gb of data)

How can I get a reasonably sized backup created to transfer and restore elsewhere?

Thanks, Dan

Upvotes: 0

Views: 136

Answers (2)

usr
usr

Reputation: 171178

Full backups contain all pages in use plus the part of the log that covers the time span that the backup ran for (approximately). It would surprise me if you generated 40GB of log during a backup of 5GB. At this point I'd check if you really backed up the right database. Or, maybe the destination file already contained other backups.

You say:

I created a smaller backup by going to the options tab on the backup and set the compression to 'compress backup', I also chose Overwrite all existing backup sets and Backed up to a new media set name.

Apparently, the existing file already contained data. It is unlikely that compression is responsible for the bulk of the savings.

Upvotes: 1

user1896017
user1896017

Reputation: 83

I created a smaller backup by going to the options tab on the backup and set the compression to 'compress backup', I also chose Overwrite all existing backup sets and Backed up to a new media set name.

Upvotes: 0

Related Questions