Migrating from SQL Server 2012 to 2008

We were doing some server consolidation and the path of least resistance was to move an isolated database running on 2012 to 2008.

NOTE: The size of the database was relatively small at 6GB.

Things that did not work.

• Backup and restore. duh
• Export/importing data. They both erred out.

Here are the steps I ended up taking

• In Management Studio, right click on the database > Tasks > Follow the wizard
• To keep the size of the script small and manageable I first scripted out the database schema and executed that on the 2008 server. Reminder to go into "Advanced Settings" on the "Set Scripting Options" window. You need to set the "Script for Server Version" to "SQL Server 2008"
• Next I scripted the data in the tables using the same method.
• The script was too large to open in Management Studio so I had to use SQL Command Utility. http://msdn.microsoft.com/en-us/library/ms162773.aspx It took a while to run the file but it did work.
• There was one table that was much too large to run via this method. Since the table had no referential integrity to it I created a linked server to the server running 2012 and did a bulk insert into the 2008 table.
• After all that I had to set up the database users and the migration was complete.