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
> >>
> >>
>
>