Backing Up Issue

My transaction log is growing really big few hundred MB.
However before I do transaction log backup, I read that I
need to do a full database backup at least once. Also
clearing the transaction log before you backup the
database results in a smaller backup of the full database
I should have done earlier when the log was small.
My question is : Does it mean that I have no other way
except to back up the database with the full transaction
log.. ?
After the full database backup, I will then do a
transaction log backup
You could truncate the log and immediately perform a full database backup.
Given that you're never performed a full database backup anyway, this should
be fine in your situation.
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
followed by DBCC SHRINKFILE .. command for the log file.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>
|||When the database is in FULL recovery mode, the log will automatically
truncate until you do the first full db backup, because it has no value
until then...So your log should not be growing if you haven't made the first
db backup...
Peter's response is one of your options...
Long term, if you need to be able to recover all of the data.
Start scheduling a regular full database backup, followed by regular log
backups as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>
|||Thanks for the advice..
So even though I've not done even one full database
backup before, I can still safely 1) Backup the log with
truncate only, followed by 2) DBCC Shrinkfile for the log
3) Then do a full database backup...
Can I say it is safe to NOT do at least one full database
backup before doing a log ?
|||> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
Yes, since the log is useless without a full database backup. I believe SQL
Server makes this check too i.e. prevents you from making a trx log backup
if no full backup has ever been done.

> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
What do you mean by 'doing a log'?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:49aa01c4803a$aa694260$a501280a@.phx.gbl...
> Thanks for the advice..
> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
>
>
|||Hi Peter,
Thanks for you reply to the first part of my question..
[vbcol=seagreen]
database
>What do you mean by 'doing a log'?
I mean 'doing a transaction log backup' Please excuse me
for my type error.
|||If you change from Simple to Full or Bulk-logged recovery, or if you are
intializing log shipping, you must take a full database backup before
starting transaction log backups. The database annot be in the Simple
recovery model if you want to take transaction log backups.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:564501c4810b$02640830$a501280a@.phx.gbl...
> Hi Peter,
> Thanks for you reply to the first part of my question..
> database
> I mean 'doing a transaction log backup' Please excuse me
> for my type error.
>