Backing up SQL Server 2008 database in different recovery model

Question

We currently have a production environment with 10 SQL Server 2008 databases in Full recovery mode (for obvious reasons). We back these databases up fully at a frequent interval, but due to the Full recovery mode this results in a larger than advertised file size. For example, the .bak file reads 14GB but due to the transaction log the file is actually 60+GB.

We also have a dev environment that we would like to restore the production data to at a weekly interval. However, the dev environments have much less space and cannot handle the full 60+GB backup - they need a Simple recovery mode backup.

Right now, we have a manual process of taking the Full recovery production backup, restoring it on a large enough server, setting the recovery model to Simple, shrinking the database and log, and backing up in Simple mode. Then we restore this new Simple backup to the dev environment. Obviously this is not a very easy solution to automate, and just feels wrong.

I'd imagine there is an easier way to automate this same process and backup the production databases into Simple recovery mode without adding more space to the dev environments.

Any help would be appreciated.

Answer 1

For example, the .bak file reads 14GB but due to the transaction log the file is actually 60+GB.

Your problem is an inadequate maintenance and backup/recovery plan. You are not taking log backups with enough frequency, this is why your log grows through the roof. Start taking log backups to allow truncation and then shrink the log. You'll have a better recovery plan and a smaller database log file.

As for the question: no, there is no way to change the restored database file size(s). The restored database will always have the exact same layout as the original backed up database. Recovery mode has nothing to do with this, your restore would need 60GB on any recovery model. You're only mixing the recovery model in the discussion because you shrink the log by switching to SIMPLE. Read the linked article for more details on this aspect.