Need to backup the log shipping db

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Need to backup the log shipping db

Author

Topic

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-09-15 : 11:59:03

Hello,
On Friday at 4.30PM, I stopped logshipping since we were going to apply some PS Sps to Prod db. Now we accidentally deleted the Prod bckup which we took at 4 PM on Friday. Now I want to the bckup the logshipping database since it has data until 4.30 Friday but I get this message so how do i fix it:

Database 'HR8PRD' is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is completed.

Basically, I want to backup the read only database!!

Thanks,
Sarat

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-15 : 12:01:20

I am not understanding the problem. Why would you want to backup the read-only database? The source database already has the data, so back that one up. It is not necessary to back up both locations.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-09-15 : 12:06:41

But the source database has psoft service pack data so in the event that this sp blows up something, we want to go back to friday 4.30 when we didn't apply sp. we cannot go back now because we lost the source backup we made on friday at 4.30!! so the only way is to backup the log shipping db which has data until 4.30 on friday (since we stopped log shipping at 4.30) also.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-15 : 12:11:11

Well you can backup the read-only database as the error mentions. To get the desired results, you could use the last full backup that you have available and all of the transaction logs since the full backup and up to the maintenance.

Also, why would you stop log shipping for this maintenance?

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-09-15 : 12:19:24

Because of space issues and multiple backups, index rebuild and update stats we had to do while applying the sp.
In EM under Database Properties Options tab, what will happen if I UNCHECK the read-only box?
and what is the point of logshipping if I can turn it into a real time db if the real time db fails? am i missing something?
Also,say worst case, I restore from the last db backup (i know the sql), what sql should I do for applying each log backup?
thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-15 : 12:22:53

Unchecking the read-only option probably will not be enough to backup the database. You would need to take it out of STANDBY mode which can only occur by applying a transaction log with RECOVERY, which means that you have now broken log shipping. If the real time db fails, you would just apply the last transacion log to the standby database and restore it WITH RECOVERY. I don't understand your point of the real-time database.

To apply each log, you would need to run a RESTORE command for each. I know this would be tedious work, so what I have done is write a script to get the RESTORE command for each transaction log found in a directory. I do this using xp_cmdshell and dir.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-09-15 : 12:34:13

ok, i think i get your point!
so is this want i will do now? is the sql okay?

1. Apply transaction log to the destination (stand-by) db. (Based on the last T-log applied)

RESTORE LOG CPYPROD
FROM DISK = 'Q:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL\BACKUP\TLOG\Log_Latest.TRN'

2. Restore database with recovery

RESTORE DATABASE CPYPROD
WITH RECORVERY

3. Backup database

Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-09-15 : 12:56:09

BTW this seems to have worked:
1..
restore log HR8PRD
from disk = 'Q:\Program Files\Microsoft SQL Server\MSSQL\Backup\TranLogBak\HR8PRD_tlog_200309121635-Keep.TRN'
2..
backup database HR8PRD
to disk = 'Q:\Program Files\Microsoft SQL Server\MSSQL\Backup\091203_430.Bak'
with stats = 2

Ofcourse the logshipping needs to be set again now.
Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-15 : 13:06:07

WITH RECOVERY option does not need to be part of the statement because it is the default option. WITH NORECOVERY must be part of the statement if you want the ability to apply transaction logs.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources