February 17, 2020 at 11:20 pm
We recently got hit by a ransomware attack. The SQL server is hosed, but I was able to get backups of the system and user databases. The hosed server is SQL 2012. We have a SQL 2016 license, but hadn't upgraded the server yet. Can i restore the backups to a new SQL 2016 server? Would i need to restore the master, model, and msdb databases or just certain ones? Thanks for any input.
February 18, 2020 at 1:34 pm
You can restore 2012 to 2016. It'll work fine. However, once it's moved to 2016, you can't backup and restore back to 2012. So, this is likely a permanent upgrade (right thing to do anyway).
However, one caveat. You'll be hopping the great Cardinality Estimator divide of 2014. The cardinality estimation engine changed in SQL Server 2014. This can affect performance on systems as they move from pre-2014 to post-2014 (never upgrade to just 2014, utter waste of effort). So, I'd leave those databases in compatibility mode until you're ready to set up testing to make the move to the new CE. Leaving it in the old compatibility mode means it will run with the old CE.
I'd also recommend you do DBCC checks after the restores. A little paranoia in this area goes a long way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2020 at 2:56 pm
Thanks for in info. That helps greatly. Which system databases can i restore? I would assume i can't restore the 2012 system databases to the 2016 server. Is there any other way to migrate the users / permissions?
February 18, 2020 at 3:39 pm
You can. I'd suggest you read through the documentation for some of the how to. Don't bother with model (unless you're a unicorn and you're putting customization in model on purpose, exceedingly few people do that).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2020 at 3:54 pm
You can. I'd suggest you read through the documentation for some of the how to. Don't bother with model (unless you're a unicorn and you're putting customization in model on purpose, exceedingly few people do that).
Can you restore a 2012 system DB to a 2016 instance Grant, looking at the docs it details
System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running
So from that I would say restoring 2012 to 2016 isn't supported.
For this I would usually suggest spinning up a 2012 instance and restore master and msdb and script out the stuff via GUI or T-SQL needed then destroy the instance, or restore the DB's as user DB's then script out the objects via T-SQL, depends if the OP is confident building the scripts from the system tables.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply