Scheduled mssql restore to a different server

Hello @DanC 

Most of your steps are good except a few points here:

1. “match name in the serve2 and physical path as well” This makes it appear that the database already exists on the destination, or you will create it. Don't. There is no need to create the DB the restore will create it on destination when that first restore job runs. However your schedule after that would need to match everything.
2. Differentials are not needed only tlogs. Once you restore that first job you only need to restore tlog backups. You can set that schedule anytime you want. Since tlogs are running every 15min it will just grab the last one that completed with the schedule starts. Doesnt matter if one is currently running, etc. In that schedule you also want to select skip full backup and apply log backups only in advanced options.

To follow up on point 2 once first restore is done these options are needed. Documentation calls is hot server standby option.

https://documentation.commvault.com/2022e/expert/18281_restoring_sql_transaction_logs_to_hot_standby_server.html

Another note this can all be automated with SQL Livesync which monitors the source machines and auto applies the tlog backups to the destination automatically or on a schedule/delay. It also contains some intelligence like if the destination was deleted it knows to go backup and restore the entire database again automatically.

https://documentation.commvault.com/2022e/expert/92005_replication_of_sql_databases.html

@Scott Reynolds thank you very much

#1 you’re right - > the database already exist on the destination server via a regular(one-time) db restore using source db backup. But source and destination sql servers db name and files path are different.

To make destination db up to date and remain in standby(read-only) → below steps look good ?

• when create a new sql restore schedule and make sure match everything in the destination (e.g. database name, file path, undo file path)
•  select recovery types - > standby and choice undo file path
• In that schedule -> select skip full backup and apply log backups only in advanced options.
• set restore schedule anytime I want (e.g once every 15mins or once a day, etc...)

#2 we want the destination db on standby mode so user can access.

correct me if i was wrong →  for sql live sync option, seems like the destination database remain on restoring/recovery mode so no one can access until initiate failover via Commcell/admin center or manually bring it to standby mode 

just want to add one more thing for sql live sync:

To leave destination database on standby, source and destination db has to be on the same version.

No they do not. Livesync requirements are the same as restore. Destination SQL version must be the same OR higer. That is Microsoft limitation. Can always go to higher version but not to lower version.

@Scott Reynolds

source sql db: 2016

destination sql db : 2019

not sure what i was missing but live sync can’t leave destination db on standby when they are on different sql version

Delete the database from the destination. Do NOT recreate it. Let the restore create it

Run the restore again

V11/SP24.34
 

i did run from scratch and still same result :

#1 delete db from destination

#2 run brand new live sync but still having the same error

This is related to standby not specific to livesync you will have the same issue on a scheduled restore.

 This is SQL error and limitation. A restore itself would work (without the standby)