Re: How can I back up a log-shipped database?
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 09/01/04
- Next message: JohnW: "Re: Shrink MSDB Data device??"
- Previous message: Blue: "cannot compare data?"
- In reply to: John Peterson: "Re: How can I back up a log-shipped database?"
- Next in thread: Olu Adedeji: "RE: How can I back up a log-shipped database?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 1 Sep 2004 12:27:02 +0200
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 database will not just be
able to add the log records to the log-shipped database, because the transaction 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 files. 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 could 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 they're later > resumed. > > Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or would 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... > >> Any way we can use an undocumented "flag" in some system table to toggle the DB back in > >> STANDBY mode? ;-) > > > > 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 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 process work or > > give us some other > > option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVERY). > > > > Or MS would need to change SQL Server so it allow us to do a backup of a database 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 check 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... > >> 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... > >> > Hi John, > >> > > >> >> * Change the status of the database back to STANDBY (dunno how to do this yet). > >> > > >> > No can do. The recovery procedures etc in SQL Server aren't written to handle 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... > >> >> 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-shipped > >> >> 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 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... > >> >> > 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! > >> >> >> > >> >> >> > >> >> >>. > >> >> >> > >> >> > >> >> > >> > > >> > > >> > >> > > > > > >
- Next message: JohnW: "Re: Shrink MSDB Data device??"
- Previous message: Blue: "cannot compare data?"
- In reply to: John Peterson: "Re: How can I back up a log-shipped database?"
- Next in thread: Olu Adedeji: "RE: How can I back up a log-shipped database?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
Loading
