We like to live on the edge, so we don't mind trying out the latest version of SQL in production do we? Sure, why not, SQL Server 2017 is stable and it's an enterprise class database platform (didn't used to be able to say that :-( ). Well what happens when you reach the maximum database size and have to move a database from the cutting edge SQL 2017 Express edition (slightly sarcastic here) to a Standard or Enterprise server and the only other servers available are 2016? Oops.
I discovered the problem when trying to restore a backup from the 2017 instance onto a 2016 instance. SQL coughed up this error message:
------------------------------
Restore of database '*******' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The database was backed up on a server running version 14.00.1000. That version is incompatible with this server, which is running version 13.00.4001. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)
------------------------------
"Ok, that's inconvenient" I thought, "what now?"
So I googled (as you do in situations like this) and came across this nice article https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/ which explained how to export the entire database via script...nice! Only problem was that the script it produced was too large to open in Notepad or SQL Management Studio (5.8GB) and I needed to amend the CREATE DATABASE statement as well as execute it.
Well, to amend the script this article proved rather useful https://www.petri.com/tip-opening-large-files-windows . I downloaded EmEditor and fixed the script so that the database files were created in the right path. Then, I logged on to the SQL 2016 server, opened a command prompt and executed the massive script using SQLCMD:
C:\>SQLCMD -S .\MyInstance -E -i "C:\...\MyMassiveScript.sql"
Easier than I expected really, it all just worked...which is not a common scenario :-)
Later dudes!
PS: the script will take ages to execute so be patient. It is, without a doubt, the worst performing method of inserting huge amounts of data into a SQL database, i.e. one record at a time.
