July 20, 2010 at 10:33 am
Hi,
We have SQL Server 2005 database in production of size 100 GB.
mdf file--->80 GB and used space 20 GB and free space is 60 GB
ldf file--->20 GB and used space is 12 GB and free space is 8 GB
I need to backup this database and restore it in a development server to give to developers. Here I do not have enough space on Development server and I'm looking to decrease the mdf and ldf file size while restoing in development server.
Is this possible? Please advice
Thanks
July 20, 2010 at 10:39 am
Can't be done as part of a restore operation I'm afraid.
You would be able to cut the log file size down to 1MB or so if you were doing an attach rather than a restore (using the CREATE DATABASE FOR ATTACH_REBUILD_LOG syntax) but that doesn't seem an option for you.
Unless you want to look at a different strategy to refresh your development databases (replication, for example), your best bet is to add some disk space to your server! 100GB isn't much these days...
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 20, 2010 at 11:02 am
Or shrink down your datafiles, then backup and restore! After that resize your DB to normal size. just a thought.
July 20, 2010 at 11:45 am
Thank you,
I would go for increasing drive space on Dev server as there is no other option:-):-)
July 20, 2010 at 12:01 pm
Hyperbac (from Red Gate software) and Idera have products you can buy that let you restore and work with a backup file without requiring the original disk space. It's a "virtual restore"
July 20, 2010 at 12:23 pm
[font="Courier New"]In our environment, we have to keep many development efforts/projects at the same time. Some projects are deployed quickly while others are kept for a while and keeping the complete database was in fact a problem. The solution we use for this problem is to create a subset database. There are no tools for this and we use custom scripts only. We truncate a bunch of history type tables, keep all the data in the config tables and keep only few records instead of millions based on business type, users etc.... This is definitely cubersome, messy but it works.
We reduce the db size from close to 100 GB down to around 6-10 GB. Bringing the full database from the prod domain to Dev domain is always pain and with this effort dev db's can be put up very quickly.
PS: Adding more space to the box is the right solution here and we did but sometimes we have to keep as many as 100+ dbs on our dev server.[/font]
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply