Hi,
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.
Yep...you can. Just like how you do it in a non-clustered instance. The only
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:
> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>
|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:[vbcol=seagreen]
> Yep...you can. Just like how you do it in a non-clustered instance. The only
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:
Showing posts with label clustered. Show all posts
Showing posts with label clustered. Show all posts
Friday, March 30, 2012
Log Shipping with clustered database.
Hi,
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.Yep...you can. Just like how you do it in a non-clustered instance. The only
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
--
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:
> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:
> Yep...you can. Just like how you do it in a non-clustered instance. The only
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:
> > Hi,
> >
> > We are running SQL Server Failover cluster in Active/Passive mode.
> >
> > Now, we want to setup DR server for this clustered database using
> > Log shipping.
> >
> > But my concern is, can we do log shipping with clustered database ?
> > and the answer of this question is Yes then Please let me know How ?
> >
> >
> > Thanks & Regards,
> > Sajid C.
> >
> >
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.Yep...you can. Just like how you do it in a non-clustered instance. The only
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
--
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:
> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:
> Yep...you can. Just like how you do it in a non-clustered instance. The only
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:
> > Hi,
> >
> > We are running SQL Server Failover cluster in Active/Passive mode.
> >
> > Now, we want to setup DR server for this clustered database using
> > Log shipping.
> >
> > But my concern is, can we do log shipping with clustered database ?
> > and the answer of this question is Yes then Please let me know How ?
> >
> >
> > Thanks & Regards,
> > Sajid C.
> >
> >
Log Shipping with clustered database.
Hi,
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.Yep...you can. Just like how you do it in a non-clustered instance. The onl
y
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
--
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:
> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:[vbcol=seagreen]
> Yep...you can. Just like how you do it in a non-clustered instance. The o
nly
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment
.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:
>
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.Yep...you can. Just like how you do it in a non-clustered instance. The onl
y
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
--
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:
> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:[vbcol=seagreen]
> Yep...you can. Just like how you do it in a non-clustered instance. The o
nly
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment
.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:
>
Log Shipping vs. Clustered Environment
I am embarking on a project to upgrade our SQL server environment. I'm curious for some opinions on the relative benefits of Log Shipping versus a Clustered environment and you have any experience that has helped form your opinion on the issue?
Thanks,
MarcMarc,
I think it depends on what you want to achieve. Log Shipping is useful if you want use a copy of your primary database for read-only queries as it reduces the load on your primary database, although this is not possible if you run Log Shipping continuously as the log shipping restore job requires that there be no connections in the database being log shipped to.
Also, you are building in tolerance against data loss - assuming that the database you are log shipping to is on a different physical disk or server.
You have to have a database in Full or Bulk Logged recovery mode before you can log ship it.
If you have a lot of inserts, updates and deletes occuring on your log shipped database, the size of the log shipping backups and restores can be considerably - something to think about if your network bandwidth is low. In this case you may want to look at using 3rd party log shipping software such as Quest Litespeed which can compress the backups.
Clustering is more of a protection against O/S failures or server failures. When the SQL Server service on the active node fails, is terminated unexpectedly or is shutdown, that is detected by the cluster service and it fails SQL Server (and any other cluster-aware services) over to the inactive node which then becomes the active node.
In SQL 2005 you also have the option of Mirroring which can be combined with clustering...although Microsoft didn't initially support the use of mirroring in a Production environment (so why have it as a feature you may ask!). Not sure whether it is now supported since SP2.
Regards
Lempster|||Lempster -
Can you tell me more about using clustering with mirroring. I'm exploring all of the options (clustering, mirroring, log shipping and replication) and am seeing the benefits of each. I can image the benefit of using clustering with mirroring, but I can't get my head around the actual implementation.
The clusters have to be pointing to a shared storage device. How do you then set it up to mirror? If you have a cluster [Cluster] with [Box A] and [Box B]. Can you set the mirror up to point to the [Cluster] as the principal so that it's not really worried about the individual boxes?
During my research on mirroring, it looks like Microsoft started supporting mirroring with SP1.
Thanks,
Marc|||Marc,
I've not implemented 'clustered mirroring' (for want of a better term), but there's a fair bit about it in SQL 2005 BOL. Essentially, you'd have the principal and mirror on different clusters. The following is taken from BOL:
"The principal server and mirror server both reside on clusters, with the principal server running on the virtual server of one cluster and the mirror server running on the virtual server of a different cluster.
If a cluster failover makes a principal server temporarily unavailable, client connections are disconnected from the database. After the cluster failover completes, clients can reconnect to the principal server on the same cluster or on a different cluster (or on an unclustered computer), depending on the operating mode."
Regards
Lempster
Thanks,
MarcMarc,
I think it depends on what you want to achieve. Log Shipping is useful if you want use a copy of your primary database for read-only queries as it reduces the load on your primary database, although this is not possible if you run Log Shipping continuously as the log shipping restore job requires that there be no connections in the database being log shipped to.
Also, you are building in tolerance against data loss - assuming that the database you are log shipping to is on a different physical disk or server.
You have to have a database in Full or Bulk Logged recovery mode before you can log ship it.
If you have a lot of inserts, updates and deletes occuring on your log shipped database, the size of the log shipping backups and restores can be considerably - something to think about if your network bandwidth is low. In this case you may want to look at using 3rd party log shipping software such as Quest Litespeed which can compress the backups.
Clustering is more of a protection against O/S failures or server failures. When the SQL Server service on the active node fails, is terminated unexpectedly or is shutdown, that is detected by the cluster service and it fails SQL Server (and any other cluster-aware services) over to the inactive node which then becomes the active node.
In SQL 2005 you also have the option of Mirroring which can be combined with clustering...although Microsoft didn't initially support the use of mirroring in a Production environment (so why have it as a feature you may ask!). Not sure whether it is now supported since SP2.
Regards
Lempster|||Lempster -
Can you tell me more about using clustering with mirroring. I'm exploring all of the options (clustering, mirroring, log shipping and replication) and am seeing the benefits of each. I can image the benefit of using clustering with mirroring, but I can't get my head around the actual implementation.
The clusters have to be pointing to a shared storage device. How do you then set it up to mirror? If you have a cluster [Cluster] with [Box A] and [Box B]. Can you set the mirror up to point to the [Cluster] as the principal so that it's not really worried about the individual boxes?
During my research on mirroring, it looks like Microsoft started supporting mirroring with SP1.
Thanks,
Marc|||Marc,
I've not implemented 'clustered mirroring' (for want of a better term), but there's a fair bit about it in SQL 2005 BOL. Essentially, you'd have the principal and mirror on different clusters. The following is taken from BOL:
"The principal server and mirror server both reside on clusters, with the principal server running on the virtual server of one cluster and the mirror server running on the virtual server of a different cluster.
If a cluster failover makes a principal server temporarily unavailable, client connections are disconnected from the database. After the cluster failover completes, clients can reconnect to the principal server on the same cluster or on a different cluster (or on an unclustered computer), depending on the operating mode."
Regards
Lempster
Log Shipping Transaction Logs.
Hi,
We currently have a couple a large Databases running on SQL 2000 SP3 Clustered Windows 2000 SP3 environment.
Log Shipping is enabled for both databases shipping to a Standalone SQL 2000 SP3 Windows 2000 SP3 box.
Log Shipping occurs every 15 mins with the Transaction Files on average being no more than 500KB in size. However, every now and then a Transaction Log comes through and it can be as big as 3.52GB.
Not sure why this is happening. Anyone got any ideas?
Regards
Paul TowlerIs this after substantial activity or reindexing?|||i think auto shrink or some other job is enabled
if iam right, the auto shrink or job gets activated and the step goes thru
which will obviously create transaction file with huge size.|||Thanks for your replies.
There is a Job to Optimise the Database which does re-index the database. I presume all the indexing changes count as changes and therefore shipped as one big Transacton file.
Hopefully there is a way to prevent these hugh Transaction Files without turning off the indexing job.
Regards
Paul Towlersql
We currently have a couple a large Databases running on SQL 2000 SP3 Clustered Windows 2000 SP3 environment.
Log Shipping is enabled for both databases shipping to a Standalone SQL 2000 SP3 Windows 2000 SP3 box.
Log Shipping occurs every 15 mins with the Transaction Files on average being no more than 500KB in size. However, every now and then a Transaction Log comes through and it can be as big as 3.52GB.
Not sure why this is happening. Anyone got any ideas?
Regards
Paul TowlerIs this after substantial activity or reindexing?|||i think auto shrink or some other job is enabled
if iam right, the auto shrink or job gets activated and the step goes thru
which will obviously create transaction file with huge size.|||Thanks for your replies.
There is a Job to Optimise the Database which does re-index the database. I presume all the indexing changes count as changes and therefore shipped as one big Transacton file.
Hopefully there is a way to prevent these hugh Transaction Files without turning off the indexing job.
Regards
Paul Towlersql
Wednesday, March 21, 2012
Log Shipping on Clustered SQL Server
Hi,
I'm trying to implement Log Shipping on a 2 Node SQL Cluster for certain
DB's.
As far as I understand, I've to share the folder where the transaction logs
are backed.
My questions are:
a) I cannot configure the DB to back the TRN's on a network drive, it says
"Not Allowed" The drive should be local to the SQL server.
b) If I create a share on the existing Drive where the TRN's are getting
back, that share will be lost if a failover occurs.
c) Is it like you've to write a script that will move the TRN's to a shared
Location.
I'd really appreciate any feedback on this.
Thanks a lot.
Vick
You can back up to a UNC network share.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
If you insist on storing the backup files to local cluster disks, you need
to create a separate file share that is a clustered resource. That way, any
instance can access it regardless of the current host node.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Vick Shaw" <nospam@.msft.com> wrote in message
news:eeJaOvdUHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm trying to implement Log Shipping on a 2 Node SQL Cluster for certain
> DB's.
> As far as I understand, I've to share the folder where the transaction
> logs
> are backed.
> My questions are:
> a) I cannot configure the DB to back the TRN's on a network drive, it says
> "Not Allowed" The drive should be local to the SQL server.
> b) If I create a share on the existing Drive where the TRN's are getting
> back, that share will be lost if a failover occurs.
> c) Is it like you've to write a script that will move the TRN's to a
> shared
> Location.
> I'd really appreciate any feedback on this.
> Thanks a lot.
> Vick
>
|||Thanks Geoff..
So which is the right way to do it. Should I back it to UNC or should I
create a separate file share?
Thanks again
Vick
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23pKfx7dUHHA.4744@.TK2MSFTNGP02.phx.gbl...
> You can back up to a UNC network share.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/kb/555128
> If you insist on storing the backup files to local cluster disks, you need
> to create a separate file share that is a clustered resource. That way,
any[vbcol=seagreen]
> instance can access it regardless of the current host node.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Vick Shaw" <nospam@.msft.com> wrote in message
> news:eeJaOvdUHHA.1636@.TK2MSFTNGP02.phx.gbl...
says
>
|||Hi Geoff,
When I tried to put a UNC in the TRN log file path it gave me an error:
Error:5110 \\domain\filename.ldf is on a network device not supported for
database files.
ALTER DATABSE failed...
"Vick Shaw" <nospam@.msft.com> wrote in message
news:OS77SMgUHHA.1212@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Thanks Geoff..
> So which is the right way to do it. Should I back it to UNC or should I
> create a separate file share?
> Thanks again
> Vick
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23pKfx7dUHHA.4744@.TK2MSFTNGP02.phx.gbl...
need[vbcol=seagreen]
> any
certain[vbcol=seagreen]
> says
getting
>
|||Slight misunderstanding.
I think you are trying to store an actual transaction log file, not a
transaction log backup, on a network share. At least, that is what the
error is reporting.
Which version of SQL are you using and what tool are you using? Can you
send the exact steps you are using to generate this error.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Vick Shaw" <nospam@.msft.com> wrote in message
news:uuJjFWgUHHA.1212@.TK2MSFTNGP03.phx.gbl...
> Hi Geoff,
> When I tried to put a UNC in the TRN log file path it gave me an error:
> Error:5110 \\domain\filename.ldf is on a network device not supported for
> database files.
> ALTER DATABSE failed...
> "Vick Shaw" <nospam@.msft.com> wrote in message
> news:OS77SMgUHHA.1212@.TK2MSFTNGP03.phx.gbl...
> need
> certain
> getting
>
I'm trying to implement Log Shipping on a 2 Node SQL Cluster for certain
DB's.
As far as I understand, I've to share the folder where the transaction logs
are backed.
My questions are:
a) I cannot configure the DB to back the TRN's on a network drive, it says
"Not Allowed" The drive should be local to the SQL server.
b) If I create a share on the existing Drive where the TRN's are getting
back, that share will be lost if a failover occurs.
c) Is it like you've to write a script that will move the TRN's to a shared
Location.
I'd really appreciate any feedback on this.
Thanks a lot.
Vick
You can back up to a UNC network share.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
If you insist on storing the backup files to local cluster disks, you need
to create a separate file share that is a clustered resource. That way, any
instance can access it regardless of the current host node.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Vick Shaw" <nospam@.msft.com> wrote in message
news:eeJaOvdUHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm trying to implement Log Shipping on a 2 Node SQL Cluster for certain
> DB's.
> As far as I understand, I've to share the folder where the transaction
> logs
> are backed.
> My questions are:
> a) I cannot configure the DB to back the TRN's on a network drive, it says
> "Not Allowed" The drive should be local to the SQL server.
> b) If I create a share on the existing Drive where the TRN's are getting
> back, that share will be lost if a failover occurs.
> c) Is it like you've to write a script that will move the TRN's to a
> shared
> Location.
> I'd really appreciate any feedback on this.
> Thanks a lot.
> Vick
>
|||Thanks Geoff..
So which is the right way to do it. Should I back it to UNC or should I
create a separate file share?
Thanks again
Vick
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23pKfx7dUHHA.4744@.TK2MSFTNGP02.phx.gbl...
> You can back up to a UNC network share.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/kb/555128
> If you insist on storing the backup files to local cluster disks, you need
> to create a separate file share that is a clustered resource. That way,
any[vbcol=seagreen]
> instance can access it regardless of the current host node.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Vick Shaw" <nospam@.msft.com> wrote in message
> news:eeJaOvdUHHA.1636@.TK2MSFTNGP02.phx.gbl...
says
>
|||Hi Geoff,
When I tried to put a UNC in the TRN log file path it gave me an error:
Error:5110 \\domain\filename.ldf is on a network device not supported for
database files.
ALTER DATABSE failed...
"Vick Shaw" <nospam@.msft.com> wrote in message
news:OS77SMgUHHA.1212@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Thanks Geoff..
> So which is the right way to do it. Should I back it to UNC or should I
> create a separate file share?
> Thanks again
> Vick
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23pKfx7dUHHA.4744@.TK2MSFTNGP02.phx.gbl...
need[vbcol=seagreen]
> any
certain[vbcol=seagreen]
> says
getting
>
|||Slight misunderstanding.
I think you are trying to store an actual transaction log file, not a
transaction log backup, on a network share. At least, that is what the
error is reporting.
Which version of SQL are you using and what tool are you using? Can you
send the exact steps you are using to generate this error.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Vick Shaw" <nospam@.msft.com> wrote in message
news:uuJjFWgUHHA.1212@.TK2MSFTNGP03.phx.gbl...
> Hi Geoff,
> When I tried to put a UNC in the TRN log file path it gave me an error:
> Error:5110 \\domain\filename.ldf is on a network device not supported for
> database files.
> ALTER DATABSE failed...
> "Vick Shaw" <nospam@.msft.com> wrote in message
> news:OS77SMgUHHA.1212@.TK2MSFTNGP03.phx.gbl...
> need
> certain
> getting
>
Friday, February 24, 2012
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)