New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Comments
incompatible
no
Switch to PostgreSQL.
Are you going to take backups on 2014 and restore them on 2016? If so, it should be possible.
Sounds like he wants it the other way unfortunately.
What's the size of database you're looking at moving? If it's small then Generate Scripts might work, although I've only ever used that on individual tables and never tried doing a whole database at once. There's a few options on the Advanced screen that you'll want to look at if you use this feature (server version, type of data to script, etc).
My bad. I totally read it the other way. Need more sleep
MS discourages restoring on older versions of SQL server and they are never guaranteed to work. But the "generate scripts" approach might work as long as the tables don't have any fancy data types.
This might be a good place to start:
https://msdn.microsoft.com/en-us/library/cc280407.aspx
Yes, you can. Set the db compatibility mode to lower level, backup and restore
While I have not tried this so I can't say with 100% certainty, my understanding is the compatibility level does not solve this problem (it's more about feature support than about altering the actual file format of the .mdf, .ldf, or .bak files)
I'm genuinely curious if you've ever done that.
My understanding is as @Ree notes that you're crossing a line as far as file formats and such. Compatibility level just allows SQL 2016 to pretend to be SQL 2014 to the user, but it knows in its brain to actually do SQL 2016 things. I don't know how SQL 2014 would know how to read a SQL 2016 datafile. Maybe they don't change every version, but I'm sure the format does change as features are added.
Select into for each table?
The rule of thumb when migrating from newer version to older is big possibility of compatibility issues. To overcome this issue, use the most generic format as possible. This could be CSV, XML, json, etc. You might also lose your stored procedures, if you have any.
On open-source RDBMS, we can use CSV to migrate database between versions or even between RDBMS. I've successfully migrate data between MySQL to PostgreSQL on some occasions using CSV format.
Sorry for simplified answer, this link describes the process https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/