question

sqltracy avatar image
sqltracy asked

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?
backuprestorespace
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
Have you tried the new products from red-gate? [Virtual Restore][1] may be what you want. They have trial versions of their software and their tools rock! [1]: http://www.red-gate.com/products/SQL_Virtual_Restore/index.htm
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ozamora avatar image ozamora commented ·
+1 @William, what is the main difference between Virtual Restore and Hyperbac? Thanks
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
@ozamora - they are one and the same. Red-Gate bought Hyperbac not so long ago and re-branded the product (they added a couple of things too, but basically the same tool with a different name).
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
WilliamD has a good answer. If you do not like that one, another option is to use a staging server where you shrink the database before moving it to your space cramped target server. There are also a couple of options to work around the problem instead of directly doing what you are describing. You may want to just add more space on the target server and avoid the whole issue. Unless you are using something exotic like SSD, then harddrive space tends to be cheap and if you are using a full fledged SAN you can normally reallocate the space dynamically. And finally, you may be able to look at options short of a full backup and restore such as replication with only the data actually needed on the other server. Snapshot replication in fact can be handled in many ways similar to a backup and restore.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
Well, +1 in spirit to both William and Timothy (seeing as I'm out of votes again), but I feel it's only fair to point out that Idera were first to the 'mount a backup file as a pseudo-drive' game with [SQL Virtual Database][1]. I won't be writing a similar tool, I don't see the point, but in the future I might write a 'extract the data from the backup' kind of tool. [1]: http://www.idera.com/Products/SQL-toolbox/SQL-virtual-database/
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
Good point +1 - I am a Red-Gate customer, hence the link to their product. RE: I'm gonna write XYZ tool - pulling my hair out here, is there anything he can't do!! :)
3 Likes 3 ·
sp_lock avatar image sp_lock commented ·
@Matt we use Idera's tool also and im impressed... Not tried the redgate tool but im sure its equally as good. Just a quick tip, the idera VB has a quicker initialization time if you use the SQLSafe product as it created the streaming file that is used. +1
1 Like 1 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Hahaha - Like *everyone* is a Red Gate customer, or a Friend of Red Gate. It's properly depressing when you're trying to write some software and you just want to earn enough to feed your kids and everywhere you turn it's already covered. Hats off to their marketing team, but thoroughly depressing none the less.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
I am by no means biased, I would go for any other product that is better. I don't have this particular tool in my box and would certainly consider it useful - especially for this sort of thing (big restore on small testbed). Misquote from Wayne's World - Build it and they will come!
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Jonlee - good info!
0 Likes 0 ·
Show more comments
sqltracy avatar image
sqltracy answered
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!
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
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
0 Likes 0 ·
Phil Factor avatar image Phil Factor commented ·
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.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Yeah, figured as much. Not sure why mentioning Idera warrants a down vote, but still!
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
Another thought I had that woke me last night (I know - I am a sad man); could you do a partial restore of only the filegroups/files you need? If this is just to get hold of *some* of the data inside the backup, you could try restoring only the part of the backup you need: [How to: Restore Files and Filegroups][1] [1]: http://msdn.microsoft.com/en-us/library/ms178099.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
Do you actually need a Backup/Restore or can you build an SSIS package to transfer the whole thing to the new location? This would only take the data, plus whatever growth % you have set on the target, and therefore squeeze it in to the space you have ... ?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqltracy avatar image sqltracy commented ·
I think the SSIS idea is good - I do need all of the data, so I can't just get some of the files. I am trying deperately to get additional space for this today, but if that doesn't transpire, I will most likely copy the data. The tools sound great, but this will be for stress testing and I'm leary of trying a new tool for that kind of traffic. Thanks so much!!!!
0 Likes 0 ·
user-57 avatar image
user-57 answered
[Disclosure: I work for Red Gate]
One more option, is another of our tools: [SQL Storage Compress][1]. 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. [1]: http://www.red-gate.com/products/SQL_Storage_Compress/index.htm [2]: http://www.red-gate.com/supportcenter/Content.aspx?c=SQL_Storage_Compresshelp5.0ssc_msg_3257_insufficient_free_space.htm&p=SQL%20Storage%20Compress
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.