Downgrading db from SQL Server 2016 Beta to SQL 2014 SP1

I'd rather gloss over the details of the situation that brought me here, but I have a production database running on SQL Server 2016 Beta which is about to expire. On the same server I have SQL Server 2014. Though I cannot just detach from SQL 2016 and attach to 2014.

My plan is to use the SQL Server Import & export wizard to transfer the database from one to the other. I think that I know the answer, but I just need to be absolutely sure that there aren't any known pitfalls. IE, I think it transfers over all constraints, triggers, stored procedures, etc... But if it didn't that would be pretty bad for me.

If this is not the right approach, what would you suggest?

Also, I do have a backup from 4 months ago which is 2014 compatible. However the onus is then on me to transfer all of the new & altered data. Which seems like a messy approach.

Thoughts?

How much data? Millions of rows? More? Less perhaps? :slight_smile:

Uncompressed, the dbis 45gb. It has a few hundred tables where some of those tables have tens of thousands of rows and some others have hundreds of thousands.

Does that change how you might approach a backup/restore?

Well it rules out just scripting the data!

My inclination would the to COMPLETELY script the DDL of the DB ("completely" means that there are some options in SSMS RightClick DatabaseName - Tasks - Generate Scripts that are off by default that need turning on, amongst them i would turn these on:

Continue scripting on Error
Include System constraint names (doubt anything is dependent on that though ...)
Script bindings (I have a couple of those chappies, you might too)
Script for Server Version - you could do 2014 but I think ti would be better to do 2016 and then if anything breaks you can worry about whether that has been exploited, already, on the 2016 database, and how you should go about downgrading that)
Script Logins
Script Object Level permissions
Script Owner
Script USE DATABASE change to FALSE
Script Change Tracking ()
Script Data Compression Options (
)
Script Full Text indexes (*)
Script triggers (why on earth is that off by default? :frowning: )

(*) IF you use that - but I'm thinking "Why wouldn't you script that anyway [by default], on the off chance that it MIGHT be used" ??

You might want to also include DROP commands - if you need to clear all the data, for a second-attempt, being able to extract all the DROPs for all Triggers and Foreign keys might be handy - and then you can TRUNCATE all the tables, but you might just DROP the datbase and start over (45GB takes a while to create though)

I would hand-edit the script to disable (or move to a second script file) all the Foreign key definitions and all the Triggers

Then I would:

create the "empty database" on the target machine

CONSIDER: hand extending the files to same-size as existing DB, in particular taking care of optimising the VLFs in the TLog

migrate all the data (SSIS or however you choose to do it - BCP using NATIVE format would also be a choice for me as I am more familiar with that, and I like the error message files it generates)

Create the Triggers and Foreign Keys

1 Like