3

I'm working on SQL Server 2016, and I want to deploy a database I have created to Sql Server 2012 SP1.

I found I was unable to create a backup in v2016 and restore to v2012. So instead in the v2016 version I:

  • Right click the DB
  • Select Tasks > Generate scripts
  • Script a CREATE statement for all objects

This kinda works... but I have to add a lot of objects manually. The database has tables/views/tvps/sps/scalar functions. Is there a way to script a create statement that is backwards compatible with older SQL Server versions?

Improve this question
2
  • 3
    Don't reinvent the wheel, man.
    – Aaron Bertrand
    Mar 10, 2017 at 15:47
  • @AaronBertrand. if by 'don't reinvent the wheel' you mean 'don't shell out for the first piece of paid software that is recommended, and instead ask opinions from others on how best to continue' I definitely agree with you. I found a great Youtube video on SSDT a while ago (youtube.com/watch?v=uPll3dMxTXU) and is almost certainly the approach I will take if there isn't a simple 'make compatible with x' check box in SSMS. But it will take a few hours to get comfortable with SSDT probably
    – Zach Smith
    Mar 10, 2017 at 21:16

3 Answers 3

Reset to default
2

I would advocate that you use Visual Studio to create a database project for this. You can set the target to be SQL 2012, then do a build and address any issues found. You can then deploy the schema to SQL 2012, and export/import your data. HTH.

Improve this answer
6

You're using the correct method, you're just not setting all the right options.

Go through the Tasks > Generate scripts wizard again, there are steps in there where you can choose the all object types, and other places where you can choose what properties of those objects to include (permissions, indexes, etc).

There's also a 'Script for Server Version' option, which allows you to choose which version of SQL you're scripting for.

If your database is small enough, you can even include data in that script. If not, you'll have to create the empty database container, and then using SSIS or something to pipe all the data over.

One more option: you can create an SSIS package and use the "Transfer SQL Server Objects Task" type to do the same thing, it should have all the same options:

enter image description here

Improve this answer
2

Do not use backup/restore for this. Using scripts is the right way to go. To help you in that there are tools. I can recommend Red Gate Tools (you can create setup project, migration scripts, data insert scripts).

Btw, if you need your database to work on older versions, then develop on older versions (or set compatibility level). Or you will be tempted to use features not available in older versions. If you use older version database then you could restore to newer version. But that path leads to dark side.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.