Sample Header Ad - 728x90

How to copy “Standby / Read-Only” database from one VM to other VM?

1 vote
1 answer
267 views
I am trying to upgrade from SQL 2016 to SQL 2019 (as SQL 2016 will be out of service in July 2016) in a different VM (Virtual Machine - Windows Server 2022), so I want to copy one database to other machine. I have a database (in SQL 2016) that is in “Standby / Read-Only” mode because there is a nightly job that runs to feed 24 hourly log files from a third party's SFPT server. enter image description here Bottom is screenshot of how it is set up at the beginning when these three databases were created: Recovery state: "RESTORE WITH STANDBY" : enter image description here I am trying to find a solution to transfer data (mdf) and log (ldf) file from original VM (that has SQL 2016) to a new VM (that has SQL 2019) and create a new database as “Standby / Read-Only” mode. I stopped SQL in SQL 2016, and I was able to move both data and log files. Now, when I tried to attach both data (mdf) and log (log) file in SQL 2019, I got an error saying: "Cannot attach a database that was being restored. (Microsoft SQL Server, Error: 1824) So, I am curious whether I have to change the mode of original database from “Standby / Read-Only” to Regular mode before moving mdf and ldf files. Or is it even possible? Sorry. I called bottom mode as "Regular" mode (I am not sure it is correct though). enter image description here If that is the only solution (change the mode from “Standby / Read-Only” mode to regular), can I create a database as “Standby / Read-Only” mode in SQL 2019 (on other VM) without any damage to the data or interruption of log file feed?
Asked by Java (253 rep)
Aug 3, 2024, 12:44 AM
Last activity: Aug 6, 2024, 08:51 PM