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 ramifications 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 restore process cannot
"compact" these pages to move them towards the beginning of the file, because 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 restore 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 and a "compact-and-restore".
You might want to vent such a request at http://connect.microsoft.com/site/sitehome.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 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|||Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
"Tibor Karaszi" wrote:
> > Makes sense '
> The request makes sense, but the implementation would have huge ramifications 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 restore process cannot
> "compact" these pages to move them towards the beginning of the file, because 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 restore 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 and a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/sitehome.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 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
>|||I almost figured that MS wouldn't be too keen on re-writing large portions of 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...
> Thanks...There was a similar request...but declined by MS
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
> "Tibor Karaszi" wrote:
>> > Makes sense '
>> The request makes sense, but the implementation would have huge ramifications 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 restore process cannot
>> "compact" these pages to move them towards the beginning of the file, because 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 restore 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 and a
>> "compact-and-restore".
>> You might want to vent such a request at
>> http://connect.microsoft.com/site/sitehome.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 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
>>