x

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 '10 at 01:59 PM in Default

sqltracy gravatar image

sqltracy
33 3 3 5

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

7 answers: sort voted first

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
more ▼

answered Oct 20 '10 at 02:12 PM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

+1 @William, what is the main difference between Virtual Restore and Hyperbac? Thanks
Oct 20 '10 at 03:05 PM ozamora
@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).
Oct 21 '10 at 12:07 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

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/
more ▼

answered Oct 20 '10 at 02:46 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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!! :)
Oct 20 '10 at 02:52 PM WilliamD
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.
Oct 20 '10 at 03:30 PM Matt Whitfield ♦♦

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!
Oct 21 '10 at 12:08 AM WilliamD
@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
Oct 21 '10 at 12:31 AM sp_lock
@Jonlee - good info!
Oct 21 '10 at 12:47 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 20 '10 at 02:39 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

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

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
more ▼

answered Oct 21 '10 at 12:12 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

(comments are locked)
10|1200 characters needed characters left
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 ... ?
more ▼

answered Oct 21 '10 at 02:35 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

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!!!!
Oct 21 '10 at 07:49 AM sqltracy
(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x186
x101
x21

asked: Oct 20 '10 at 01:59 PM

Seen: 3140 times

Last Updated: Oct 20 '10 at 01:59 PM