In our company, we need to update a Windows Server OS, but an in-place-update fails. So Drive C is going to get wiped and a current Windows Server OS will get installed, followed by MS SQL 2022, which is already running there - just on an old OS.
I would detach and reattach the user databases, but use the master db from the fresh installed. The infrastructure team wants to backup the current master, and overwrite the fresh one with the backup after install.
I have never done that - what does one have to do or have to know when going this way?
My role is a BI Developer, using SSIS and SSAS, but since I have some SQL Admin background, I was asked to assist the infrastructure team with this work. This, however, I never had to do.
The server in question is a dev/test one, so things may go wrong without much impact. At some point early next year however, our production SQL 2014 (on Server 2012) will be moved to new hardware, current OS obviously, and SQL 2022 to match the dev environment.
This sounds a bit more frightning (reusing a master db from SQL 2014 in SQL 2022) but on the other hand also more important for all users configured in the master, all jobs configured in msdb and so on. Any pitfalls to know about before with that update process?
1 Answer 1
The infrastructure team wants to backup the current master, and overwrite the fresh one with the backup after install.
You can restore Master from the same instance before the OS upgrade, so long as you install SQL Server to exactly the same patch level and configuration.
You can't restore Master when doing a side-by-side version upgrade.
In the event of disaster recovery, 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 can result in undefined SQL Server instance behavior, with inconsistent feature support, and isn't guaranteed to be viable.