How can I restore a database w/o the free space?

I have a 500GB database that is only using 50GB of that space. I need to backup and restore this database to a server that only has 100GB space available. I cannot change the allocation on original database. Is there a way to handle this?
more ▼

asked Oct 20, 2010 at 01:59 PM in Default

sqltracy gravatar image

33 3 3 5

(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

Thanks for the great feedback! I downloaded the Red Gate tool on my PC, but it needs to be installed on the server - I can't do that right now.
I'll see how the Idera tool works, too. Last restort may be to shrink the files then set them back, but there are so many!

Thanks again!
more ▼

answered Oct 20, 2010 at 03:19 PM

sqltracy gravatar image

33 3 3 5

The idera one works the same way, as far as I know. In fact, I don't know jack, but my assumption is that both products pretend to be an I/O device through which SQL Server can mount a database and request pages. Otherwise they'd have to write their own query optimizer, parser, lexer etc etc
Oct 20, 2010 at 03:40 PM Matt Whitfield ♦♦
The two applications work in a similar way, excapt that from SQL Servers' perspective, with Idera, the backup is 'attached', whereas with 'Virtual Restore' it is just that, a restore from a backup. Brad McGehee has written an in-depth review of Virtual Restore here http://www.simple-talk.com/sql/sql-tools/brads-sure-guide-to-sql-virtual-restore-/ ...which should explain how it does it. I went to a fascinating technical presentation about Idera's tool recently, but I haven't had time to try it.
Oct 21, 2010 at 04:10 AM Phil Factor
Yeah, figured as much. Not sure why mentioning Idera warrants a down vote, but still!
Oct 21, 2010 at 04:24 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

[Disclosure: I work for Red Gate]
One more option, is another of our tools: SQL Storage Compress. This will allow you to restore the backup to compressed data files, and since most of the space in your database is empty you should get a very large storage saving (for eg, the 50GB of used space should compress down to 5-10GB).

Note that you'll need to set a config parameter called VirtualDiskSize, to avoid SQL Server thinking there's insufficient free space. See the SQL Storage Compress documentation [here][2] for more information.

[2]: http://www.red-gate.com/supportcenter/Content.aspx?c=SQL_Storage_Compresshelp5.0ssc_msg_3257_insufficient_free_space.htm&p=SQL%20Storage%20Compress
more ▼

answered Oct 25, 2010 at 02:32 AM

user-57 gravatar image

22 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 20, 2010 at 01:59 PM

Seen: 3560 times

Last Updated: Oct 20, 2010 at 01:59 PM