Backup log - will it truncate?

[Trevor Best]

According to BOL
<---
BACKUP LOG
Specifies a backup of the transaction log only. The log is backed up
from the last successfully executed LOG backup to the current end of
the log. Once the log is backed up, the space may be truncated when no
longer required by replication or active transactions.
--->

Does this mean I could do something else to truncate the log or is it
saying that the backup log command may truncate the log if it feels
like it?

I want ot get a log from a client site onto my server for analysis but
I want ot make absolute certain that my backing up the log on their
server won't truncate it there.

[johnbandettini@yahoo.co.uk]

Re: Backup log - will it truncate?

Trevor

A backup of the transaction log will truncate the transaction log. All
completed transactions will be removed.

What are you planing to do with the transaction log? There is not a lot
you can do with out a matching database. As far as I am aware,
Lumigent's Log reader is the only product that can read the transaction
log.

Regards

John

Trevor Best wrote:[color=blue]
> According to BOL
> <---
> BACKUP LOG
> Specifies a backup of the transaction log only. The log is backed up
> from the last successfully executed LOG backup to the current end of
> the log. Once the log is backed up, the space may be truncated when no
> longer required by replication or active transactions.
> --->
>
> Does this mean I could do something else to truncate the log or is it
> saying that the backup log command may truncate the log if it feels
> like it?
>
> I want ot get a log from a client site onto my server for analysis but
> I want ot make absolute certain that my backing up the log on their
> server won't truncate it there.[/color]

[Trevor Best]

Re: Backup log - will it truncate?


johnbandettini@ yahoo.co.uk wrote:[color=blue]
> Trevor
>
> A backup of the transaction log will truncate the transaction log. All
> completed transactions will be removed.
>
> What are you planing to do with the transaction log? There is not a lot
> you can do with out a matching database. As far as I am aware,
> Lumigent's Log reader is the only product that can read the transaction
> log.[/color]

Hi John, thanks for the reply.

I do intend to view the log, I'm using SQLLog Rescue from
www.red-gate.com. I can connect to their server over a VPN but it took
hours to read the log and got a general network error during the
process. What I want to do is restore the database on my server c/w
logs so I can view them here.

I know I can grab the data files and attach them on my end but I would
have to wait until tonight when people are off line to free up the
files.

[Hugo Kornelis]

Re: Backup log - will it truncate?

On 9 Jun 2006 02:35:01 -0700, Trevor Best wrote:

(snip)[color=blue]
>I want ot get a log from a client site onto my server for analysis but
>I want ot make absolute certain that my backing up the log on their
>server won't truncate it there.[/color]

Hi Trevor,

BACKUP LOG <databasename >
TO DISK = 'x:\y\z\logback up.bak'
WITH COPY_ONLY;

--
Hugo Kornelis, SQL Server MVP

[Greg D. Moore \(Strider\)]

Re: Backup log - will it truncate?


"Hugo Kornelis" <hugo@perFact.R EMOVETHIS.info. INVALID> wrote in message
news:5hmi82pfl0 2iqnhelegm0v515 dbdm1qpid@4ax.c om...[color=blue]
> On 9 Jun 2006 02:35:01 -0700, Trevor Best wrote:
>
> (snip)[color=green]
> >I want ot get a log from a client site onto my server for analysis but
> >I want ot make absolute certain that my backing up the log on their
> >server won't truncate it there.[/color]
>
> Hi Trevor,
>
> BACKUP LOG <databasename >
> TO DISK = 'x:\y\z\logback up.bak'
> WITH COPY_ONLY;[/color]

Is this a SQL 2005 specific command? I've never seen it before.

Very handy.

[color=blue]
>
> --
> Hugo Kornelis, SQL Server MVP[/color]

[Erland Sommarskog]

Re: Backup log - will it truncate?

Greg D. Moore (Strider) (mooregr_delete th1s@greenms.co m) writes:[color=blue]
> "Hugo Kornelis" <hugo@perFact.R EMOVETHIS.info. INVALID> wrote in message
> news:5hmi82pfl0 2iqnhelegm0v515 dbdm1qpid@4ax.c om...[color=green]
>> BACKUP LOG <databasename >
>> TO DISK = 'x:\y\z\logback up.bak'
>> WITH COPY_ONLY;[/color]
>
> Is this a SQL 2005 specific command? I've never seen it before.[/color]

Yes, that's a new addition (which I neither I had noticed until Hugo
posted about it). It appears to be a "NO_TRUNCAT E light". That is,
NO_TRUNCATE also waives the requirement that the database should be
accessible etc and is mainly intended for emergency situations.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Hugo Kornelis]

Re: Backup log - will it truncate?

On Sat, 10 Jun 2006 12:51:23 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
>Greg D. Moore (Strider) (mooregr_delete th1s@greenms.co m) writes:[color=green]
>> "Hugo Kornelis" <hugo@perFact.R EMOVETHIS.info. INVALID> wrote in message
>> news:5hmi82pfl0 2iqnhelegm0v515 dbdm1qpid@4ax.c om...[color=darkred]
>>> BACKUP LOG <databasename >
>>> TO DISK = 'x:\y\z\logback up.bak'
>>> WITH COPY_ONLY;[/color]
>>
>> Is this a SQL 2005 specific command? I've never seen it before.[/color]
>
>Yes, that's a new addition (which I neither I had noticed until Hugo
>posted about it). It appears to be a "NO_TRUNCAT E light". That is,
>NO_TRUNCATE also waives the requirement that the database should be
>accessible etc and is mainly intended for emergency situations.[/color]

Hi Erland,

That's right. The major advantage of COPY_ONLY over NO_TRUNCATE is (IMO)
that this option also works on full and incremental backups. It's a
great way to quickly get a backup of a DB to do some tests or
trouble-shooting on without disrupting the backup schema.

--
Hugo Kornelis, SQL Server MVP

[Greg D. Moore \(Strider\)]

Re: Backup log - will it truncate?


"Hugo Kornelis" <hugo@perFact.R EMOVETHIS.info. INVALID> wrote in message
news:9juo821f71 b7ah782onh5lbot re5qrgie1@4ax.c om...[color=blue]
> On Sat, 10 Jun 2006 12:51:23 +0000 (UTC), Erland Sommarskog wrote:
>[color=green]
> >Greg D. Moore (Strider) (mooregr_delete th1s@greenms.co m) writes:[color=darkred]
> >> "Hugo Kornelis" <hugo@perFact.R EMOVETHIS.info. INVALID> wrote in message
> >> news:5hmi82pfl0 2iqnhelegm0v515 dbdm1qpid@4ax.c om...
> >>> BACKUP LOG <databasename >
> >>> TO DISK = 'x:\y\z\logback up.bak'
> >>> WITH COPY_ONLY;
> >>
> >> Is this a SQL 2005 specific command? I've never seen it before.[/color]
> >
> >Yes, that's a new addition (which I neither I had noticed until Hugo
> >posted about it). It appears to be a "NO_TRUNCAT E light". That is,
> >NO_TRUNCATE also waives the requirement that the database should be
> >accessible etc and is mainly intended for emergency situations.[/color]
>
> Hi Erland,
>
> That's right. The major advantage of COPY_ONLY over NO_TRUNCATE is (IMO)
> that this option also works on full and incremental backups. It's a
> great way to quickly get a backup of a DB to do some tests or
> trouble-shooting on without disrupting the backup schema.[/color]

Yeah, that's why I was thinking it would be so useful.

I'll have to keep it in mind for when we upgrade to SQL 2005.

(Still have to have THAT particular debate with the finance folks who pay
the bills. :-)

[color=blue]
>
> --
> Hugo Kornelis, SQL Server MVP[/color]