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