0

Here is the story: We want to do side by side SQL upgrade from 2016 to 2019 with a new windows of 2016. My SA wants to robocopy all drives except drive C from the old server to the new server. Do I get any issues with live or offline databases (data and log files) after transfer? will they get corrupted? Please provide me your ideas.

3 Answers 3

1

You can only do this "robocopy everything" approach if you stop all services on the source server, and then attach the databases on the destination. But even that won't work 100%, because unless you copy the msdb and master databases as well, which I do not recommend, you won't bring over logins, Agent jobs, and other things like that.

I'm soapboxing a little here but as a DBA, I would not allow a systems administrator who does not have good SQL Server experience to perform the installation and migration of SQL Server, in part because of what I mention above.

Instead, I suggest doing a full migration using the dbatools PowerShell module, specificially the Start-DbaMigration function. The "easy" way of using it is to install your new 2019 instance, make sure that the accounts that both the source and destination services run under (preferably gMSA domain accounts) have access to a common file share, and then run that function with a backup & restore (via that share) to copy the databases.

Start-DbaMigration will pull over the entire instance - databases, security, XE sessions, Resource Governor, Agent jobs, dbmail settings, linked servers - literally everything on the instance will be brought over. You can exclude individual components if you like as well.

Manual migrations leave too much room for error, they're slow, and so 2012. Automation rules in 2020.

Here's a 50-second demo of it.

2
  • What if all databases are encrypted ?
    – BDAR
    Jan 30, 2020 at 18:23
  • AFAIK, TDE-encrypted databases are supported as well. The certificates will be migrated as well.
    – alroc
    Jan 30, 2020 at 19:48
2

If the SA is building a new server then you best option is to do a migration not an upgrade (upgrades also do not have a good rollback plan and therefore is not recommended to do in production).

Here is a decent article with migration steps to start with but there may be adjustments for your environment. https://www.mssqltips.com/sqlservertip/1936/sql-server-database-migration-checklist/

Migrations should not be scary but they need everyone's involvement to make sure that if you do run into snags that they get fixed asap and hopefully documented for future reference.

0

You can Attach/Detach or Backup/Restore user databases but and general data (e.g. jobs) & configuration (e.g. Logins) stored in System Databases, And you can't use the same approach as user databases.

SQL Server migration will completely be done if you do it right with SQL Server Upgrade Wizard but mix it with RoboCopy or any external tools could damage your System Databases.

1
  • The SQL Server Upgrade wizard only applies for in-place upgrades, which the OP Is not going to be doing (and I recommend against).
    – alroc
    Jan 30, 2020 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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