July 31, 2021 at 12:53 am
Hi Guys,
I have an SQL Server 2012 Express database that I would like to migrate to SQL Server 2014 Express as well as subsequent versions. The reason for wanting multiple versions of the database is because I will be developing an application using the database and I haven't decided which version of .Net I will be using for development. I have tried using the SQL Server Migration Assistant and it seems to be unnecessarily complicated for what seems to be such a simple task. I have also tried scripting the database and running the script against my Server 2014 instance. Executing the script generated errors that make no sense to me given that they are just basic create statements. My basic question is what is the simplest way to accomplish my objective?
Thank you in advance for your assistance.
July 31, 2021 at 8:02 am
The quickest and easiest method is always going to be BACKUP and RESTORE.
Just note that if you have created any logins on the 2012 machine you will need something like sp_help_Revlogin or Copy-DbaLogin from the DBATools powershell module to move the logins correctly between the sql versions.
you could also look at the migration commands within DBATools, but quickest would be backup restore.
July 31, 2021 at 2:27 pm
Why would you be concerned about the version of SQL Server? That should not be dependent in any way on the version of .NET you are using in the application, since all .NET will be doing is making a connection to a database. The important issue for that is what version of driver you use - and as long as that driver is compatible with that version of the database there won't be any issues.
If you are developing an application - you really should use the latest version of SQL Server and .NET. The only reason to downgrade from that point would be to support customers who are not able to support the latest version on their devices.
Developing for 2012/2014 as the baseline - you should be aware that those version are already 8+ years old and out of general support.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 2, 2021 at 12:10 pm
One huge note, I wouldn't recommend anyone "upgrade" a server to 2014 here in 2021. There are three other versions of SQL Server after 2014. All of them offer mechanisms for dealing with issues caused by the new cardinality estimation engine, introduced in 2014, that do not exist in SQL Server 2014. I would always skip that version.
This doesn't even mention that mainstream support ended in 2019. Extended support ends in three years.
Friends don't let friends upgrade to SQL Server 2014.
"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
August 2, 2021 at 12:22 pm
Are you using Visual Studio? You could always use the SQL Server Schema Compare and Data Compare utilities for this, so long as your source database is not too large (and given you are using SQL Express I guess it won't be).
Create a new empty database on the SQL 2014 instance first and then use the Schema Compare utility to compare and update, once you've got the schema done you can then do the data (if you've set primary keys that can be used for the comparison).
August 10, 2021 at 11:16 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply