Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
I figured there was something I was doing wrong. Thank you. –  Daniel Hoerst May 6 '13 at 15:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.