Hi,
Can separate monitor server for SQL 2000 Log Shipping
high-availability configuration be SQL 2000 Standard
Edtion?
Or must be SQL 2000 Server Enterprise Edition?
Thanks for answers
MilanYou cant have log shipping for SQL Standard, not quite sure what ur
question is?
Regards
J
"Milan Ojstersek" <milan.ojstersek@.hermes-plus.si> wrote in message
news:083101c34c5d$25531fe0$a601280a@.phx.gbl...
> Hi,
> Can separate monitor server for SQL 2000 Log Shipping
> high-availability configuration be SQL 2000 Standard
> Edtion?
> Or must be SQL 2000 Server Enterprise Edition?
> Thanks for answers
> Milan
Showing posts with label separate. Show all posts
Showing posts with label separate. Show all posts
Wednesday, March 21, 2012
Friday, February 24, 2012
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.
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 olderSQL 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 theadditional 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.
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. :)
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 a separate backup plan
Log Shipping and a separate backup plan
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the log
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping.
They simply use a full back to restore the database on the reporting servers.
How can we resolve this issue?
Thank you,
ktfSounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf|||No can do. Log backups need to be restored in sequence, so log shipping will influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
also have database mirroring, which would probably be a better option compared to log shipping.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the log
> shipping has its own tlog backup files and is not touching the full backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails max
> after a couple of days, so currently, our developers cannot use log shipping.
> They simply use a full back to restore the database on the reporting servers.
> How can we resolve this issue?
> Thank you,
> ktf|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace these
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
> also have database mirroring, which would probably be a better option compared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> > Log Shipping and a separate backup plan
> >
> > I have a clustered sql 2k enterprise with mission critical application
> > database. A group of people has managed a disaster recovery plan using sql
> > backup full nightly backup and hourly tlog backup. The other group, the
> > developers, need to have hot standby server using log shipping to move the
> > data from the production server to the reporting servers.
> >
> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> > separately. That means the separate jobs and separate file backups so the log
> > shipping has its own tlog backup files and is not touching the full backup/
> > tlog backup files of the main backup plan.
> >
> > I also have a different software to replicate data from the active site to a
> > disaster site real time.
> >
> > We tried to have these two plans running but the log shipping job fails max
> > after a couple of days, so currently, our developers cannot use log shipping.
> > They simply use a full back to restore the database on the reporting servers.
> >
> > How can we resolve this issue?
> >
> > Thank you,
> > ktf
>|||OK... I'm not sure if you are asking anything? All I'm saying is that you have to restore the log
backups in sequence.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace these
> file every 2-3 weeks because they get corrupted so every time I replace these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
>> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
>> also have database mirroring, which would probably be a better option compared to log shipping.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>> > Log Shipping and a separate backup plan
>> >
>> > I have a clustered sql 2k enterprise with mission critical application
>> > database. A group of people has managed a disaster recovery plan using sql
>> > backup full nightly backup and hourly tlog backup. The other group, the
>> > developers, need to have hot standby server using log shipping to move the
>> > data from the production server to the reporting servers.
>> >
>> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
>> > separately. That means the separate jobs and separate file backups so the log
>> > shipping has its own tlog backup files and is not touching the full backup/
>> > tlog backup files of the main backup plan.
>> >
>> > I also have a different software to replicate data from the active site to a
>> > disaster site real time.
>> >
>> > We tried to have these two plans running but the log shipping job fails max
>> > after a couple of days, so currently, our developers cannot use log shipping.
>> > They simply use a full back to restore the database on the reporting servers.
>> >
>> > How can we resolve this issue?
>> >
>> > Thank you,
>> > ktf
>>|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>> No can do. Log backups need to be restored in sequence, so log shipping
>> will influence your other
>> backup strategy. In 2005, you have a COPY_ONLY option of database and log
>> backups, but there you
>> also have database mirroring, which would probably be a better option
>> compared to log shipping.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>> > Log Shipping and a separate backup plan
>> >
>> > I have a clustered sql 2k enterprise with mission critical application
>> > database. A group of people has managed a disaster recovery plan using
>> > sql
>> > backup full nightly backup and hourly tlog backup. The other group, the
>> > developers, need to have hot standby server using log shipping to move
>> > the
>> > data from the production server to the reporting servers.
>> >
>> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be
>> > ran
>> > separately. That means the separate jobs and separate file backups so
>> > the log
>> > shipping has its own tlog backup files and is not touching the full
>> > backup/
>> > tlog backup files of the main backup plan.
>> >
>> > I also have a different software to replicate data from the active site
>> > to a
>> > disaster site real time.
>> >
>> > We tried to have these two plans running but the log shipping job fails
>> > max
>> > after a couple of days, so currently, our developers cannot use log
>> > shipping.
>> > They simply use a full back to restore the database on the reporting
>> > servers.
>> >
>> > How can we resolve this issue?
>> >
>> > Thank you,
>> > ktf
>>|||Tibor,
I have no problem on backup and restore and I have already restored the
production after a disaster occurred in 3 hours. They are very critical that
is why I do not want to touch my backups.
All I wanted is to have a totally separated log shipping job in place
pointed to an intermediate database (read only) as the target and after that
we are all covered from there.
Geoff,
I have these question and I donâ't want you feel I am acting as a handicap in
here but maybe you are giving me the best answers.
What is UNC in here?
TLog backups to UNC share with datetime filename suffixes.
What toll you use to open Tlog file to see the filename and datetime please?
Do you use a vb app to recognize the filename and datatime?
Do you have sample code for this operation specifically to create this kind
of jobs, control and clean as you said?
About log ship for reporting would you send me the script to
kamitf@.yahoo.com please?
Where is the best place to see log shipping custom code.
Thanks in advance to both of you,
V/r
ktf
"Geoff N. Hiten" wrote:
> Here is the quick version of how I do it:
> Everything goes to disk files first via UNC share. Files never get
> overwritten.
> Full backups nightly. Full backups weekly to a separate folder. Weekly
> backups copied to tape with monthly or quarterly archive.
> TLog backups to UNC share with datetime filename suffixes. You can use the
> Maintenance Plan wizard to create these or "roll your own" code. I keep
> these for four to seven days and then clean them out. Again, the
> maintenance plan can do this or you can write your own directory clean out
> program.
> To log ship for reporting, I have a script that queries the MSDB tables on
> the source server for the log backup sequence. I then restore this on the
> target system(s). Note that I can run this restore script at any time so I
> can schedule around normal database use. Since I am using UNC shares, the
> paths are identical for backup and restore.
> To log ship for Disaster Recovery, I start with the same basic structure as
> for reporting, except I copy the files to a second file share that is
> located near the target server. I then add restore work items to a table on
> the target server. A scheduled job running on the target server grabs these
> and restores on schedule, usually with a 24-hour delay to avoid streaming an
> "oops" to the failover system. All the log shipping stuff is custom SQL
> code.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> > Tibor,
> >
> > The way I do backup is a full backup daily with overwrite and two tlog
> > backup hourly job for even and odd hours (23 tlog backups). I replace
> > these
> > file every 2-3 weeks because they get corrupted so every time I replace
> > these
> > files the tlog files will be different that is why I wanted these to be
> > separated.
> >
> >
> > Thank you in advance.
> >
> > ktf
> >
> > "Tibor Karaszi" wrote:
> >
> >> No can do. Log backups need to be restored in sequence, so log shipping
> >> will influence your other
> >> backup strategy. In 2005, you have a COPY_ONLY option of database and log
> >> backups, but there you
> >> also have database mirroring, which would probably be a better option
> >> compared to log shipping.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> >> > Log Shipping and a separate backup plan
> >> >
> >> > I have a clustered sql 2k enterprise with mission critical application
> >> > database. A group of people has managed a disaster recovery plan using
> >> > sql
> >> > backup full nightly backup and hourly tlog backup. The other group, the
> >> > developers, need to have hot standby server using log shipping to move
> >> > the
> >> > data from the production server to the reporting servers.
> >> >
> >> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be
> >> > ran
> >> > separately. That means the separate jobs and separate file backups so
> >> > the log
> >> > shipping has its own tlog backup files and is not touching the full
> >> > backup/
> >> > tlog backup files of the main backup plan.
> >> >
> >> > I also have a different software to replicate data from the active site
> >> > to a
> >> > disaster site real time.
> >> >
> >> > We tried to have these two plans running but the log shipping job fails
> >> > max
> >> > after a couple of days, so currently, our developers cannot use log
> >> > shipping.
> >> > They simply use a full back to restore the database on the reporting
> >> > servers.
> >> >
> >> > How can we resolve this issue?
> >> >
> >> > Thank you,
> >> > ktf
> >>
> >>
>
>
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the log
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping.
They simply use a full back to restore the database on the reporting servers.
How can we resolve this issue?
Thank you,
ktfSounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf|||No can do. Log backups need to be restored in sequence, so log shipping will influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
also have database mirroring, which would probably be a better option compared to log shipping.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the log
> shipping has its own tlog backup files and is not touching the full backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails max
> after a couple of days, so currently, our developers cannot use log shipping.
> They simply use a full back to restore the database on the reporting servers.
> How can we resolve this issue?
> Thank you,
> ktf|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace these
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
> also have database mirroring, which would probably be a better option compared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> > Log Shipping and a separate backup plan
> >
> > I have a clustered sql 2k enterprise with mission critical application
> > database. A group of people has managed a disaster recovery plan using sql
> > backup full nightly backup and hourly tlog backup. The other group, the
> > developers, need to have hot standby server using log shipping to move the
> > data from the production server to the reporting servers.
> >
> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> > separately. That means the separate jobs and separate file backups so the log
> > shipping has its own tlog backup files and is not touching the full backup/
> > tlog backup files of the main backup plan.
> >
> > I also have a different software to replicate data from the active site to a
> > disaster site real time.
> >
> > We tried to have these two plans running but the log shipping job fails max
> > after a couple of days, so currently, our developers cannot use log shipping.
> > They simply use a full back to restore the database on the reporting servers.
> >
> > How can we resolve this issue?
> >
> > Thank you,
> > ktf
>|||OK... I'm not sure if you are asking anything? All I'm saying is that you have to restore the log
backups in sequence.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace these
> file every 2-3 weeks because they get corrupted so every time I replace these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
>> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
>> also have database mirroring, which would probably be a better option compared to log shipping.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>> > Log Shipping and a separate backup plan
>> >
>> > I have a clustered sql 2k enterprise with mission critical application
>> > database. A group of people has managed a disaster recovery plan using sql
>> > backup full nightly backup and hourly tlog backup. The other group, the
>> > developers, need to have hot standby server using log shipping to move the
>> > data from the production server to the reporting servers.
>> >
>> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
>> > separately. That means the separate jobs and separate file backups so the log
>> > shipping has its own tlog backup files and is not touching the full backup/
>> > tlog backup files of the main backup plan.
>> >
>> > I also have a different software to replicate data from the active site to a
>> > disaster site real time.
>> >
>> > We tried to have these two plans running but the log shipping job fails max
>> > after a couple of days, so currently, our developers cannot use log shipping.
>> > They simply use a full back to restore the database on the reporting servers.
>> >
>> > How can we resolve this issue?
>> >
>> > Thank you,
>> > ktf
>>|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>> No can do. Log backups need to be restored in sequence, so log shipping
>> will influence your other
>> backup strategy. In 2005, you have a COPY_ONLY option of database and log
>> backups, but there you
>> also have database mirroring, which would probably be a better option
>> compared to log shipping.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>> > Log Shipping and a separate backup plan
>> >
>> > I have a clustered sql 2k enterprise with mission critical application
>> > database. A group of people has managed a disaster recovery plan using
>> > sql
>> > backup full nightly backup and hourly tlog backup. The other group, the
>> > developers, need to have hot standby server using log shipping to move
>> > the
>> > data from the production server to the reporting servers.
>> >
>> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be
>> > ran
>> > separately. That means the separate jobs and separate file backups so
>> > the log
>> > shipping has its own tlog backup files and is not touching the full
>> > backup/
>> > tlog backup files of the main backup plan.
>> >
>> > I also have a different software to replicate data from the active site
>> > to a
>> > disaster site real time.
>> >
>> > We tried to have these two plans running but the log shipping job fails
>> > max
>> > after a couple of days, so currently, our developers cannot use log
>> > shipping.
>> > They simply use a full back to restore the database on the reporting
>> > servers.
>> >
>> > How can we resolve this issue?
>> >
>> > Thank you,
>> > ktf
>>|||Tibor,
I have no problem on backup and restore and I have already restored the
production after a disaster occurred in 3 hours. They are very critical that
is why I do not want to touch my backups.
All I wanted is to have a totally separated log shipping job in place
pointed to an intermediate database (read only) as the target and after that
we are all covered from there.
Geoff,
I have these question and I donâ't want you feel I am acting as a handicap in
here but maybe you are giving me the best answers.
What is UNC in here?
TLog backups to UNC share with datetime filename suffixes.
What toll you use to open Tlog file to see the filename and datetime please?
Do you use a vb app to recognize the filename and datatime?
Do you have sample code for this operation specifically to create this kind
of jobs, control and clean as you said?
About log ship for reporting would you send me the script to
kamitf@.yahoo.com please?
Where is the best place to see log shipping custom code.
Thanks in advance to both of you,
V/r
ktf
"Geoff N. Hiten" wrote:
> Here is the quick version of how I do it:
> Everything goes to disk files first via UNC share. Files never get
> overwritten.
> Full backups nightly. Full backups weekly to a separate folder. Weekly
> backups copied to tape with monthly or quarterly archive.
> TLog backups to UNC share with datetime filename suffixes. You can use the
> Maintenance Plan wizard to create these or "roll your own" code. I keep
> these for four to seven days and then clean them out. Again, the
> maintenance plan can do this or you can write your own directory clean out
> program.
> To log ship for reporting, I have a script that queries the MSDB tables on
> the source server for the log backup sequence. I then restore this on the
> target system(s). Note that I can run this restore script at any time so I
> can schedule around normal database use. Since I am using UNC shares, the
> paths are identical for backup and restore.
> To log ship for Disaster Recovery, I start with the same basic structure as
> for reporting, except I copy the files to a second file share that is
> located near the target server. I then add restore work items to a table on
> the target server. A scheduled job running on the target server grabs these
> and restores on schedule, usually with a 24-hour delay to avoid streaming an
> "oops" to the failover system. All the log shipping stuff is custom SQL
> code.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> > Tibor,
> >
> > The way I do backup is a full backup daily with overwrite and two tlog
> > backup hourly job for even and odd hours (23 tlog backups). I replace
> > these
> > file every 2-3 weeks because they get corrupted so every time I replace
> > these
> > files the tlog files will be different that is why I wanted these to be
> > separated.
> >
> >
> > Thank you in advance.
> >
> > ktf
> >
> > "Tibor Karaszi" wrote:
> >
> >> No can do. Log backups need to be restored in sequence, so log shipping
> >> will influence your other
> >> backup strategy. In 2005, you have a COPY_ONLY option of database and log
> >> backups, but there you
> >> also have database mirroring, which would probably be a better option
> >> compared to log shipping.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> >> > Log Shipping and a separate backup plan
> >> >
> >> > I have a clustered sql 2k enterprise with mission critical application
> >> > database. A group of people has managed a disaster recovery plan using
> >> > sql
> >> > backup full nightly backup and hourly tlog backup. The other group, the
> >> > developers, need to have hot standby server using log shipping to move
> >> > the
> >> > data from the production server to the reporting servers.
> >> >
> >> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be
> >> > ran
> >> > separately. That means the separate jobs and separate file backups so
> >> > the log
> >> > shipping has its own tlog backup files and is not touching the full
> >> > backup/
> >> > tlog backup files of the main backup plan.
> >> >
> >> > I also have a different software to replicate data from the active site
> >> > to a
> >> > disaster site real time.
> >> >
> >> > We tried to have these two plans running but the log shipping job fails
> >> > max
> >> > after a couple of days, so currently, our developers cannot use log
> >> > shipping.
> >> > They simply use a full back to restore the database on the reporting
> >> > servers.
> >> >
> >> > How can we resolve this issue?
> >> >
> >> > Thank you,
> >> > ktf
> >>
> >>
>
>
Log Shipping and a separate backup plan
Log Shipping and a separate backup plan
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the log
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping.
They simply use a full back to restore the database on the reporting servers.
How can we resolve this issue?
Thank you,
ktf
Sounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf
|||No can do. Log backups need to be restored in sequence, so log shipping will influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
also have database mirroring, which would probably be a better option compared to log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the log
> shipping has its own tlog backup files and is not touching the full backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails max
> after a couple of days, so currently, our developers cannot use log shipping.
> They simply use a full back to restore the database on the reporting servers.
> How can we resolve this issue?
> Thank you,
> ktf
|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace these
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
> also have database mirroring, which would probably be a better option compared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>
|||OK... I'm not sure if you are asking anything? All I'm saying is that you have to restore the log
backups in sequence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace these
> file every 2-3 weeks because they get corrupted so every time I replace these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the log
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping.
They simply use a full back to restore the database on the reporting servers.
How can we resolve this issue?
Thank you,
ktf
Sounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf
|||No can do. Log backups need to be restored in sequence, so log shipping will influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
also have database mirroring, which would probably be a better option compared to log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the log
> shipping has its own tlog backup files and is not touching the full backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails max
> after a couple of days, so currently, our developers cannot use log shipping.
> They simply use a full back to restore the database on the reporting servers.
> How can we resolve this issue?
> Thank you,
> ktf
|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace these
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
> also have database mirroring, which would probably be a better option compared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>
|||OK... I'm not sure if you are asking anything? All I'm saying is that you have to restore the log
backups in sequence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace these
> file every 2-3 weeks because they get corrupted so every time I replace these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
Log Shipping and a separate backup plan
Log Shipping and a separate backup plan
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the lo
g
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping
.
They simply use a full back to restore the database on the reporting servers
.
How can we resolve this issue?
Thank you,
ktfSounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf|||No can do. Log backups need to be restored in sequence, so log shipping will
influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log ba
ckups, but there you
also have database mirroring, which would probably be a better option compar
ed to log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
log
> shipping has its own tlog backup files and is not touching the full backup
/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails ma
x
> after a couple of days, so currently, our developers cannot use log shippi
ng.
> They simply use a full back to restore the database on the reporting serve
rs.
> How can we resolve this issue?
> Thank you,
> ktf|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace thes
e
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping wi
ll influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log
backups, but there you
> also have database mirroring, which would probably be a better option comp
ared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>|||OK... I'm not sure if you are asking anything? All I'm saying is that you ha
ve to restore the log
backups in sequence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace thes
e
> file every 2-3 weeks because they get corrupted so every time I replace th
ese
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the lo
g
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping
.
They simply use a full back to restore the database on the reporting servers
.
How can we resolve this issue?
Thank you,
ktfSounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf|||No can do. Log backups need to be restored in sequence, so log shipping will
influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log ba
ckups, but there you
also have database mirroring, which would probably be a better option compar
ed to log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
log
> shipping has its own tlog backup files and is not touching the full backup
/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails ma
x
> after a couple of days, so currently, our developers cannot use log shippi
ng.
> They simply use a full back to restore the database on the reporting serve
rs.
> How can we resolve this issue?
> Thank you,
> ktf|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace thes
e
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping wi
ll influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log
backups, but there you
> also have database mirroring, which would probably be a better option comp
ared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>|||OK... I'm not sure if you are asking anything? All I'm saying is that you ha
ve to restore the log
backups in sequence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace thes
e
> file every 2-3 weeks because they get corrupted so every time I replace th
ese
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>
Subscribe to:
Posts (Atom)