Generate backwards compatible CREATE DATABASE script with all objects?

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?

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:

StackExchange Database Administrators Q#166846, answer score: 6