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]

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:

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.

[Trevor Best]

jo************@ yahoo.co.uk wrote:

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.

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]

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

(snip)

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.

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\)]

"Hugo Kornelis" <hu**@perFact.R EMOVETHIS.info. INVALID> wrote in message
news:5h******** *************** *********@4ax.c om...

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

(snip)

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.
Hi Trevor,

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.

Very handy.


--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:

"Hugo Kornelis" <hu**@perFact.R EMOVETHIS.info. INVALID> wrote in message
news:5h******** *************** *********@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.

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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Sat, 10 Jun 2006 12:51:23 +0000 (UTC), Erland Sommarskog wrote:

Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:

"Hugo Kornelis" <hu**@perFact.R EMOVETHIS.info. INVALID> wrote in message
news:5h******** *************** *********@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.

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.

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\)]

"Hugo Kornelis" <hu**@perFact.R EMOVETHIS.info. INVALID> wrote in message
news:9j******** *************** *********@4ax.c om...

On Sat, 10 Jun 2006 12:51:23 +0000 (UTC), Erland Sommarskog wrote:

Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:

"Hugo Kornelis" <hu**@perFact.R EMOVETHIS.info. INVALID> wrote in message
news:5h******** *************** *********@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.
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.

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.

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. :-)


--
Hugo Kornelis, SQL Server MVP