Log Shipping transaction log backup files have wrong timestamp

I'm experiencing a weird problem with log shipping in SQL 2005.

I've setup Log Shipping for a production database between two sites. The standby database is being updated correctly and everything seems to be working as expected but for one detail: the name of the transaction log backups are generated with an UTC timestamp instead of my local timezone.

The the data below extracted from the backup history:

2007-06-23 17:30:00.000 D:\Backup\Databases\mydb\mydb_20070623073000.trn
2007-06-23 17:15:00.000 D:\Backup\Databases\mydb\mydb_20070623071500.trn
2007-06-23 17:00:00.000 D:\Backup\Databases\mydb\mydb_20070623070000.trn
2007-06-23 16:45:00.000 D:\Backup\Databases\mydb\mydb_20070623064500.trn

My timezone here is GMT+10.

Although it's not affecting Log Shipping, it's very confusing as the full backups have a timestamp in the local timezone!

Has anyone seen experienced something similar to this? Please see below my SQL details:

1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.3042.00
3 Language 1033 English (United States)
4 Platform NULL NT AMD64
5 Comments NULL NT AMD64
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT - 64 Bit
8 FileVersion NULL 2005.090.3042.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL ? Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft? is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 199360512 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 4 4
17 ProcessorActiveMask 4 f
18 ProcessorType 8664 NULL
19 PhysicalMemory 4095 4095 (4294037504)
20 Product ID NULL NULL

Thanks,
Andr

Hi Andre

The use of UTC timestamp for the log shipping files is by design and how log shipping is supposed to work. The main reason for this is so that there is no way timestamps can jump backward or forward when daylight savings time starts or finishes, for example. Log shipping uses the timestamps to work out if there is anything new to process and using UTC ensures this process works reliably.

Thanks, Mark

|||Thanks, Mark.

It would be good, though, that the maintenance plan's backup task had at least an option to use the same approach when naming backup files, for consistency purposes.

Is this feature documented in Books Online? I couldn't find any reference.

Thanks,

Andr

Log Shipping and BLOB data

Hi all,

It was my understanding (Please correct me if I'm wrong on this!) that
BLOB data actually reside on their own separate pages and a BLOB field
only holds a pointer to the location of the actual data, therefore the
BLOB data per se would not get written to the log, only the pointer
would be written.

If log shipping works by applying the transaction log to the standby
database, then what happens to the BLOB data?

Related question, how does transactional replication work? Is it also
based on the transaction log?

TIA,

EllenEllen K (ekaye2002@.yahoo.com) writes:
> It was my understanding (Please correct me if I'm wrong on this!) that
> BLOB data actually reside on their own separate pages and a BLOB field
> only holds a pointer to the location of the actual data, therefore the
> BLOB data per se would not get written to the log, only the pointer
> would be written.

A BLOB normally resides on its own page. However, this page is also
logged, else ROLLBACK TRANSACTION would be very tragic.

> If log shipping works by applying the transaction log to the standby
> database, then what happens to the BLOB data?

It's shipped as well.

> Related question, how does transactional replication work? Is it also
> based on the transaction log?

Yes. I don't remember the exact details, but basically log entries are
sent to the distributor which spreads them around. Note an important
feature of transacional replication is that what is a transaction in
the publisher, is also a transaction in the subscribers.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks so much. So I think I can use log shipping for my availability
/ disaster recovery plan, the BLOB data was my only worry.

Ellen :)|||Ellen K (ekaye2002@.yahoo.com) writes:
> Thanks so much. So I think I can use log shipping for my availability
> / disaster recovery plan, the BLOB data was my only worry.

It goes without saying that you have not completed the work with setting
up a recovery plan, until you actually have simulated a disaster recovery.
(And depending on your availability requirements, is something that should
be exercised with some frequency.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Absolutely.

Thanks again. :)

log shipping and BLOB data

Hi all,
It was my understanding (Please correct me if I'm wrong on this!) that
BLOB data actually reside on their own separate pages and a BLOB field
only holds a pointer to the location of the actual data, therefore the
BLOB data per se would not get written to the log, only the pointer
would be written.
If log shipping works by applying the transaction log to the standby
database, then what happens to the BLOB data?
Related question, how does transactional replication work? Is it also
based on the transaction log?
TIA,
Ellen> It was my understanding (Please correct me if I'm wrong on this!) that
> BLOB data actually reside on their own separate pages and a BLOB field
> only holds a pointer to the location of the actual data, therefore the
> BLOB data per se would not get written to the log, only the pointer
> would be written.
Text, ntext and image data may be stored on separate pages, depending on the
'text in row' table option. The default is to store only the text pointer.
However, regardless of the setting, data are written to the log

> Related question, how does transactional replication work? Is it also
> based on the transaction log?
Yes, transactional replication uses log data. See the Books Online for a
thorough description.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1118177847.585875.143740@.g44g2000cwa.googlegroups.com...
> Hi all,
>
> It was my understanding (Please correct me if I'm wrong on this!) that
> BLOB data actually reside on their own separate pages and a BLOB field
> only holds a pointer to the location of the actual data, therefore the
> BLOB data per se would not get written to the log, only the pointer
> would be written.
>
> If log shipping works by applying the transaction log to the standby
> database, then what happens to the BLOB data?
>
> Related question, how does transactional replication work? Is it also
> based on the transaction log?
>
> TIA,
>
> Ellen
>|||Thanks very much. I'm not sure where I got the idea that the BLOB data
wouldn't be logged, but it's been bothering me as I try to come up with
an availability / disaster recovery plan because the whole raison
d'=EAtre of one of our databases is to store BLOB data and although log
shipping otherwise seemed like the best choice, I was very worried
about this point. So thanks for putting my mind at ease. |||I should add that my comments are applicable to SQL Server 2000. In older
SQL Server versions, client apps could specify the NO LOG option on
WRITETEXT and UPDATETEXT statements if the 'select into/bulk copy' database
option was on and this would break the log backup sequence. This isn't an
issue in SQL Server 2000 when the database recovery model is FULL or
BULK_LOGGED (a requirement for log shipping).
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1118194929.139819.255330@.g43g2000cwa.googlegroups.com...
Thanks very much. I'm not sure where I got the idea that the BLOB data
wouldn't be logged, but it's been bothering me as I try to come up with
an availability / disaster recovery plan because the whole raison
d'tre of one of our databases is to store BLOB data and although log
shipping otherwise seemed like the best choice, I was very worried
about this point. So thanks for putting my mind at ease. |||We are using SQL Server 2000, no problem. But thanks for the
additional information.

log shipping and BLOB data

Hi all,
It was my understanding (Please correct me if I'm wrong on this!) that
BLOB data actually reside on their own separate pages and a BLOB field
only holds a pointer to the location of the actual data, therefore the
BLOB data per se would not get written to the log, only the pointer
would be written.
If log shipping works by applying the transaction log to the standby
database, then what happens to the BLOB data?
Related question, how does transactional replication work? Is it also
based on the transaction log?
TIA,
Ellen
> It was my understanding (Please correct me if I'm wrong on this!) that
> BLOB data actually reside on their own separate pages and a BLOB field
> only holds a pointer to the location of the actual data, therefore the
> BLOB data per se would not get written to the log, only the pointer
> would be written.
Text, ntext and image data may be stored on separate pages, depending on the
'text in row' table option. The default is to store only the text pointer.
However, regardless of the setting, data are written to the log

> Related question, how does transactional replication work? Is it also
> based on the transaction log?
Yes, transactional replication uses log data. See the Books Online for a
thorough description.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1118177847.585875.143740@.g44g2000cwa.googlegr oups.com...
> Hi all,
>
> It was my understanding (Please correct me if I'm wrong on this!) that
> BLOB data actually reside on their own separate pages and a BLOB field
> only holds a pointer to the location of the actual data, therefore the
> BLOB data per se would not get written to the log, only the pointer
> would be written.
>
> If log shipping works by applying the transaction log to the standby
> database, then what happens to the BLOB data?
>
> Related question, how does transactional replication work? Is it also
> based on the transaction log?
>
> TIA,
>
> Ellen
>
|||Thanks very much. I'm not sure where I got the idea that the BLOB data
wouldn't be logged, but it's been bothering me as I try to come up with
an availability / disaster recovery plan because the whole raison
d'=EAtre of one of our databases is to store BLOB data and although log
shipping otherwise seemed like the best choice, I was very worried
about this point. So thanks for putting my mind at ease.
|||I should add that my comments are applicable to SQL Server 2000. In older
SQL Server versions, client apps could specify the NO LOG option on
WRITETEXT and UPDATETEXT statements if the 'select into/bulk copy' database
option was on and this would break the log backup sequence. This isn't an
issue in SQL Server 2000 when the database recovery model is FULL or
BULK_LOGGED (a requirement for log shipping).
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1118194929.139819.255330@.g43g2000cwa.googlegr oups.com...
Thanks very much. I'm not sure where I got the idea that the BLOB data
wouldn't be logged, but it's been bothering me as I try to come up with
an availability / disaster recovery plan because the whole raison
d'tre of one of our databases is to store BLOB data and although log
shipping otherwise seemed like the best choice, I was very worried
about this point. So thanks for putting my mind at ease.
|||We are using SQL Server 2000, no problem. But thanks for the
additional information.

log shipping and BLOB data

Hi all,
It was my understanding (Please correct me if I'm wrong on this!) that
BLOB data actually reside on their own separate pages and a BLOB field
only holds a pointer to the location of the actual data, therefore the
BLOB data per se would not get written to the log, only the pointer
would be written.
If log shipping works by applying the transaction log to the standby
database, then what happens to the BLOB data?
Related question, how does transactional replication work? Is it also
based on the transaction log?
TIA,
Ellen> It was my understanding (Please correct me if I'm wrong on this!) that
> BLOB data actually reside on their own separate pages and a BLOB field
> only holds a pointer to the location of the actual data, therefore the
> BLOB data per se would not get written to the log, only the pointer
> would be written.
Text, ntext and image data may be stored on separate pages, depending on the
'text in row' table option. The default is to store only the text pointer.
However, regardless of the setting, data are written to the log
> Related question, how does transactional replication work? Is it also
> based on the transaction log?
Yes, transactional replication uses log data. See the Books Online for a
thorough description.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1118177847.585875.143740@.g44g2000cwa.googlegroups.com...
> Hi all,
>
> It was my understanding (Please correct me if I'm wrong on this!) that
> BLOB data actually reside on their own separate pages and a BLOB field
> only holds a pointer to the location of the actual data, therefore the
> BLOB data per se would not get written to the log, only the pointer
> would be written.
>
> If log shipping works by applying the transaction log to the standby
> database, then what happens to the BLOB data?
>
> Related question, how does transactional replication work? Is it also
> based on the transaction log?
>
> TIA,
>
> Ellen
>|||Thanks very much. I'm not sure where I got the idea that the BLOB data
wouldn't be logged, but it's been bothering me as I try to come up with
an availability / disaster recovery plan because the whole raison
d'=EAtre of one of our databases is to store BLOB data and although log
shipping otherwise seemed like the best choice, I was very worried
about this point. So thanks for putting my mind at ease. :)|||I should add that my comments are applicable to SQL Server 2000. In older
SQL Server versions, client apps could specify the NO LOG option on
WRITETEXT and UPDATETEXT statements if the 'select into/bulk copy' database
option was on and this would break the log backup sequence. This isn't an
issue in SQL Server 2000 when the database recovery model is FULL or
BULK_LOGGED (a requirement for log shipping).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1118194929.139819.255330@.g43g2000cwa.googlegroups.com...
Thanks very much. I'm not sure where I got the idea that the BLOB data
wouldn't be logged, but it's been bothering me as I try to come up with
an availability / disaster recovery plan because the whole raison
d'être of one of our databases is to store BLOB data and although log
shipping otherwise seemed like the best choice, I was very worried
about this point. So thanks for putting my mind at ease. :)|||We are using SQL Server 2000, no problem. But thanks for the
additional information. :)