1

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?

Improve this question
0

1 Answer 1

Reset to default
3

is it even possible?

No, you cannot attach a database in norecovery mode. You can work it around by

  • creating empty database with the same mdf and ldf files
  • backup empty database and restore it with standby on the same standby file as old one.
  • set this database offline
  • replace data, log and standby files with old ones
  • set the database online again
  • continue applying log backups

Anyways, you can ask third party to send you full backup as a base for log shipping

EDIT. Here is the trick you can do to temporary open copy of your logshipped read-only standby database (let's name it 'standby')

  • Create empty database for copy (let's name it 'standby_copy')
  • Set both 'standby' and 'standby_copy' offline
  • Copy data and log files (mdf and ldf) files from 'standby' to 'standby_copy'
  • Set both databases online
Improve this answer
3
  • Can you please put more details?
    – Java
    Commented Aug 10 at 0:05
  • I am not sure what do you mean. I edited my answer.
    – SergeyA
    Commented Aug 10 at 0:22
  • I saw your previous comment that if "you want, I could provide more details", so I was just following up on that comment. No problem. Thank you.
    – Java
    Commented Aug 10 at 16:22

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.