question

Tom 3 avatar image
Tom 3 asked

SQL 2005 Log Shipping standby file

I setup log shipping using the ip address of the secondary server. The IP Address of the secondary server has changed. I am now getting an error on the restore job:

-------------
Error during startup of warm standby database.....its standby file ["\192.168.1.106\Log_Shipping\xxxxxxx was inaccessible to the RESTORE statement.
-------------

Where can I modify the address of the standby file ??????

sql-server-2005log-shipping
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered

The location of the standby file is stored with the recovering database and can't be changed while your database is still in standby.

You will need to add the old IP address back to the server and allow log shipping to resume after you reconfigure it to use the new IP address. This will apply the old standby file, and the new standby file will use the new IP address.

If getting your old IP address for a few minutes isn't possible, you will either need to place the standby file on the computer that has your old IP address and make the file share and path exactly match the original so the standby can be applied, or you will have to re-initialize the log shipping secondary.

An alternative to using standby is to use norecovery, which doesn't use a standby file. The trade-off is that users won't be able to query the secondary database, which may not be acceptable in your environment.

[Edit]

Blackhawk-17 made a great observation, so I'm using it to improve my own answer :)

To reconfigure log shipping to use the new IP address without reinitializing with a new full backup, you'll need to update log shipping tables on both the primary and secondary server.

On your primary server, you can update the IP address of the backup share in the backup_share and/or backup_directory columns of msdb.dbo.log_shipping_primary_databases and you can update the IP address of your secondary server in the secondary_server column of msdb.dbo.log_shipping_primary_secondaries.

On your secondary server, you can update the IP address of your backup share in the backup_source_directory and/or backup_destination_directory columns of msdb.dbo.log_shipping_secondary

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

can the two servers ping each other? The standby file is the location that the logfile is placed in order to restore from. It sounds like a connectivity issue between the secondary server and the network location being used.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

the servers can ping each other. The problem is that the restore job is looking for the old ip address. I don't know where the restore job is getting the old ip address from.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered

You can try updating the information in the secondary_server_name column of the dbo.log_shipping_secondaries table in msdb.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Most definitely. I got hung up on the standby file and completely missed the bit about fixing the IP address.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.