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.
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
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.
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.
user databases but and general data (e.g.
Logins) stored in
System Databases, And you can't use the same approach as
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