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.
Bottom is screenshot of how it is set up at the beginning when these three databases were created: Recovery state: “RESTORE WITH STANDBY” :
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).
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?