0

I'm rather new to MSSQL and maybe a task is very simple.

I have WinServer 2016 and SQL Server 2016. I need to copy/clone/migrate everything about SQL server (logins, procedures, schedules, tables...) to new clean VM (WinServer 2022, SQL Server 2019), so a client can just change the host name of SQL server and work with same DB like nothing happened. The original VM can be deleted when new VM is OK.

What's a simplest correct way to do this?

5
  • You can check here: stackoverflow.com/a/47519397/2908599
    – Romeo Ninov
    Aug 2 at 12:59
  • 1
    Scipt the whole master database, then backup/restore the other databases. You can also backup/restore master, but try to avoid if possible, especially as you are doing a version upgrade as well. Aug 2 at 15:54
  • Is backup-restore alone enough to get a full clone of existing MSSQL instance?
    – ZZ Wave
    Aug 2 at 16:34
  • Not quite, there is also the master database as I said. Either scripting or restoring it should be enough. There are also a handful of registry settings, such as TCP and SSL certificate settings, which you can find in the SQL Server Configuration Manager Aug 2 at 16:51
  • In addition to what Charlieface said, from a setup and infrastructure perspective, you should provision your new server the same way as the old one. E.g. same drive letters, same install to the same drive, and same breakout of the databases to the same drives as you were previously doing. There is meta-data stored in the master database about where those files live, inclusive of drive letter, and depending on how you migrate the master database over, that will need to be accounted for if anything changes in server setup. If you're using replication, you might need to take extra steps too.
    – J.D.
    Aug 3 at 2:03

1 Answer 1

0

I've never used the backup/restore master approach with that objective, but there's a warning on the Restore the master Database doc related to that procedure:

[...] the instance where the master database is being restored to should be as close to an exact match to the original as possible. At a minimum, this recovery instance should be the same version, edition, and patch level, and it should have the same selection of features and the same external configuration (hostname, cluster membership, and so on) as the original instance. Doing otherwise might result in undefined SQL Server instance behavior, with inconsistent feature support, and is not guaranteed to be viable.

Since it's a VM, you could clone it and try an Upgrade in-place to upgrade master to the newer version before the backup and restore to a brand new server:

With this approach, the SQL Server setup program upgrades the existing SQL Server installation by replacing the existing SQL Server bits with the new SQL Server bits and then upgrades each of the system and user databases.

There's also a Migrate to a new installation section on that doc that could give you some guidance on how to move your data to a new clean server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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