Restore with Percent free space

Usually you have huge databases in production with atleast 50-60% free space
...buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db
?
Makes sense '
Thanks,
Ranga> Makes sense '
The request makes sense, but the implementation would have huge ramification
s on the restore
process.
Consider a database file in which you have pages. Any page can be in use, an
d any page in there can
have the address for this page in the page header (for instance). The restor
e process cannot
"compact" these pages to move them towards the beginning of the file, becaus
e of the linkage etc
between pages. Sure, it might be physically possible, but that restore would
be much much slower.
And it might not even be possible at all, since recovery is part of the rest
ore process and physical
page addresses might be in the transaction log records (might be, I'm not 10
0% certain of this).
Perhaps is it possible to have two types of restore: what we have today and
a "compact-and-restore".
You might want to vent such a request at http://connect.microsoft.com/site/s...aspx?SiteID=68.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> Usually you have huge databases in production with atleast 50-60% free spa
ce
> ...buth when you want to restore the db in dev...you need to have simila
r
> storage size as in prod. Would it be nice if we have a option like
> "FreeSpacePercent" parameter in the restore command where we can mention h
ow
> much free space is good enough.
> For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> space. I should be able to restore the DB using "FreeSpacePercent" = 5%, S
o,
> all I need is 45GB instead of 100 GB in my dev environment to restore the
db?
> Makes sense '
> Thanks,
> Ranga|||Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLSe...=1252
87
"Tibor Karaszi" wrote:

> The request makes sense, but the implementation would have huge ramificati
ons on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use,
and any page in there can
> have the address for this page in the page header (for instance). The rest
ore process cannot
> "compact" these pages to move them towards the beginning of the file, beca
use of the linkage etc
> between pages. Sure, it might be physically possible, but that restore wou
ld be much much slower.
> And it might not even be possible at all, since recovery is part of the re
store process and physical
> page addresses might be in the transaction log records (might be, I'm not
100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today an
d a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/s...aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>|||I almost figured that MS wouldn't be too keen on re-writing large portions o
f the restore code.
Thanks for the update, Ranga.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:6010C190-C1DB-4980-8FF8-E6CC37C75913@.microsoft.com...[vbcol=seagreen]
> Thanks...There was a similar request...but declined by MS
> https://connect.microsoft.com/SQLSe...=12
5287
> "Tibor Karaszi" wrote:
>