Log Shipping Failover - SQL Server Forums

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)

Log Shipping Failover

Author

Topic

catparks
Starting Member

18 Posts

Posted - 2004-08-04 : 09:38:27

I am log shipping a database and want to document the failover process. I stopped the log shipping, and went to the server where the read only database resides. The problem is that I can't seem to get the database out of a warm stand by state. I get the following error:
"Error 5063: Database 'test' is in warm standby. A warm-standby database is read only."

Any suggestions?

Thanks.

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-08-04 : 10:42:42

you try ?

RESTORE DATABASE TEST
WITH RECOVERY
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-04 : 12:52:23

Yes you will need to perform a RESTORE on the destination database. You will need to apply at least one transaction log with recovery to fail it over. You can either use the last one already applied or copy a new one over from the source and apply that.

Tara

MuadDBA

628 Posts

Posted - 2004-08-04 : 15:53:07

Tara,

I don't think you have to even have a transaction log. You can issue the restore statement with the "WITH RECOVERY" verbage and no transaction log specified, and it will just bring it out of standby mode.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-04 : 19:26:13

Just know that the only way you can continue your log shipping is to resynchronize with another full backup. Once you take databases out of warm standby mode, you can't apply any more transaction logs to them until the next restore from a full backup.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-04 : 21:21:31

The fastest way though is to apply a transaction log. Yes you can issue the restore statement using with recovery but you would have to restore the full backup. You would normally want to reapply the last applied transaction log but specify with recovery or simply get the final one over from the source and use with recovery. For us, it would be much faster to apply the tlogs as the full backup could take a bit to restore where as the tlogs would be quick as they are smaller. Plus we'd want the new data applied and not just the data from the last full.

Tara

catparks
Starting Member

18 Posts

Posted - 2004-08-05 : 10:43:04

What is the syntax to restore the transaction logs? I can't seem to get it right. I found an example:

RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY

I understand 'MyNwind' is the database name, and what NORECOVERY means. What is 'MyNwind_log1'? An example of my log file name is: TestDB_tlog_200408050800.TRN

The syntax I use to restore a database is:

RESTORE DATABASE TestDB
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TestDB8-4'
WITH RECOVERY,
MOVE 'TestDB_Data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDB_Data.mdf', -- New path for database file.
MOVE 'TestDB_Log' TO 'd:\MSSQL\LogFiles\TestDB_log.ldf'

Thanks for all your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-05 : 12:12:02

RESTORE DATABASE TestDB
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TestDB8-4\TestDB_tlog_200408050800.TRN'
WITH RECOVERY,
MOVE 'TestDB_Data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDB_Data.mdf', -- New path for database file.
MOVE 'TestDB_Log' TO 'd:\MSSQL\LogFiles\TestDB_log.ldf'

You need to specify the path AND filename of the tlog in the FROM DISK part. You first would have had to restore a full backup and used WITH NORECOVERY though in order to be able to apply a tlog. And the tlog must be the one that was backed up after the full backup was taken. If it isn't, you need to apply them in order until you get to the tlog you want. You use WITH NORECOVERY until you are done applying and use WITH RECOVERY for the last one.

Tara

catparks
Starting Member

18 Posts

Posted - 2004-08-05 : 14:41:33

I tried your suggestion, and am getting the following error:

The backup set in file 'd:\LogShipCopy\TestDB_tlog_200408050800.TRN' was created by BACKUP LOG and cannot be used for this restore operation.

Also, I don't know if this makes a difference, but the database appears as 'Loading' in the Enterprise Manager view.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-05 : 14:44:22

Oops:

RESTORE LOG TestDB
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TestDB8-4\TestDB_tlog_200408050800.TRN'
WITH RECOVERY,
MOVE 'TestDB_Data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDB_Data.mdf', -- New path for database file.
MOVE 'TestDB_Log' TO 'd:\MSSQL\LogFiles\TestDB_log.ldf'

Tara

catparks
Starting Member

18 Posts

Posted - 2004-08-05 : 15:07:39

That worked! Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources