Re: Restore Database w/Large Transaction Log File

From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 12/13/04 

Date: Sun, 12 Dec 2004 23:03:05 -0600

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,

Relevant Pages

  • Re: Still dont understand log backups
    ... The transaction log, whether or not it is one or many files, is a serial, ... until the end of the physical log file is reached. ... backup is executed, the active "virtual" logs are backed up, but the ... Using BULK LOGGED, does the online log file contain EVERY transaction for all time -- given that I don't truncate it manually? ...
    (microsoft.public.sqlserver.server)
  • Re: Log Shipping: question about secondary dB mode?
    ... Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. ... And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. ... all the backup and restore jobs are proceeding without errors. ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL 2005 Shrink issue
    ... When you perform a Transaction Log Backup, passive virtual logs in your transaction log file are deleted. ... you can change your database's recovery model to SIMPLE when you want to get rid of your passive virtual logs in your transaction log file. ...
    (microsoft.public.sqlserver.tools)
  • Re: suitable plan for Log backup
    ... After the transaction log backup, I have tried to perform the shrinkfile command but I got the error message as below: ... How can I reduce the log file size in this situation? ...
    (microsoft.public.sqlserver.server)
  • Re: optimum log file size
    ... Fixing of Transaction log file based up on the amount of bulk transaction ... CUrrent log growth will be because of FULL recovery mode. ... If you do not require a point in time recovery or if your database ...
    (microsoft.public.sqlserver.clients)
Loading