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.