log shipping question on two databases

Hi,
I created 2 log shipping plans at the same time, for database dbA and dbB
respectively, to ship logs to antother database server.
All the log transaction schedule and location are the same.
However, only log shipping plan for dbA succeed.
Is there any restriction on log shipping for 2 databases at the same time?
Thanks for help.
Jason
No, there is no such restriction. You can can configure two databases from
the same instance for log shipping.
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jason Huang" wrote:

> Hi,
> I created 2 log shipping plans at the same time, for database dbA and dbB
> respectively, to ship logs to antother database server.
> All the log transaction schedule and location are the same.
> However, only log shipping plan for dbA succeed.
> Is there any restriction on log shipping for 2 databases at the same time?
> Thanks for help.
>
> Jason
>
>

log shipping question on two databases

Hi,
I created 2 log shipping plans at the same time, for database dbA and dbB
respectively, to ship logs to antother database server.
All the log transaction schedule and location are the same.
However, only log shipping plan for dbA succeed.
Is there any restriction on log shipping for 2 databases at the same time?
Thanks for help.
JasonNo, there is no such restriction. You can can configure two databases from
the same instance for log shipping.
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jason Huang" wrote:

> Hi,
> I created 2 log shipping plans at the same time, for database dbA and dbB
> respectively, to ship logs to antother database server.
> All the log transaction schedule and location are the same.
> However, only log shipping plan for dbA succeed.
> Is there any restriction on log shipping for 2 databases at the same time?
> Thanks for help.
>
> Jason
>
>

log shipping question on two databases

Hi,
I created 2 log shipping plans at the same time, for database dbA and dbB
respectively, to ship logs to antother database server.
All the log transaction schedule and location are the same.
However, only log shipping plan for dbA succeed.
Is there any restriction on log shipping for 2 databases at the same time?
Thanks for help.
JasonNo, there is no such restriction. You can can configure two databases from
the same instance for log shipping.
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jason Huang" wrote:
> Hi,
> I created 2 log shipping plans at the same time, for database dbA and dbB
> respectively, to ship logs to antother database server.
> All the log transaction schedule and location are the same.
> However, only log shipping plan for dbA succeed.
> Is there any restriction on log shipping for 2 databases at the same time?
> Thanks for help.
>
> Jason
>
>

Log shipping from 64-bit to 32-bit boxes?

My company is considering a new Itanium cluster in our production
environment. Our DR boxes are still 32-bit with no plans to replace
them with 64-bit boxes. We currently log ship our production DBs from
our 32-bit production clusters to our 32-bit DR cluster.
My boss asked me this week (as part of the Itanium considerations) if
we'll be able to still log ship the production DBs from our new 64-bit
SQL instances to our 32-bit DR SQL instances. After a little thought I
said it would probably work but I've never done it myself or read
anything from someone who had done it so I couldn't say definitively
that it would work. (I fired off the question to my TAM but haven't
heard back from him.)
The only potential problem I can think of would be during the restore
phase of the sync - will a backup from a 64-bit instance restore
properly to a 32-bit instance? Do they have different internal DB
structures like SQL 7.0 vs SQL 2000 for example? If so, will that
conversion happen automagically like SQL 7 -> 2000?
Has anyone done this before (log shipping from a primary 64-bit SQL
instance to a secondary 32-bit SQL instance)? Does it work? Is it
supported by Microsoft? Can the log shipping monitor run on a 32-bit
instance?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Never tried it yet but it should work as far as I know. You can take a 32 bit backup or detached file and restore it or attach it to a 64 bit and visa versa. So I see no reason why log shipping would not work. The internal structures do not change.
Andrew J. Kelly SQL MVP
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uH9URVImFHA.1148@.TK2MSFTNGP12.phx.gbl...
My company is considering a new Itanium cluster in our production environment. Our DR boxes are still 32-bit with no plans to replace them with 64-bit boxes. We currently log ship our production DBs from our 32-bit production clusters to our 32-bit DR cluster.
My boss asked me this week (as part of the Itanium considerations) if we'll be able to still log ship the production DBs from our new 64-bit SQL instances to our 32-bit DR SQL instances. After a little thought I said it would probably work but I've never done it myself or read anything from someone who had done it so I couldn't say definitively that it would work. (I fired off the question to my TAM but haven't heard back from him.)
The only potential problem I can think of would be during the restore phase of the sync - will a backup from a 64-bit instance restore properly to a 32-bit instance? Do they have different internal DB structures like SQL 7.0 vs SQL 2000 for example? If so, will that conversion happen automagically like SQL 7 -> 2000?
Has anyone done this before (log shipping from a primary 64-bit SQL instance to a secondary 32-bit SQL instance)? Does it work? Is it supported by Microsoft? Can the log shipping monitor run on a 32-bit instance?
mike hodgson
blog: http://sqlnerd.blogspot.com

Log shipping from 64-bit to 32-bit boxes?

My company is considering a new Itanium cluster in our production
environment. Our DR boxes are still 32-bit with no plans to replace
them with 64-bit boxes. We currently log ship our production DBs from
our 32-bit production clusters to our 32-bit DR cluster.
My boss asked me this week (as part of the Itanium considerations) if
we'll be able to still log ship the production DBs from our new 64-bit
SQL instances to our 32-bit DR SQL instances. After a little thought I
said it would probably work but I've never done it myself or read
anything from someone who had done it so I couldn't say definitively
that it would work. (I fired off the question to my TAM but haven't
heard back from him.)
The only potential problem I can think of would be during the restore
phase of the sync - will a backup from a 64-bit instance restore
properly to a 32-bit instance? Do they have different internal DB
structures like SQL 7.0 vs SQL 2000 for example? If so, will that
conversion happen automagically like SQL 7 -> 2000?
Has anyone done this before (log shipping from a primary 64-bit SQL
instance to a secondary 32-bit SQL instance)? Does it work? Is it
supported by Microsoft? Can the log shipping monitor run on a 32-bit
instance?
*mike hodgson*
blog: http://sqlnerd.blogspot.comNever tried it yet but it should work as far as I know. You can take a 32 b
it backup or detached file and restore it or attach it to a 64 bit and visa
versa. So I see no reason why log shipping would not work. The internal st
ructures do not change.
--
Andrew J. Kelly SQL MVP
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uH9
URVImFHA.1148@.TK2MSFTNGP12.phx.gbl...
My company is considering a new Itanium cluster in our production environmen
t. Our DR boxes are still 32-bit with no plans to replace them with 64-bit
boxes. We currently log ship our production DBs from our 32-bit production
clusters to our 32-bit DR cluster.
My boss asked me this week (as part of the Itanium considerations) if we'll
be able to still log ship the production DBs from our new 64-bit SQL instanc
es to our 32-bit DR SQL instances. After a little thought I said it would p
robably work but I've never done it myself or read anything from someone who
had done it so I couldn't say definitively that it would work. (I fired of
f the question to my TAM but haven't heard back from him.)
The only potential problem I can think of would be during the restore phase
of the sync - will a backup from a 64-bit instance restore properly to a 32-
bit instance? Do they have different internal DB structures like SQL 7.0 vs
SQL 2000 for example? If so, will that conversion happen automagically lik
e SQL 7 -> 2000?
Has anyone done this before (log shipping from a primary 64-bit SQL instance
to a secondary 32-bit SQL instance)? Does it work? Is it supported by Mic
rosoft? Can the log shipping monitor run on a 32-bit instance?
mike hodgson
blog: http://sqlnerd.blogspot.com

Log shipping and Replication

I am documenting our disaster recovery plans and I was wondering if anyone
has any experience with failing over to a log shipping secondary in a
replicated environment.
If I log ship all user databases, and msdb, and distribution to a secondary
server, all to an exact point in time (after disabling all agent jobs), will
replication work on the secondary box? What will break?
I'm thinking that as long as the CNAME is updated so the DNS servers point
to the new box, and the SYSSERVERS table is updated with the new LOCAL server
name, and the account that starts the SQL Server and owns all the replication
jobs is the same domain account, all should be good.
It just sounds too good to be true!
The publisher servername is hardcoded into several system replication
tables. You could try renaming the standby server after failover and
renaming the old server, then updating the sysservers table - this should
work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It is too good to be true. Basically what you have to do is enable sync with
backup on your publication database.
Then ship your msdb, distribution, published database to the standby server.
What will happen is your log reader will only read from the log before it
dumps which increases the latency. Now when you failover, you rename the
standby server with the name of the primary and it should pick up where it
left off if you enable the continue on data consistency profile. You may
need to issue sp_repldone incase the log reader is ahead of the distribution
database on the standby. This is somewhat documented in SQL 2000 BOL.
There are more options in SQL 2005. For example with a remote distributor
and if you use the PublisherFailoverPartner.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
news:16FD2787-A5C5-4492-8185-43BF85EB3380@.microsoft.com...
>I am documenting our disaster recovery plans and I was wondering if anyone
> has any experience with failing over to a log shipping secondary in a
> replicated environment.
> If I log ship all user databases, and msdb, and distribution to a
> secondary
> server, all to an exact point in time (after disabling all agent jobs),
> will
> replication work on the secondary box? What will break?
> I'm thinking that as long as the CNAME is updated so the DNS servers point
> to the new box, and the SYSSERVERS table is updated with the new LOCAL
> server
> name, and the account that starts the SQL Server and owns all the
> replication
> jobs is the same domain account, all should be good.
> It just sounds too good to be true!

Log Shipping and maintenance plans

BACKGROUND:
I'm a developer who has just recently been given the additional
responsibility of DBA, we don't have the resources to hire a full-time DBA
yet but we can no longer afford to treat our database so casually. I am
trying to setup a configuration which involves
Replication, Mirroring and Log Shipping. We have a primary database SQL00
where all data entry happens, then we use replication to publish the data to
3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers for
the public web site. We do nightly full backups. We want to add mirroring,
log shipping and a maintenance plan. We will have a 5th server (SQL04) which
will act as a mirror for the primary and a 6th server (SQL05) which will be
the monitor for mirroring. Then we will use log shipping for reporting as
well as a set of "warm" standby servers.
As I am reading about log shipping I understand that I can only have one job
which will backup the transaction log for the primary database which will of
course be the log shipping job. I don't want to have to keep the entire
history of transaction log backups around so I want to do regular full and
differential backups.
QUESTION:
Because I want to do full and differential backups on a regular scheduled
basis I want to know if running a full or differential backup will
invalidate the transaction log backups created by the log shipping job and
cause the log shipping restore job to fail on the secondary. I am assuming
that I should be able to do this, but I still don't fully understand how all
the different backup types work together so I want to make sure that I'm
doing things right.
Thanks in advance for any help,
MarkThe short answer to your question is no, full and differential backups on
the primary database will not invalidate the transaction logs being shipped.
In a log shipping scenario, as soon as the standby server receives the log
backup, it restores it.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data
> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers
> for the public web site. We do nightly full backups. We want to add
> mirroring, log shipping and a maintenance plan. We will have a 5th server
> (SQL04) which will act as a mirror for the primary and a 6th server
> (SQL05) which will be the monitor for mirroring. Then we will use log
> shipping for reporting as well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one
> job which will backup the transaction log for the primary database which
> will of course be the log shipping job. I don't want to have to keep the
> entire history of transaction log backups around so I want to do regular
> full and differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how
> all the different backup types work together so I want to make sure that
> I'm doing things right.
> Thanks in advance for any help,
> Mark
>|||Mark Miller wrote:
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data
to
> 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers fo
r
> the public web site. We do nightly full backups. We want to add mirroring,
> log shipping and a maintenance plan. We will have a 5th server (SQL04) whi
ch
> will act as a mirror for the primary and a 6th server (SQL05) which will b
e
> the monitor for mirroring. Then we will use log shipping for reporting as
> well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one j
ob
> which will backup the transaction log for the primary database which will
of
> course be the log shipping job. I don't want to have to keep the entire
> history of transaction log backups around so I want to do regular full and
> differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how a
ll
> the different backup types work together so I want to make sure that I'm
> doing things right.
> Thanks in advance for any help,
> Mark
>
Easy answer is "NO", running a full or diff backup does not disrupt the
transaction logs. That said however, since you already have replication
working, I question why you would want to switch to log-shipping for
your reporting needs. Replication is a much better option for a
reporting solution - log shipping makes the database unavailable for a
brief period while doing a restore.
You also state the desire to create a maintenance plan, I assume to
handle index fragmentation? You might be interested in this script:
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, James.
You're right, I went and tested this by runing a series of backups and then
restoring them in differnt sequence.
FULL BACKUP #1
add table
LOG BACKUP #1
insert rows in new table
FULL BACKUP #2
insert more rows
LOG BACKUP #2
I tried
RESTORE FULL #1
RESTORE LOG #1
RESTORE LOG #2
and I also tried
RESTORE FULL #2
RESTORE LOG #2
It didn't matter which order I restored them in I always had all the data in
the end.
Thank you for your help.
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
> The short answer to your question is no, full and differential backups on
> the primary database will not invalidate the transaction logs being
> shipped. In a log shipping scenario, as soon as the standby server
> receives the log backup, it restores it.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>|||One quick caveat - differential backups (which you mentioned in the first
post) do throw a potential wrench into the works. A differential is always
tied to the last full backup. It is possible to setup a plan (or make an
"out-of-backup-scheme" full backup to restore to a test database or
something) that invalidates a number of differential backups. If you need to
have those type of full backups, you can use the WITH COPY_ONLY clause in
the full backup command. This tell 2005 (and it only works on 2005) to
ignore this full backup as part of the recorded backup chain - essentially
preserving the differential backups.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
> Thanks, James.
> You're right, I went and tested this by runing a series of backups and
> then restoring them in differnt sequence.
> FULL BACKUP #1
> add table
> LOG BACKUP #1
> insert rows in new table
> FULL BACKUP #2
> insert more rows
> LOG BACKUP #2
> I tried
> RESTORE FULL #1
> RESTORE LOG #1
> RESTORE LOG #2
> and I also tried
> RESTORE FULL #2
> RESTORE LOG #2
> It didn't matter which order I restored them in I always had all the data
> in the end.
> Thank you for your help.
> Mark
> "James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
> message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
>|||Thanks, your answer is what I was looking for. To satisfy curiosity and
answer your question the answer is this:
The database is growing very large and much of the data in the largest
tables may never be updated again, but I have to still provide access to it
on the site. I would like to use table partitioning on the reporting
servers, but because of budget constraints we cannot afford Enterprise
edition. So I am filtering the replication which I hope will give me a
"partition" for current data. For older "archive" data I want to use log
shipping which I can configure to run on a less frequent schedule. I will
have multiple secondaries which will be restored on different schedules so I
will always have 2 which are available for reporting.
As far as maintenance goes I meant that I need to make sure I have regular
backups which I can move offsite incase of disaster.
Thanks,
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e0P7v5cqGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Mark Miller wrote:
> Easy answer is "NO", running a full or diff backup does not disrupt the
> transaction logs. That said however, since you already have replication
> working, I question why you would want to switch to log-shipping for your
> reporting needs. Replication is a much better option for a reporting
> solution - log shipping makes the database unavailable for a brief period
> while doing a restore.
> You also state the desire to create a maintenance plan, I assume to handle
> index fragmentation? You might be interested in this script:
> http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks, that will help a lot with "ad hoc" backups!
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:uPJUW$cqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> One quick caveat - differential backups (which you mentioned in the first
> post) do throw a potential wrench into the works. A differential is always
> tied to the last full backup. It is possible to setup a plan (or make an
> "out-of-backup-scheme" full backup to restore to a test database or
> something) that invalidates a number of differential backups. If you need
> to have those type of full backups, you can use the WITH COPY_ONLY clause
> in the full backup command. This tell 2005 (and it only works on 2005) to
> ignore this full backup as part of the recorded backup chain - essentially
> preserving the differential backups.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
>|||Mark Miller wrote:
> Thanks, your answer is what I was looking for. To satisfy curiosity and
> answer your question the answer is this:
> The database is growing very large and much of the data in the largest
> tables may never be updated again, but I have to still provide access to i
t
> on the site. I would like to use table partitioning on the reporting
> servers, but because of budget constraints we cannot afford Enterprise
> edition. So I am filtering the replication which I hope will give me a
> "partition" for current data. For older "archive" data I want to use log
> shipping which I can configure to run on a less frequent schedule. I will
> have multiple secondaries which will be restored on different schedules so
I
> will always have 2 which are available for reporting.
> As far as maintenance goes I meant that I need to make sure I have regular
> backups which I can move offsite incase of disaster.
> Thanks,
> Mark
>
Hi Mark
You'll have to keep in mind that Log Shipping isn't available in SQL2000
Standard edition - you need Enterprise Edition for that.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
> Mark Miller wrote:
> Hi Mark
> You'll have to keep in mind that Log Shipping isn't available in SQL2000
> Standard edition - you need Enterprise Edition for that.
>
Or write your own - it's not too difficult, and easier to troubleshoot...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Log Shipping and maintenance plans

BACKGROUND:
I'm a developer who has just recently been given the additional
responsibility of DBA, we don't have the resources to hire a full-time DBA
yet but we can no longer afford to treat our database so casually. I am
trying to setup a configuration which involves
Replication, Mirroring and Log Shipping. We have a primary database SQL00
where all data entry happens, then we use replication to publish the data to
3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers for
the public web site. We do nightly full backups. We want to add mirroring,
log shipping and a maintenance plan. We will have a 5th server (SQL04) which
will act as a mirror for the primary and a 6th server (SQL05) which will be
the monitor for mirroring. Then we will use log shipping for reporting as
well as a set of "warm" standby servers.
As I am reading about log shipping I understand that I can only have one job
which will backup the transaction log for the primary database which will of
course be the log shipping job. I don't want to have to keep the entire
history of transaction log backups around so I want to do regular full and
differential backups.
QUESTION:
Because I want to do full and differential backups on a regular scheduled
basis I want to know if running a full or differential backup will
invalidate the transaction log backups created by the log shipping job and
cause the log shipping restore job to fail on the secondary. I am assuming
that I should be able to do this, but I still don't fully understand how all
the different backup types work together so I want to make sure that I'm
doing things right.
Thanks in advance for any help,
MarkThe short answer to your question is no, full and differential backups on
the primary database will not invalidate the transaction logs being shipped.
In a log shipping scenario, as soon as the standby server receives the log
backup, it restores it.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data
> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers
> for the public web site. We do nightly full backups. We want to add
> mirroring, log shipping and a maintenance plan. We will have a 5th server
> (SQL04) which will act as a mirror for the primary and a 6th server
> (SQL05) which will be the monitor for mirroring. Then we will use log
> shipping for reporting as well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one
> job which will backup the transaction log for the primary database which
> will of course be the log shipping job. I don't want to have to keep the
> entire history of transaction log backups around so I want to do regular
> full and differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how
> all the different backup types work together so I want to make sure that
> I'm doing things right.
> Thanks in advance for any help,
> Mark
>|||Mark Miller wrote:
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data to
> 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers for
> the public web site. We do nightly full backups. We want to add mirroring,
> log shipping and a maintenance plan. We will have a 5th server (SQL04) which
> will act as a mirror for the primary and a 6th server (SQL05) which will be
> the monitor for mirroring. Then we will use log shipping for reporting as
> well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one job
> which will backup the transaction log for the primary database which will of
> course be the log shipping job. I don't want to have to keep the entire
> history of transaction log backups around so I want to do regular full and
> differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how all
> the different backup types work together so I want to make sure that I'm
> doing things right.
> Thanks in advance for any help,
> Mark
>
Easy answer is "NO", running a full or diff backup does not disrupt the
transaction logs. That said however, since you already have replication
working, I question why you would want to switch to log-shipping for
your reporting needs. Replication is a much better option for a
reporting solution - log shipping makes the database unavailable for a
brief period while doing a restore.
You also state the desire to create a maintenance plan, I assume to
handle index fragmentation? You might be interested in this script:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, James.
You're right, I went and tested this by runing a series of backups and then
restoring them in differnt sequence.
FULL BACKUP #1
add table
LOG BACKUP #1
insert rows in new table
FULL BACKUP #2
insert more rows
LOG BACKUP #2
I tried
RESTORE FULL #1
RESTORE LOG #1
RESTORE LOG #2
and I also tried
RESTORE FULL #2
RESTORE LOG #2
It didn't matter which order I restored them in I always had all the data in
the end.
Thank you for your help.
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
> The short answer to your question is no, full and differential backups on
> the primary database will not invalidate the transaction logs being
> shipped. In a log shipping scenario, as soon as the standby server
> receives the log backup, it restores it.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually. I
>> am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database SQL00
>> where all data entry happens, then we use replication to publish the data
>> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting"
>> servers for the public web site. We do nightly full backups. We want to
>> add mirroring, log shipping and a maintenance plan. We will have a 5th
>> server (SQL04) which will act as a mirror for the primary and a 6th
>> server (SQL05) which will be the monitor for mirroring. Then we will use
>> log shipping for reporting as well as a set of "warm" standby servers.
>> As I am reading about log shipping I understand that I can only have one
>> job which will backup the transaction log for the primary database which
>> will of course be the log shipping job. I don't want to have to keep the
>> entire history of transaction log backups around so I want to do regular
>> full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular scheduled
>> basis I want to know if running a full or differential backup will
>> invalidate the transaction log backups created by the log shipping job
>> and cause the log shipping restore job to fail on the secondary. I am
>> assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want to
>> make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
>|||One quick caveat - differential backups (which you mentioned in the first
post) do throw a potential wrench into the works. A differential is always
tied to the last full backup. It is possible to setup a plan (or make an
"out-of-backup-scheme" full backup to restore to a test database or
something) that invalidates a number of differential backups. If you need to
have those type of full backups, you can use the WITH COPY_ONLY clause in
the full backup command. This tell 2005 (and it only works on 2005) to
ignore this full backup as part of the recorded backup chain - essentially
preserving the differential backups.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
> Thanks, James.
> You're right, I went and tested this by runing a series of backups and
> then restoring them in differnt sequence.
> FULL BACKUP #1
> add table
> LOG BACKUP #1
> insert rows in new table
> FULL BACKUP #2
> insert more rows
> LOG BACKUP #2
> I tried
> RESTORE FULL #1
> RESTORE LOG #1
> RESTORE LOG #2
> and I also tried
> RESTORE FULL #2
> RESTORE LOG #2
> It didn't matter which order I restored them in I always had all the data
> in the end.
> Thank you for your help.
> Mark
> "James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
> message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
>> The short answer to your question is no, full and differential backups on
>> the primary database will not invalidate the transaction logs being
>> shipped. In a log shipping scenario, as soon as the standby server
>> receives the log backup, it restores it.
>> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
>> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually. I
>> am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database
>> SQL00 where all data entry happens, then we use replication to publish
>> the data to 3 other servers (SQL01, SQ02, SQL03) which serve as
>> "reporting" servers for the public web site. We do nightly full backups.
>> We want to add mirroring, log shipping and a maintenance plan. We will
>> have a 5th server (SQL04) which will act as a mirror for the primary and
>> a 6th server (SQL05) which will be the monitor for mirroring. Then we
>> will use log shipping for reporting as well as a set of "warm" standby
>> servers.
>> As I am reading about log shipping I understand that I can only have one
>> job which will backup the transaction log for the primary database which
>> will of course be the log shipping job. I don't want to have to keep the
>> entire history of transaction log backups around so I want to do regular
>> full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular
>> scheduled basis I want to know if running a full or differential backup
>> will invalidate the transaction log backups created by the log shipping
>> job and cause the log shipping restore job to fail on the secondary. I
>> am assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want to
>> make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
>>
>|||Thanks, your answer is what I was looking for. To satisfy curiosity and
answer your question the answer is this:
The database is growing very large and much of the data in the largest
tables may never be updated again, but I have to still provide access to it
on the site. I would like to use table partitioning on the reporting
servers, but because of budget constraints we cannot afford Enterprise
edition. So I am filtering the replication which I hope will give me a
"partition" for current data. For older "archive" data I want to use log
shipping which I can configure to run on a less frequent schedule. I will
have multiple secondaries which will be restored on different schedules so I
will always have 2 which are available for reporting.
As far as maintenance goes I meant that I need to make sure I have regular
backups which I can move offsite incase of disaster.
Thanks,
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e0P7v5cqGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Mark Miller wrote:
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually. I
>> am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database SQL00
>> where all data entry happens, then we use replication to publish the data
>> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting"
>> servers for the public web site. We do nightly full backups. We want to
>> add mirroring, log shipping and a maintenance plan. We will have a 5th
>> server (SQL04) which will act as a mirror for the primary and a 6th
>> server (SQL05) which will be the monitor for mirroring. Then we will use
>> log shipping for reporting as well as a set of "warm" standby servers.
>> As I am reading about log shipping I understand that I can only have one
>> job which will backup the transaction log for the primary database which
>> will of course be the log shipping job. I don't want to have to keep the
>> entire history of transaction log backups around so I want to do regular
>> full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular scheduled
>> basis I want to know if running a full or differential backup will
>> invalidate the transaction log backups created by the log shipping job
>> and cause the log shipping restore job to fail on the secondary. I am
>> assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want to
>> make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
> Easy answer is "NO", running a full or diff backup does not disrupt the
> transaction logs. That said however, since you already have replication
> working, I question why you would want to switch to log-shipping for your
> reporting needs. Replication is a much better option for a reporting
> solution - log shipping makes the database unavailable for a brief period
> while doing a restore.
> You also state the desire to create a maintenance plan, I assume to handle
> index fragmentation? You might be interested in this script:
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks, that will help a lot with "ad hoc" backups!
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:uPJUW$cqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> One quick caveat - differential backups (which you mentioned in the first
> post) do throw a potential wrench into the works. A differential is always
> tied to the last full backup. It is possible to setup a plan (or make an
> "out-of-backup-scheme" full backup to restore to a test database or
> something) that invalidates a number of differential backups. If you need
> to have those type of full backups, you can use the WITH COPY_ONLY clause
> in the full backup command. This tell 2005 (and it only works on 2005) to
> ignore this full backup as part of the recorded backup chain - essentially
> preserving the differential backups.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
>> Thanks, James.
>> You're right, I went and tested this by runing a series of backups and
>> then restoring them in differnt sequence.
>> FULL BACKUP #1
>> add table
>> LOG BACKUP #1
>> insert rows in new table
>> FULL BACKUP #2
>> insert more rows
>> LOG BACKUP #2
>> I tried
>> RESTORE FULL #1
>> RESTORE LOG #1
>> RESTORE LOG #2
>> and I also tried
>> RESTORE FULL #2
>> RESTORE LOG #2
>> It didn't matter which order I restored them in I always had all the data
>> in the end.
>> Thank you for your help.
>> Mark
>> "James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
>> message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
>> The short answer to your question is no, full and differential backups
>> on the primary database will not invalidate the transaction logs being
>> shipped. In a log shipping scenario, as soon as the standby server
>> receives the log backup, it restores it.
>> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
>> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually.
>> I am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database
>> SQL00 where all data entry happens, then we use replication to publish
>> the data to 3 other servers (SQL01, SQ02, SQL03) which serve as
>> "reporting" servers for the public web site. We do nightly full
>> backups. We want to add mirroring, log shipping and a maintenance plan.
>> We will have a 5th server (SQL04) which will act as a mirror for the
>> primary and a 6th server (SQL05) which will be the monitor for
>> mirroring. Then we will use log shipping for reporting as well as a set
>> of "warm" standby servers.
>> As I am reading about log shipping I understand that I can only have
>> one job which will backup the transaction log for the primary database
>> which will of course be the log shipping job. I don't want to have to
>> keep the entire history of transaction log backups around so I want to
>> do regular full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular
>> scheduled basis I want to know if running a full or differential backup
>> will invalidate the transaction log backups created by the log shipping
>> job and cause the log shipping restore job to fail on the secondary. I
>> am assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want
>> to make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
>>
>>
>|||Mark Miller wrote:
> Thanks, your answer is what I was looking for. To satisfy curiosity and
> answer your question the answer is this:
> The database is growing very large and much of the data in the largest
> tables may never be updated again, but I have to still provide access to it
> on the site. I would like to use table partitioning on the reporting
> servers, but because of budget constraints we cannot afford Enterprise
> edition. So I am filtering the replication which I hope will give me a
> "partition" for current data. For older "archive" data I want to use log
> shipping which I can configure to run on a less frequent schedule. I will
> have multiple secondaries which will be restored on different schedules so I
> will always have 2 which are available for reporting.
> As far as maintenance goes I meant that I need to make sure I have regular
> backups which I can move offsite incase of disaster.
> Thanks,
> Mark
>
Hi Mark
You'll have to keep in mind that Log Shipping isn't available in SQL2000
Standard edition - you need Enterprise Edition for that.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
> Mark Miller wrote:
>> Thanks, your answer is what I was looking for. To satisfy curiosity
>> and answer your question the answer is this:
>> The database is growing very large and much of the data in the largest
>> tables may never be updated again, but I have to still provide access
>> to it on the site. I would like to use table partitioning on the
>> reporting servers, but because of budget constraints we cannot afford
>> Enterprise edition. So I am filtering the replication which I hope
>> will give me a "partition" for current data. For older "archive" data
>> I want to use log shipping which I can configure to run on a less
>> frequent schedule. I will have multiple secondaries which will be
>> restored on different schedules so I will always have 2 which are
>> available for reporting.
>> As far as maintenance goes I meant that I need to make sure I have
>> regular backups which I can move offsite incase of disaster.
>> Thanks,
>> Mark
> Hi Mark
> You'll have to keep in mind that Log Shipping isn't available in SQL2000
> Standard edition - you need Enterprise Edition for that.
>
Or write your own - it's not too difficult, and easier to troubleshoot...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Log Shipping (sort of) with Standard Edition

I have about 30 Databases on a Production SQL Server STANDARD Edition 2000k Instance.

All Backups are performed using SQL Server Maintenance plans (Full Nightly backups and periodic Xaction log backups during business hours)

I would like the ability to copy the FULL Backups to another Server - perhaps on a nightly basis - and then restore them to this other Server as a means of having some sort of Disaster Recovery.

I know with Enterprise Edition, you can set up linked servers and do log shipping or replication, but I don't have that luxury here. I also don't have access to the MS Backoffice Resource Kit - which I understand has some canned scripts to do what I want to do here.

My problem really is that because I don't have control over the backup filenames, how to I automate the restore process on the second Server?

any help would be much appreciated.You could try using the Scripting.FileSystemObject to perform a directory listing of the files in a particular directory. Then use a DO...LOOP to scroll through the list, copying the file and then restoring it to a remote server.

Alternatively, SQL keeps track of the file names used for backups in the msdb database. Look at the following tables:

backupfile
backupmediafamily
backupmediaset
backupset

Finally, you might just consider having the backups run out over the network to a remote server. In the event the primary node fails, you could restore the backups to an alternate server pretty quickly. Just remember that you also need to script off logins, jobs and save DTS packages (as binary files). You then also need to "exercise" your disaster recovery plan on a periodic basis to validate/verify your processes.

Regards,

hmscott|||Thank you for your reply!

I am actually already doing backups locally as well as to another Server on the Network - My problem was how to 'programatically' restore these backups on the remote server.

I found this script - posted by someone else on another forum...

It is SWEET!!!! As long as my backups are in a certain format and all in the same directory, this store procedure will check the folder for the newest .bak file for a specific database and restore it!!!!

So now what I have done is set up a maintenance plan on my prod server to do a full set of backups to this 'network' server - then I just schedule this stored procedure to run on the 'network' server and my databases are automatically refreshed..

Thanks again for your help|||Hi,
Can you let me know where you got that script...
Looking to do something similar.

Thanks,
-jmr|||I would also like to see that script.

Log Shipping & Query Plans

I have noticed that on our setup that every time the Log Shipping restore
happens, dm_exec_cached_plans and dm_exec_query_stats get completly whiped
out. Is this normal?
We are running on Sql Server 2005 Enterprise Edition SP1 on Windows Server
2003 R2 EEHi Bryan,
This is the expected behavior based on the current design.
We are considering of changing the design to allow the presevarion of
cached plans after a restore, in the next release of SQL Server.
Regards,
Leo
"Bryan Aldrich" <BryanAldrich@.discussions.microsoft.com> wrote in message
news:F1F94B7D-8664-4658-B73D-6B7137B9B618@.microsoft.com...
>I have noticed that on our setup that every time the Log Shipping restore
> happens, dm_exec_cached_plans and dm_exec_query_stats get completly whiped
> out. Is this normal?
> We are running on Sql Server 2005 Enterprise Edition SP1 on Windows Server
> 2003 R2 EE|||Thanks
"Leo Giakoumakis [MS]" wrote:
> Hi Bryan,
> This is the expected behavior based on the current design.
> We are considering of changing the design to allow the presevarion of
> cached plans after a restore, in the next release of SQL Server.
> Regards,
> Leo
>
> "Bryan Aldrich" <BryanAldrich@.discussions.microsoft.com> wrote in message
> news:F1F94B7D-8664-4658-B73D-6B7137B9B618@.microsoft.com...
> >I have noticed that on our setup that every time the Log Shipping restore
> > happens, dm_exec_cached_plans and dm_exec_query_stats get completly whiped
> > out. Is this normal?
> >
> > We are running on Sql Server 2005 Enterprise Edition SP1 on Windows Server
> > 2003 R2 EE
>
>

Log Shipping & Query Plans

I have noticed that on our setup that every time the Log Shipping restore
happens, dm_exec_cached_plans and dm_exec_query_stats get completly whiped
out. Is this normal?
We are running on Sql Server 2005 Enterprise Edition SP1 on Windows Server
2003 R2 EE
Hi Bryan,
This is the expected behavior based on the current design.
We are considering of changing the design to allow the presevarion of
cached plans after a restore, in the next release of SQL Server.
Regards,
Leo
"Bryan Aldrich" <BryanAldrich@.discussions.microsoft.com> wrote in message
news:F1F94B7D-8664-4658-B73D-6B7137B9B618@.microsoft.com...
>I have noticed that on our setup that every time the Log Shipping restore
> happens, dm_exec_cached_plans and dm_exec_query_stats get completly whiped
> out. Is this normal?
> We are running on Sql Server 2005 Enterprise Edition SP1 on Windows Server
> 2003 R2 EE
|||Thanks
"Leo Giakoumakis [MS]" wrote:

> Hi Bryan,
> This is the expected behavior based on the current design.
> We are considering of changing the design to allow the presevarion of
> cached plans after a restore, in the next release of SQL Server.
> Regards,
> Leo
>
> "Bryan Aldrich" <BryanAldrich@.discussions.microsoft.com> wrote in message
> news:F1F94B7D-8664-4658-B73D-6B7137B9B618@.microsoft.com...
>
>

Log Shipping & Query Plans

I have noticed that on our setup that every time the Log Shipping restore
happens, dm_exec_cached_plans and dm_exec_query_stats get completly whiped
out. Is this normal?
We are running on Sql Server 2005 Enterprise Edition SP1 on Windows Server
2003 R2 EEHi Bryan,
This is the expected behavior based on the current design.
We are considering of changing the design to allow the presevarion of
cached plans after a restore, in the next release of SQL Server.
Regards,
Leo
"Bryan Aldrich" <BryanAldrich@.discussions.microsoft.com> wrote in message
news:F1F94B7D-8664-4658-B73D-6B7137B9B618@.microsoft.com...
>I have noticed that on our setup that every time the Log Shipping restore
> happens, dm_exec_cached_plans and dm_exec_query_stats get completly whiped
> out. Is this normal?
> We are running on Sql Server 2005 Enterprise Edition SP1 on Windows Server
> 2003 R2 EE|||Thanks
"Leo Giakoumakis [MS]" wrote:

> Hi Bryan,
> This is the expected behavior based on the current design.
> We are considering of changing the design to allow the presevarion of
> cached plans after a restore, in the next release of SQL Server.
> Regards,
> Leo
>
> "Bryan Aldrich" <BryanAldrich@.discussions.microsoft.com> wrote in message
> news:F1F94B7D-8664-4658-B73D-6B7137B9B618@.microsoft.com...
>
>