How can I back up a log-shipped database?

Hi,
You can not backup a database or log that is standby mode
with regular backups.
As a work around, you have to restore the database and
then back it up.
Here is something you could use:
USE MASTER
RESTORE DATABASE DB_NAME
WITH RECOVERY
This changes the standby status to normal db use and then
you can back it up.
The only thing that I am not sure is what happens at the
next log shipped/restored because it depends how you have
it setup.
hth
DeeJay
>--Original Message--
>(SQL Server 2000, SP3a)
>Hello all!
>I've got a database that is the secondary server in a log-
shipped pair. Whenever I try
>and do a BACKUP on this database, I get an error message
that the database is in a
>READ-ONLY STANDBY mode.
>Is there any way to circumvent this, temporarily, and
make a database backup of a
>log-shipped database?
>Thanks!
>
>.
>Thanks DeeJay!
What we're thinking (if you'll humor us for a moment):
* Temporarily disable the Job that's responsible for processing the log-ship
ped
transaction logs.
* Change the status of the database to get it out of STANDBY mode (as per yo
ur
recommendation).
* Back up the database.
* Change the status of the database back to STANDBY (dunno how to do this ye
t).
* Re-enable the Job.
I'm not sure if this is advisable, though. For instance, what does the Log-
Shipping
Monitor service do? Is it sensitive to any of the proposed elements above?
Thanks for any additional help you can provide! :-)
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:3a6001c48f88$24958740$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> You can not backup a database or log that is standby mode
> with regular backups.
> As a work around, you have to restore the database and
> then back it up.
> Here is something you could use:
> USE MASTER
> RESTORE DATABASE DB_NAME
> WITH RECOVERY
> This changes the standby status to normal db use and then
> you can back it up.
> The only thing that I am not sure is what happens at the
> next log shipped/restored because it depends how you have
> it setup.
> hth
> DeeJay
> shipped pair. Whenever I try
> that the database is in a
> make a database backup of a|||Hi John,

> * Change the status of the database back to STANDBY (dunno how to do this yet).[/v
bcol]
No can do. The recovery procedures etc in SQL Server aren't written to handl
e this scenario, quite simply. Any
way you can grab the log backups already on the fallback machine?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.g
bl...[vbcol=seagreen]
> Thanks DeeJay!
> What we're thinking (if you'll humor us for a moment):
> * Temporarily disable the Job that's responsible for processing the log-sh
ipped
> transaction logs.
> * Change the status of the database to get it out of STANDBY mode (as per
your
> recommendation).
> * Back up the database.
> * Change the status of the database back to STANDBY (dunno how to do this
yet).
> * Re-enable the Job.
> I'm not sure if this is advisable, though. For instance, what does the Lo
g-Shipping
> Monitor service do? Is it sensitive to any of the proposed elements above
?
> Thanks for any additional help you can provide! :-)
>
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
>|||Thanks Tibor. I don't think that we have the main database backup available
from the
fallback machine.
Any way we can use an undocumented "flag" in some system table to toggle the
DB back in
STANDBY mode? ;-)
Just to give you the heads up: we've got our production databases log-shipp
ed into our
corporate network. If we can leverage these log-shipped databases, we won't
have to pay
the network price to copy from production again.
Thanks for any additional help you might be able to provide! :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Hi John,
>
> No can do. The recovery procedures etc in SQL Server aren't written to han
dle this
> scenario, quite simply. Any
> way you can grab the log backups already on the fallback machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
>|||You might want to test this out in dev first.
I am not sure if this is supported by MS or if your log
shipping will work properly again.
You might have to re-configure log-shipping.
DeeJay
>--Original Message--
>Thanks Tibor. I don't think that we have the main
database backup available from the
>fallback machine.
>Any way we can use an undocumented "flag" in some system
table to toggle the DB back in
>STANDBY mode? ;-)
>Just to give you the heads up: we've got our production
databases log-shipped into our
>corporate network. If we can leverage these log-shipped
databases, we won't have to pay
>the network price to copy from production again.
>Thanks for any additional help you might be able to
provide! :-)
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message
>news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
(dunno how to do this yet).[vbcol=seagreen]
aren't written to handle this[vbcol=seagreen]
fallback machine?[vbcol=seagreen]
processing the log-shipped[vbcol=seagreen]
STANDBY mode (as per your[vbcol=seagreen]
(dunno how to do this yet).[vbcol=seagreen]
instance, what does the Log-Shipping[vbcol=seagreen]
proposed elements above?[vbcol=seagreen]
mode[vbcol=seagreen]
and[vbcol=seagreen]
then[vbcol=seagreen]
the[vbcol=seagreen]
have[vbcol=seagreen]
a log-[vbcol=seagreen]
message[vbcol=seagreen]
>
>.
>|||> Any way we can use an undocumented "flag" in some system table to toggle the DB back in[vb
col=seagreen]
> STANDBY mode? ;-)[/vbcol]
Nope, none that I know of. Just think about it. When you bring a db out of s
tandby mode, you get the recovery
work persisted. Stuff has been rolled forward and rolled back. Period. How w
ould you be able to apply a later
log backup onto this, as the log records in that log backup are totally out-
of sync with the database you have
performed a permanent recovery on?
For this to work, MS would need to do some changes in how the recovery proce
ss work or give us some other
option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVER
Y).
Or MS would need to change SQL Server so it allow us to do a backup of a dat
abase in STANDBY mode. (And here
I'm too tired right now to consider what ramifications that would have on lo
g record sequencing and recovery
;-) ).
I know this question has been on the table before, so you might want to chec
k the archives to see if someone
came up with anything. I have a feeling that you are out of luck, though...
Perhaps you should opt for a home-grown log shipping solution, to give you b
etter control of handling of the
backup files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.ph
x.gbl...
> Thanks Tibor. I don't think that we have the main database backup availab
le from the
> fallback machine.
> Any way we can use an undocumented "flag" in some system table to toggle t
he DB back in
> STANDBY mode? ;-)
> Just to give you the heads up: we've got our production databases log-shi
pped into our
> corporate network. If we can leverage these log-shipped databases, we won
't have to pay
> the network price to copy from production again.
> Thanks for any additional help you might be able to provide! :-)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
>|||Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
I had *hoped* that, by temporarily suspending the log file processing, I cou
ld somehow get
the DB in a state where it was backup-able. But, if I'm understanding you c
orrectly, it
sounds as if, by virtue of performing a backup on the DB, I'd be "marking" t
he transaction
log in such a way as to be incompatible with the normal log files when they'
re later
resumed.
Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or woul
d that break
the whole log-shipping "linkage"?
Thanks again!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Nope, none that I know of. Just think about it. When you bring a db out of
standby mode,
> you get the recovery
> work persisted. Stuff has been rolled forward and rolled back. Period. How
would you be
> able to apply a later
> log backup onto this, as the log records in that log backup are totally ou
t-of sync with
> the database you have
> performed a permanent recovery on?
> For this to work, MS would need to do some changes in how the recovery pro
cess work or
> give us some other
> option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOV
ERY).
> Or MS would need to change SQL Server so it allow us to do a backup of a d
atabase in
> STANDBY mode. (And here
> I'm too tired right now to consider what ramifications that would have on
log record
> sequencing and recovery
> ;-) ).
> I know this question has been on the table before, so you might want to ch
eck the
> archives to see if someone
> came up with anything. I have a feeling that you are out of luck, though..
.
> Perhaps you should opt for a home-grown log shipping solution, to give you
better
> control of handling of the
> backup files?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.phx.gbl...
>|||I think that when you do recovery, log records are either removed or added (
possibly both) to the
transaction log. This means that a later log backup from the production data
base will not just be
able to add the log records to the log-shipped database, because the transac
tion log has been
changed. The LSN (log sequence numbers) doesn't match anymore.
I haven't tested whether you can detach and attach a database a database in
STANDBY mode. Give it a
try. If not, you might consider stopping SQL server and just grabbing the fi
les. Not supported and
not guaranteed that you can attach such files, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O991LI6jEHA.1348@.TK2MSFTNGP15.phx.
gbl...
> Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
> I had *hoped* that, by temporarily suspending the log file processing, I c
ould somehow get
> the DB in a state where it was backup-able. But, if I'm understanding you
correctly, it
> sounds as if, by virtue of performing a backup on the DB, I'd be "marking"
the transaction
> log in such a way as to be incompatible with the normal log files when the
y're later
> resumed.
> Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or wo
uld that break
> the whole log-shipping "linkage"?
> Thanks again!
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
>sql

Hot-standby SQL Server Using Alias Name?

I want to have a hot standby SQL server for my clients. My idea is to install
SQL on two separate machines, SRVA and SRVB, and configure continuous merge
replication between them. Client machines would connect to an alias name,
SRVC, with DNS for SRVC resolving to the IP address of SRVA. Upon failure, DNS
would be changed to point to SRVB. Could something like this work? If so, how
would one start to implement it?
Maybe a shorther question would be, is it possible for clients to connect to a
SQL server alias name (not a named instance) instead of the actual machine
name of the server?
--Eric RobinsonThis is a multi-part message in MIME format.
--=_NextPart_000_0370_01C3A9EA.14018070
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Consider using a clustered installation of SQL Server. It has automatic
failover and requires no human intervention.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Robinson" <eric@._nospam_nvipa.com> wrote in message
news:CFN379384331352315@.news.microsoft.com...
I want to have a hot standby SQL server for my clients. My idea is to
install
SQL on two separate machines, SRVA and SRVB, and configure continuous merge
replication between them. Client machines would connect to an alias name,
SRVC, with DNS for SRVC resolving to the IP address of SRVA. Upon failure,
DNS
would be changed to point to SRVB. Could something like this work? If so,
how
would one start to implement it?
Maybe a shorther question would be, is it possible for clients to connect to
a
SQL server alias name (not a named instance) instead of the actual machine
name of the server?
--Eric Robinson
--=_NextPart_000_0370_01C3A9EA.14018070
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Consider using a clustered =installation of SQL Server. It has automatic failover and requires no human intervention.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Robinson" =wrote in message news:CFN3793843313523=15@.news.microsoft.com...I want to have a hot standby SQL server for my clients. My idea is to =install SQL on two separate machines, SRVA and SRVB, and configure =continuous merge replication between them. Client machines would connect to an alias =name, SRVC, with DNS for SRVC resolving to the IP address of SRVA. Upon =failure, DNS would be changed to point to SRVB. Could something like this =work? If so, how would one start to implement it?Maybe a shorther =question would be, is it possible for clients to connect to a SQL server =alias name (not a named instance) instead of the actual machine name of the server?--Eric Robinson

---

--=_NextPart_000_0370_01C3A9EA.14018070--

Hot vs. warm standby

We have neither log shipping nor database mirroring on our system, so the
only way I could experiment with a standby database was to restore a backup
to a new database, using the standby option. Is the database I created a hot
or a warm standby? Can both kinds be used as a read-only data source for
queries or reports?I'd call your implementation a warm standby.
Yes, you can query such a database restored using STANDBY.
As for LS and M:
For LS, you can query the db is you restore using STANDBY, but you have to kick out the users for
each new log restore. Not very practical.
For mirroring, you can create a database snapshot of the mirrored database and query that snapshot.
I do recommend that you evaluate your HS requirements and your scalability requirements separately
and then decide which technology is best suited for each (HA and scalability). It might happen that
the same technology is good for both, but it isn't uncommon to use different technologies for the
two.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F37569F-1767-4F58-B0AB-90EBB83620AE@.microsoft.com...
> We have neither log shipping nor database mirroring on our system, so the
> only way I could experiment with a standby database was to restore a backup
> to a new database, using the standby option. Is the database I created a hot
> or a warm standby? Can both kinds be used as a read-only data source for
> queries or reports?
>|||Generally:
They call "Hot standby" when there is automatic failover in the high
availbility system. (Like SQL Server Failover Clustering or Database
Mirroring with High Availibility mode)
"Warm standby" when you can failover manually. (Log Shipping and Database
Mirroring's High Performance and Protection modes etc.)
Attach\detach, backup\restore kind of stuff is called "Cold standby"
Also I wanted to add to Tibor's message that, you'll need Enterprise Edition
of SQL Server 2005 to be able to use Database Snapshot in your environment.
--
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F37569F-1767-4F58-B0AB-90EBB83620AE@.microsoft.com...
> We have neither log shipping nor database mirroring on our system, so the
> only way I could experiment with a standby database was to restore a
> backup
> to a new database, using the standby option. Is the database I created a
> hot
> or a warm standby? Can both kinds be used as a read-only data source for
> queries or reports?
>

Hot Standby/Warm Standby

Hi...can someone please explain the difference between a hot standby and
warm standby...and how replication is a hot standby and log shipping is a
warm standbyIn the way that these terms are generally used, a warm standby is a solution
where you can lose a number of modifications (transaction), where a hot
standby is where you don't lose any transactions at all. I'm talking about
committed transactions, btw. Log shipping is warm as you do the log backup
perhaps every 5 minutes, so you can lose 5 minutes worth of data. Cluster is
hot, as both nodes work against the same data. Replication is rather hot, as
the replication latency is in the vicinity of a few seconds.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Terry" <spankme@.nospam.com> wrote in message
news:e4sKB1UrDHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi...can someone please explain the difference between a hot standby and
> warm standby...and how replication is a hot standby and log shipping is a
> warm standby
>|||This is a multi-part message in MIME format.
--=_NextPart_000_06CC_01C3AD24.2B5C8F20
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
A hot standby will take over from the failed server automatically and in a
short time without human intervention. A clustered install of SQL Server is
an example of a hot standby.
A warm standby takes longer and does require human intervention. Both
methods you described are warm standby's. However, it can be debated as to
whether replication is a standby or not.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Terry" <spankme@.nospam.com> wrote in message
news:e4sKB1UrDHA.2584@.TK2MSFTNGP09.phx.gbl...
Hi...can someone please explain the difference between a hot standby and
warm standby...and how replication is a hot standby and log shipping is a
warm standby
--=_NextPart_000_06CC_01C3AD24.2B5C8F20
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

A hot standby will take over from the =failed server automatically and in a short time without human =intervention. A clustered install of SQL Server is an example of a hot =standby.
A warm standby takes longer and does =require human intervention. Both methods you described are warm standby's. =However, it can be debated as to whether replication is a standby or not.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Terry" wrote in message news:e4sKB1UrDHA.2584=@.TK2MSFTNGP09.phx.gbl...Hi...can someone please explain the difference between a hot standby andwarm standby...and how replication is a hot standby and log shipping is =awarm standby

---

--=_NextPart_000_06CC_01C3AD24.2B5C8F20--|||I might add that hardware level replications (not SQL replication) such as
EMC SRDF in the synchronous mode can also be considered as a hot standby
solution.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Terry" <spankme@.nospam.com> wrote in message
news:e4sKB1UrDHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi...can someone please explain the difference between a hot standby and
> warm standby...and how replication is a hot standby and log shipping is a
> warm standby
>

Hot Standby Server w/out Clustering?

I want to have a hot standby SQL server for my clients without clustering. My
idea is to install SQL on two separate machines, SRVA and SRVB, and configure
continuous merge replication between them. Client machines would connect to an
alias name, SRVC, with DNS for SRVC resolving to the IP address of SRVA. Upon
failure, DNS would be changed to point to SRVB. Could something like this
work?
Maybe a shorther question would be, is it possible for clients to connect to a
SQL server alias name (not a named instance) instead of the actual machine
name of the server?
--Eric RobinsonWhat it sounds like you need is log shipping, not merge
replication. Replication is not the best of solutions for
a standby solution, and if you use replication for some
form of availability, it's usually transactional.
Replication is good if you have some specific data to make
available, but you're limited by rowsize and such, but as
for a complete copy of your DB, replication is not the
way. Log shipping is the standard SQL method of making a
warm standby.
You can set up NLB to do what you want to alias things,
and this is all covered in the book SQL Server 2000 HIgh
Availability on how to set it up using it as a "switch"
mechanism - not load balanced - for SQL Server. There are
even configuration instructions on the CD-ROM.
>--Original Message--
>I want to have a hot standby SQL server for my clients
without clustering. My
>idea is to install SQL on two separate machines, SRVA and
SRVB, and configure
>continuous merge replication between them. Client
machines would connect to an
>alias name, SRVC, with DNS for SRVC resolving to the IP
address of SRVA. Upon
>failure, DNS would be changed to point to SRVB. Could
something like this
>work?
>Maybe a shorther question would be, is it possible for
clients to connect to a
>SQL server alias name (not a named instance) instead of
the actual machine
>name of the server?
>--Eric Robinson
>.
>|||To have standby , it is better to use Log shipping. U can
even write scripts to take backup and mover to otherserver
and restore ,
Replication is only for data propagation not for Standby.
In case of Replication schema changes won't be applied. Of
course Merge is not useful u need Transactioal replication.
>--Original Message--
>I want to have a hot standby SQL server for my clients
without clustering. My
>idea is to install SQL on two separate machines, SRVA and
SRVB, and configure
>continuous merge replication between them. Client
machines would connect to an
>alias name, SRVC, with DNS for SRVC resolving to the IP
address of SRVA. Upon
>failure, DNS would be changed to point to SRVB. Could
something like this
>work?
>Maybe a shorther question would be, is it possible for
clients to connect to a
>SQL server alias name (not a named instance) instead of
the actual machine
>name of the server?
>--Eric Robinson
>.
>