Create Database in SQL Server 2012, Script and Use in 2008?

Question

If I create a database using 2012 and work and add that info to it, if I were to script the database after, would I be able to run the script in 2008 and have everything work A.O.K?

Answer 1

If you are using SQL Management Studio, you can right-click on the database name, then select Tasks → Generate Scripts.

Here you will be able to create scripts to script out the Schema, Data, or both Schema and Data, and if you click the Advanced button, you can specify which version of SQL Server you'd like to target.

So, simply choose SQL 2008 as the destination server and you should be good to go!

Answer 2

If you have very large amounts of data to move (gigabytes) then the TSQL scripts that the SQL Management Studio will generate for you could give you problems because of the sheer size. A better alternative would be to use BCP (Bulk Copy Program). The downside is that this is a command line utility and requires more work than just using the wizard.

As luck would have it somebody has written something which looks remarkably like the wizard only using BCP. It is the SQL Database Migration Wizard and is freely available on Codeplex - http://sqlazuremw.codeplex.com/releases/view/32334. Originally intended as a database migration tool between SQL Server and Azure you can just as easily use it between SQL Server and SQL Server. The key is to go into the advanced options (similar to the Management Studio wizard) and pick SQL Server. Later when you are prompted for the target system choose your 2008 installation.

I had no 2012 specific artefacts in my database and it worked very smoothly for me.