Restore Database w/Large Transaction Log File

I have a SQL Server 2000 database that is approximately 300 GB in size and I
auto grew the database transaction log file to 200 GB in size. I grew the
database transaction log file to 200 GB in size since that was the largest
size the file grew.
Weekly I copy the database backup file to a disk and send it to the software
vendor.
Is there away to restore this database without the database transaction log
file growing to 200 GB in size?
To restore the database transaction log file without all of the unused space.
I would like the software vendor not to have to purchase another disk just
to restore an almost empty database transaction log file.
Thank You,
Hello there,
Shrink the transaction log file before backing up the database.
Tunji O
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
I have a SQL Server 2000 database that is approximately 300 GB in size and I
auto grew the database transaction log file to 200 GB in size. I grew the
database transaction log file to 200 GB in size since that was the largest
size the file grew.
Weekly I copy the database backup file to a disk and send it to the software
vendor.
Is there away to restore this database without the database transaction log
file growing to 200 GB in size?
To restore the database transaction log file without all of the unused space.
I would like the software vendor not to have to purchase another disk just
to restore an almost empty database transaction log file.
Thank You,
|||Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:

> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size and I
> auto grew the database transaction log file to 200 GB in size. I grew the
> database transaction log file to 200 GB in size since that was the largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the software
> vendor.
> Is there away to restore this database without the database transaction log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused space.
> I would like the software vendor not to have to purchase another disk just
> to restore an almost empty database transaction log file.
> Thank You,
|||A restored database is exactly like the original. Only the database name
and file names can be changed during the restore process.
As Tunji O said, you need to shrink the transaction log file before taking
the backup. You cannot shrink it during the restore.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...[vbcol=seagreen]
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
|||If you shrink th elog file before you back it up then when you restore that
backup the log file will be the size it was when you backed it up. A 200GB
log file is pretty large for a 300GB database. I suspect you are not doing
regular log backups if it auto grew that much.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...[vbcol=seagreen]
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
|||If I am reading you correctly, the backup has already been trasmitted to the
vendor. At this point, the restore will reproduce the files, data and
transaction logs, exactly as they were when the backup was taken. You only
have the option of renaming the database and/or location and names of the
files, not their sizes nor their content.
If you want to achieve what you have specified, YOU WILL HAVE TO DO IT
AGAIN, just like the other respondents have told you: shrink the log file,
backup the database, transmit the backup, manually regrow the transaction
log back to its original size.
AUTOGROW is a nice feature, in a pinch. However, as the Database
Administrator, you need to manually allocate a reasonable intitial size for
the data and log files, especially for the log files.
If you run the DBCC LOGINFO command on the database, I suspect you will see
thousands of VLOG entries. That many VLOGs is inefficient. If you were to
manually grow an initial model database log file to the allocation you want,
you will probably only see 20 or so VLog entries. This is what you want.
The other possibility is as what one of the other respondents has stated,
you may not be applying an appropriate disaster recovery backup schedule by
including regular transaction log backups.
Sincerely,
Anthony Thomas

"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...
Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:

> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size
and I
> auto grew the database transaction log file to 200 GB in size. I grew
the
> database transaction log file to 200 GB in size since that was the
largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the
software
> vendor.
> Is there away to restore this database without the database transaction
log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused
space.
> I would like the software vendor not to have to purchase another disk
just
> to restore an almost empty database transaction log file.
> Thank You,
sql