Restore without big transaction log

All,
Our production database is growing quite rapidly and I'm having trouble
refreshing our QA and Dev environments due to limited disk space on those
servers. My process now is
- Restore the production DB with a new name
- Set to simple and shrink the log file to 0
- Back up the shrunk db to a new .bak file
- Restore to dev or QA from that .bak file
Our production DB is way to busy to shrink the log file on and it's kind of
a pain to go through all those steps. I was wondering if anyone knew a way to
restore from a backup with an empty .trn file? I don't see that this is
possible, but thought I'd ask.
Thanks in advance.
> I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
Your assumption is correct. A restored database need as big file size for each file as the one you
were restoring from.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:3357D5E4-5001-42C7-8ED5-E854B7E90711@.microsoft.com...
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
|||On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
wrote:
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
One solution might be once you backup your production database (full
backup) to truncate your transaction log and shrink it, then perform
the restore after that. I would think that would clear up quite a few
space issues.
|||> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that.
This won't help. I assume you mean shrink the transaction log file for the (existing) destination
database. Won't help, since SQL Server need to create each file with same size as the originating
database has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1184209592.791721.108060@.k79g2000hse.googlegr oups.com...
> On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> wrote:
> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that. I would think that would clear up quite a few
> space issues.
>
|||On Jul 12, 2:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This won't help. I assume you mean shrink the transaction log file for the (existing) destination
> database. Won't help, since SQL Server need to create each file with same size as the originating
> database has.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "acorcoran" <acorco...@.gmail.com> wrote in message
> news:1184209592.791721.108060@.k79g2000hse.googlegr oups.com...
>
>
>
>
> - Show quoted text -
This is true, however, I guess I was thinking of backing up the
production database, truncting the log file, then re-backing up the
database (just for day 1). When you restore, you will be restoring a
very limited size log file. Of course, I guess this simply depends on
how large your log file grows between your full backups. As all
future backups would consist of a full backup then a truncation,
maintaining the size on your source database.
Just a though.

Restore without big transaction log

All,
Our production database is growing quite rapidly and I'm having trouble
refreshing our QA and Dev environments due to limited disk space on those
servers. My process now is
- Restore the production DB with a new name
- Set to simple and shrink the log file to 0
- Back up the shrunk db to a new .bak file
- Restore to dev or QA from that .bak file
Our production DB is way to busy to shrink the log file on and it's kind of
a pain to go through all those steps. I was wondering if anyone knew a way t
o
restore from a backup with an empty .trn file? I don't see that this is
possible, but thought I'd ask.
Thanks in advance.> I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
Your assumption is correct. A restored database need as big file size for ea
ch file as the one you
were restoring from.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:3357D5E4-5001-42C7-8ED5-E854B7E90711@.microsoft.com...
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind o
f
> a pain to go through all those steps. I was wondering if anyone knew a way
to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.|||On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
wrote:
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind o
f
> a pain to go through all those steps. I was wondering if anyone knew a way
to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
One solution might be once you backup your production database (full
backup) to truncate your transaction log and shrink it, then perform
the restore after that. I would think that would clear up quite a few
space issues.|||> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that.
This won't help. I assume you mean shrink the transaction log file for the (
existing) destination
database. Won't help, since SQL Server need to create each file with same si
ze as the originating
database has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
> On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> wrote:
> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that. I would think that would clear up quite a few
> space issues.
>|||On Jul 12, 2:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This won't help. I assume you mean shrink the transaction log file for the
(existing) destination
> database. Won't help, since SQL Server need to create each file with same
size as the originating
> database has.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "acorcoran" <acorco...@.gmail.com> wrote in message
> news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -
This is true, however, I guess I was thinking of backing up the
production database, truncting the log file, then re-backing up the
database (just for day 1). When you restore, you will be restoring a
very limited size log file. Of course, I guess this simply depends on
how large your log file grows between your full backups. As all
future backups would consist of a full backup then a truncation,
maintaining the size on your source database.
Just a though.

Restore without big transaction log

All,
Our production database is growing quite rapidly and I'm having trouble
refreshing our QA and Dev environments due to limited disk space on those
servers. My process now is
- Restore the production DB with a new name
- Set to simple and shrink the log file to 0
- Back up the shrunk db to a new .bak file
- Restore to dev or QA from that .bak file
Our production DB is way to busy to shrink the log file on and it's kind of
a pain to go through all those steps. I was wondering if anyone knew a way to
restore from a backup with an empty .trn file? I don't see that this is
possible, but thought I'd ask.
Thanks in advance.> I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
Your assumption is correct. A restored database need as big file size for each file as the one you
were restoring from.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:3357D5E4-5001-42C7-8ED5-E854B7E90711@.microsoft.com...
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.|||On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
wrote:
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
One solution might be once you backup your production database (full
backup) to truncate your transaction log and shrink it, then perform
the restore after that. I would think that would clear up quite a few
space issues.|||> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that.
This won't help. I assume you mean shrink the transaction log file for the (existing) destination
database. Won't help, since SQL Server need to create each file with same size as the originating
database has.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
> On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> wrote:
>> All,
>> Our production database is growing quite rapidly and I'm having trouble
>> refreshing our QA and Dev environments due to limited disk space on those
>> servers. My process now is
>> - Restore the production DB with a new name
>> - Set to simple and shrink the log file to 0
>> - Back up the shrunk db to a new .bak file
>> - Restore to dev or QA from that .bak file
>> Our production DB is way to busy to shrink the log file on and it's kind of
>> a pain to go through all those steps. I was wondering if anyone knew a way to
>> restore from a backup with an empty .trn file? I don't see that this is
>> possible, but thought I'd ask.
>> Thanks in advance.
> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that. I would think that would clear up quite a few
> space issues.
>|||On Jul 12, 2:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > One solution might be once you backup your production database (full
> > backup) to truncate your transaction log and shrink it, then perform
> > the restore after that.
> This won't help. I assume you mean shrink the transaction log file for the (existing) destination
> database. Won't help, since SQL Server need to create each file with same size as the originating
> database has.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "acorcoran" <acorco...@.gmail.com> wrote in message
> news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
>
> > On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> > wrote:
> >> All,
> >> Our production database is growing quite rapidly and I'm having trouble
> >> refreshing our QA and Dev environments due to limited disk space on those
> >> servers. My process now is
> >> - Restore the production DB with a new name
> >> - Set to simple and shrink the log file to 0
> >> - Back up the shrunk db to a new .bak file
> >> - Restore to dev or QA from that .bak file
> >> Our production DB is way to busy to shrink the log file on and it's kind of
> >> a pain to go through all those steps. I was wondering if anyone knew a way to
> >> restore from a backup with an empty .trn file? I don't see that this is
> >> possible, but thought I'd ask.
> >> Thanks in advance.
> > One solution might be once you backup your production database (full
> > backup) to truncate your transaction log and shrink it, then perform
> > the restore after that. I would think that would clear up quite a few
> > space issues.- Hide quoted text -
> - Show quoted text -
This is true, however, I guess I was thinking of backing up the
production database, truncting the log file, then re-backing up the
database (just for day 1). When you restore, you will be restoring a
very limited size log file. Of course, I guess this simply depends on
how large your log file grows between your full backups. As all
future backups would consist of a full backup then a truncation,
maintaining the size on your source database.
Just a though.

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
Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
"Tibor Karaszi" wrote:

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

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:
>

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

RESTORE VERIFYONLY - Disk space

What is the disk space required for RESTORE VERIFYONLY ?
Could it be the size of the database ?
I got a backup of 11.8 GB and only 11.3 GB free.
If I run RESTORE VERIFYONLY, it does not return anything (no 'The
backup set is valid.').
After that I always get MS Windows 'Device full' errors whenever I try to
save any
file even if I still have 11 GB free.
I may not event start the MS Windows event viewer
I have to reboot my server.
Could it be that RESTORE VERIFYONLY restores the DB in some temporary
file and that the space is not actually released after RESTORE
VERIFYONLY failed ?
If this is the case is there a way to specify a directory for RESTORE
VERIFYONLY ?
Thanks in advance.
PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
5.00.2195 SP4.
Hi, Eric
RESTORE VERIFYONLY only reads the backup, without writing anything in
any database (not even a temporary file), so free space is not an issue
when you use RESTORE VERIFYONLY (i.e. you can have only 1GB of free
space, and it would still work for a 11GB backup).
Razvan
|||VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially read
the header. In SQL Server 2005, it reads the header, checksums, and page
chain. It doesn't write the backup back out anywhere.
What else is happening on your server when this runs?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easyn et.net...
> What is the disk space required for RESTORE VERIFYONLY ?
>
> Could it be the size of the database ?
>
> I got a backup of 11.8 GB and only 11.3 GB free.
>
> If I run RESTORE VERIFYONLY, it does not return anything (no 'The
> backup set is valid.').
>
> After that I always get MS Windows 'Device full' errors whenever I try to
> save any
> file even if I still have 11 GB free.
> I may not event start the MS Windows event viewer
> I have to reboot my server.
>
> Could it be that RESTORE VERIFYONLY restores the DB in some temporary
> file and that the space is not actually released after RESTORE
> VERIFYONLY failed ?
>
> If this is the case is there a way to specify a directory for RESTORE
> VERIFYONLY ?
>
> Thanks in advance.
>
> PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
> 5.00.2195 SP4.
>
|||The command is executed via a Bash script that is started by the SQL Agent
as a DTS Package.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%233w7T9fFGHA.3172@.TK2MSFTNGP10.phx.gbl...
> VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially
read[vbcol=seagreen]
> the header. In SQL Server 2005, it reads the header, checksums, and page
> chain. It doesn't write the backup back out anywhere.
> What else is happening on your server when this runs?
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
> news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easyn et.net...
to
>

RESTORE VERIFYONLY - Disk space

What is the disk space required for RESTORE VERIFYONLY ?
Could it be the size of the database ?
I got a backup of 11.8 GB and only 11.3 GB free.
If I run RESTORE VERIFYONLY, it does not return anything (no 'The
backup set is valid.').
After that I always get MS Windows 'Device full' errors whenever I try to
save any
file even if I still have 11 GB free.
I may not event start the MS Windows event viewer
I have to reboot my server.
Could it be that RESTORE VERIFYONLY restores the DB in some temporary
file and that the space is not actually released after RESTORE
VERIFYONLY failed ?
If this is the case is there a way to specify a directory for RESTORE
VERIFYONLY ?
Thanks in advance.
PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
5.00.2195 SP4.Hi, Eric
RESTORE VERIFYONLY only reads the backup, without writing anything in
any database (not even a temporary file), so free space is not an issue
when you use RESTORE VERIFYONLY (i.e. you can have only 1GB of free
space, and it would still work for a 11GB backup).
Razvan|||VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially read
the header. In SQL Server 2005, it reads the header, checksums, and page
chain. It doesn't write the backup back out anywhere.
What else is happening on your server when this runs?
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easynet.net...
> What is the disk space required for RESTORE VERIFYONLY ?
>
> Could it be the size of the database ?
>
> I got a backup of 11.8 GB and only 11.3 GB free.
>
> If I run RESTORE VERIFYONLY, it does not return anything (no 'The
> backup set is valid.').
>
> After that I always get MS Windows 'Device full' errors whenever I try to
> save any
> file even if I still have 11 GB free.
> I may not event start the MS Windows event viewer
> I have to reboot my server.
>
> Could it be that RESTORE VERIFYONLY restores the DB in some temporary
> file and that the space is not actually released after RESTORE
> VERIFYONLY failed ?
>
> If this is the case is there a way to specify a directory for RESTORE
> VERIFYONLY ?
>
> Thanks in advance.
>
> PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
> 5.00.2195 SP4.
>|||The command is executed via a Bash script that is started by the SQL Agent
as a DTS Package.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%233w7T9fFGHA.3172@.TK2MSFTNGP10.phx.gbl...
> VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially
read
> the header. In SQL Server 2005, it reads the header, checksums, and page
> chain. It doesn't write the backup back out anywhere.
> What else is happening on your server when this runs?
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
> news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easynet.net...
> > What is the disk space required for RESTORE VERIFYONLY ?
> >
> >
> > Could it be the size of the database ?
> >
> >
> > I got a backup of 11.8 GB and only 11.3 GB free.
> >
> >
> > If I run RESTORE VERIFYONLY, it does not return anything (no 'The
> > backup set is valid.').
> >
> >
> > After that I always get MS Windows 'Device full' errors whenever I try
to
> > save any
> > file even if I still have 11 GB free.
> > I may not event start the MS Windows event viewer
> > I have to reboot my server.
> >
> >
> > Could it be that RESTORE VERIFYONLY restores the DB in some temporary
> > file and that the space is not actually released after RESTORE
> > VERIFYONLY failed ?
> >
> >
> > If this is the case is there a way to specify a directory for RESTORE
> > VERIFYONLY ?
> >
> >
> > Thanks in advance.
> >
> >
> > PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
> > 5.00.2195 SP4.
> >
> >
>

RESTORE VERIFYONLY - Disk space

What is the disk space required for RESTORE VERIFYONLY ?
Could it be the size of the database ?
I got a backup of 11.8 GB and only 11.3 GB free.
If I run RESTORE VERIFYONLY, it does not return anything (no 'The
backup set is valid.').
After that I always get MS Windows 'Device full' errors whenever I try to
save any
file even if I still have 11 GB free.
I may not event start the MS Windows event viewer
I have to reboot my server.
Could it be that RESTORE VERIFYONLY restores the DB in some temporary
file and that the space is not actually released after RESTORE
VERIFYONLY failed ?
If this is the case is there a way to specify a directory for RESTORE
VERIFYONLY ?
Thanks in advance.
PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
5.00.2195 SP4.Hi, Eric
RESTORE VERIFYONLY only reads the backup, without writing anything in
any database (not even a temporary file), so free space is not an issue
when you use RESTORE VERIFYONLY (i.e. you can have only 1GB of free
space, and it would still work for a 11GB backup).
Razvan|||VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially read
the header. In SQL Server 2005, it reads the header, checksums, and page
chain. It doesn't write the backup back out anywhere.
What else is happening on your server when this runs?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easynet.net...
> What is the disk space required for RESTORE VERIFYONLY ?
>
> Could it be the size of the database ?
>
> I got a backup of 11.8 GB and only 11.3 GB free.
>
> If I run RESTORE VERIFYONLY, it does not return anything (no 'The
> backup set is valid.').
>
> After that I always get MS Windows 'Device full' errors whenever I try to
> save any
> file even if I still have 11 GB free.
> I may not event start the MS Windows event viewer
> I have to reboot my server.
>
> Could it be that RESTORE VERIFYONLY restores the DB in some temporary
> file and that the space is not actually released after RESTORE
> VERIFYONLY failed ?
>
> If this is the case is there a way to specify a directory for RESTORE
> VERIFYONLY ?
>
> Thanks in advance.
>
> PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
> 5.00.2195 SP4.
>|||The command is executed via a Bash script that is started by the SQL Agent
as a DTS Package.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%233w7T9fFGHA.3172@.TK2MSFTNGP10.phx.gbl...
> VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially
read
> the header. In SQL Server 2005, it reads the header, checksums, and page
> chain. It doesn't write the backup back out anywhere.
> What else is happening on your server when this runs?
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
> news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easynet.net...
to[vbcol=seagreen]
>

Restore Problem (6.5)

I need to restore a SQL 6.5 database dump onto my laptop. Unfortunately I
don't have enought disk space, so work has kindly provided a USB external
hard disk. I can create the device on that disk, but what I try and restore
the database. It comes up with a 'Unable to write to server-side connection'
error message.
Any ideas ?
Cheers
MarkHi,
After creating the device , did you tried creating the database. The size
should be identical to the source database from where you received the dump
file.
Give me more information about,
1. Location of Dump file
2. Are you able to see the logical drives (newly attached external disk)
from explorer?
If you still have issues , try creating a table on the new database created.
Thanks
Hari.
MCDBA
"Flems" <Mark.Flemans@.apakgroup.com> wrote in message
news:uLYdnMXvDHA.1788@.tk2msftngp13.phx.gbl...
> I need to restore a SQL 6.5 database dump onto my laptop. Unfortunately I
> don't have enought disk space, so work has kindly provided a USB external
> hard disk. I can create the device on that disk, but what I try and
restore
> the database. It comes up with a 'Unable to write to server-side
connection'
> error message.
> Any ideas ?
> Cheers
> Mark
>

Restore of Database Terminates

I have 58 GB of free space on my server and I created a database and tried t
o
restore a 500 mb database and it tells me that it requires 85071298560.
The database i am restoring is quite small and I have space. I dont
understand whats the problem.Please post result fo following. Preferably, the result should be nicely
formatted to make it easy to read.:
restore filelistonly from disk =
'File Name of Database Backup File With Full Path'
"Lontae Jones" wrote:

> I have 58 GB of free space on my server and I created a database and tried
to
> restore a 500 mb database and it tells me that it requires 85071298560.
> The database i am restoring is quite small and I have space. I dont
> understand whats the problem.|||My backup is 534 mb and below is the information from the filelist and it
shows something different
DB1_Data_Data D:\relic\db\Data\db1_Data.MDF D PRIMARY 31055347712 3518437208
0640
Db1_Log D:\relic\db\Data\DB1_Log.LDF L NULL 54018048000 35184372080640
"Absar Ahmad" wrote:
> Please post result fo following. Preferably, the result should be nicely
> formatted to make it easy to read.:
> restore filelistonly from disk =
> 'File Name of Database Backup File With Full Path'
> "Lontae Jones" wrote:
>|||It is obvious from this information that the File Size of the Source Databas
e
is about 28.9 GB for the Data File and 50.3 GB for the Log File. When you tr
y
to restore from its backup, SQL Server will also need to create files of sam
e
size on the destination server. As the Free Space is only 58 GB, the restore
is failing.
Your backup size is so small because the source database has very little dat
a.
So you have following options:
1. Get the source database shrinked, take a backup and restore from that
backup.
or
2. Create about 80 GB Free Space.
Best of Luck.
"Lontae Jones" wrote:

> My backup is 534 mb and below is the information from the filelist and it
> shows something different
> DB1_Data_Data D:\relic\db\Data\db1_Data.MDF D PRIMARY 31055347712 35184372
080640
> Db1_Log D:\relic\db\Data\DB1_Log.LDF L NULL 54018048000 35184372080640|||Yeah I shrank the log and data. Thanks
"Absar Ahmad" wrote:

> It is obvious from this information that the File Size of the Source Datab
ase
> is about 28.9 GB for the Data File and 50.3 GB for the Log File. When you
try
> to restore from its backup, SQL Server will also need to create files of s
ame
> size on the destination server. As the Free Space is only 58 GB, the resto
re
> is failing.
> Your backup size is so small because the source database has very little d
ata.
> So you have following options:
> 1. Get the source database shrinked, take a backup and restore from that
> backup.
> or
> 2. Create about 80 GB Free Space.
> Best of Luck.
> "Lontae Jones" wrote:
>
>

Restore needs too much space

I'm trying to restore a database from a complete backup (70MB)
and on restore it says that the restore needs 713MB of free space to
continue
Why? and is there a way to get around this?
I assume the transaction log is the suspect...
The size of the backup file is usually unrelated to the actual size of the
files that make up the database. It is more aligned with the amount of data
in the files. You really don't have a choice but to find enough disk space
to restore the database to. You can use the
RESTORE HEADERONLY
RESTORE FILELISTONLY
commands to see what is on the backup device.
Andrew J. Kelly SQL MVP
"Ben" <test@.test.com> wrote in message
news:OzMXO6IZFHA.220@.TK2MSFTNGP10.phx.gbl...
> I'm trying to restore a database from a complete backup (70MB)
> and on restore it says that the restore needs 713MB of free space to
> continue
> Why? and is there a way to get around this?
> I assume the transaction log is the suspect...
>
|||When you restore a backup, the transaction log file is expanded to the
size it had when the backup was made, but it remains empty (the actual
log records are not stored in the backup, only the size of the
transaction log).
Razvan
|||And if the data file where, say, 2 GB, but only contained 1.5 GB of data,
you would still need the 2 GB in order for the store to create the original
2 GB data file and then restore the 1.5 GB into.
Sincerely,
Anthony Thomas

"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1117432747.852108.163660@.z14g2000cwz.googlegr oups.com...
When you restore a backup, the transaction log file is expanded to the
size it had when the backup was made, but it remains empty (the actual
log records are not stored in the backup, only the size of the
transaction log).
Razvan

Restore needs too much space

I'm trying to restore a database from a complete backup (70MB)
and on restore it says that the restore needs 713MB of free space to
continue
Why? and is there a way to get around this?
I assume the transaction log is the suspect...The size of the backup file is usually unrelated to the actual size of the
files that make up the database. It is more aligned with the amount of data
in the files. You really don't have a choice but to find enough disk space
to restore the database to. You can use the
RESTORE HEADERONLY
RESTORE FILELISTONLY
commands to see what is on the backup device.
--
Andrew J. Kelly SQL MVP
"Ben" <test@.test.com> wrote in message
news:OzMXO6IZFHA.220@.TK2MSFTNGP10.phx.gbl...
> I'm trying to restore a database from a complete backup (70MB)
> and on restore it says that the restore needs 713MB of free space to
> continue
> Why? and is there a way to get around this?
> I assume the transaction log is the suspect...
>|||When you restore a backup, the transaction log file is expanded to the
size it had when the backup was made, but it remains empty (the actual
log records are not stored in the backup, only the size of the
transaction log).
Razvan|||And if the data file where, say, 2 GB, but only contained 1.5 GB of data,
you would still need the 2 GB in order for the store to create the original
2 GB data file and then restore the 1.5 GB into.
Sincerely,
Anthony Thomas
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1117432747.852108.163660@.z14g2000cwz.googlegroups.com...
When you restore a backup, the transaction log file is expanded to the
size it had when the backup was made, but it remains empty (the actual
log records are not stored in the backup, only the size of the
transaction log).
Razvan

Restore needs too much space

I'm trying to restore a database from a complete backup (70MB)
and on restore it says that the restore needs 713MB of free space to
continue
Why? and is there a way to get around this?
I assume the transaction log is the suspect...The size of the backup file is usually unrelated to the actual size of the
files that make up the database. It is more aligned with the amount of data
in the files. You really don't have a choice but to find enough disk space
to restore the database to. You can use the
RESTORE HEADERONLY
RESTORE FILELISTONLY
commands to see what is on the backup device.
--
Andrew J. Kelly SQL MVP
"Ben" <test@.test.com> wrote in message
news:OzMXO6IZFHA.220@.TK2MSFTNGP10.phx.gbl...
> I'm trying to restore a database from a complete backup (70MB)
> and on restore it says that the restore needs 713MB of free space to
> continue
> Why? and is there a way to get around this?
> I assume the transaction log is the suspect...
>|||When you restore a backup, the transaction log file is expanded to the
size it had when the backup was made, but it remains empty (the actual
log records are not stored in the backup, only the size of the
transaction log).
Razvan|||And if the data file where, say, 2 GB, but only contained 1.5 GB of data,
you would still need the 2 GB in order for the store to create the original
2 GB data file and then restore the 1.5 GB into.
Sincerely,
Anthony Thomas
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1117432747.852108.163660@.z14g2000cwz.googlegroups.com...
When you restore a backup, the transaction log file is expanded to the
size it had when the backup was made, but it remains empty (the actual
log records are not stored in the backup, only the size of the
transaction log).
Razvan