0

In Our environment, Log log shipping is configured and working good.

  1. Transaction logs are Backed-up on Primary Server.
  2. Copy to Backup DB
  3. Restore on Backup DB.

Background:

We are in process of changing the IP of Backup (Secondary) DB server. And before changing IP, client wants to take the backup of the Secondary(Backup) DB.

Question:

  1. Is it possible to take the backup of Secondary Database? (When I Right click on Backup (Secondary) DB --> Task --> backup, backup option is grayed out.)
    --> Do I need to 'Take Offline' to take back up?
    --> Or is there any other way?

  2. Will there be any impact if we change IP of Backup (Secondary) DB and do we need to make changes anywhere else?

  3. Will Log shipping keeps running without any issues, or do we need to reconfigure it as new?

Improve this question
0

2 Answers 2

Reset to default
0

Community wiki answer:

And before changing IP, client wants to take the backup of the Secondary(Backup) DB

I'd give them a full backup of primary just before changing the IP and say there you go. In log shipping configuration as you have the secondary DB is in standby ready to restore logs per each run of those jobs. You can probably take it out of standby and then back it up but why wouldn't a full backup of the primary work? Is it super large and across a slow WAN or disaster recovery link or what?

I'd test this during an off hour and have everything ready to get LS back up just in case. For example, a full backup of primary copied over to the secondary server. Really when the logs are shipped, you have the backups to restore secondary if needed so ensure you understand how to do that and practice.

If you are the DBA, are you worried about not being able to get the secondary back up and running or what? Perhaps there's a slow link and a huge DB or something.

Improve this answer
0
1

Changing the IP of the secondary server shouldn't be an issue as long as you used DNS names in the log shipping configuration.

To answer your questions:

  1. No you can't take a backup of a standby database, at least not with the native functionality in SQL Server.
  2. As long as all the setting stay the same, there shouldn't be any impact. You can disable the copy and restore job before the change though.
  3. There shouldn't be any impact as long as you used DNS names in your log shipping configuration. The only thing that I can think of is that you need to refresh the DNS cache of the primary server to get the new IP address.
Improve this answer
3
  • Thanks for your guidance. It's helpful. I have one additional query: Is it possible to give IP address instead of Hostname (of Backup server) during configuring / updating Transaction logs shipping configuration on Primary server? Thanks, SN
    – user149163
    Commented Apr 25, 2018 at 3:35
  • It's possible to use IP addresses when configuring log shipping. I wouldn't advise doing it due to the possibility of IP addresses changes and only use DNS but it's a possibility.
    – SQLStad
    Commented Apr 25, 2018 at 8:25
  • Is it possible to modify old job and put IP address in place of Host name ? Or do I need to stop old job and create fresh / new jobs ?
    – user149163
    Commented Apr 26, 2018 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.