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?

asked Oct 20 '10 at 01:59 PM in Default

sqltracy
33 ● 3 ● 3 ● 5

(comments are locked)

7 answers:

[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 for more information.

answered Oct 25 '10 at 02:32 AM

user-57
22 ● 1 ● 1 ● 2

(comments are locked)

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!

answered Oct 20 '10 at 03:19 PM

sqltracy
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 '10 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 '10 at 04:10 AM Phil Factor

Yeah, figured as much. Not sure why mentioning Idera warrants a down vote, but still!

Oct 21 '10 at 04:24 AM Matt Whitfield ♦♦

(comments are locked)