Right click on the database in the 2012 SQL Management Studio, and choose “Tasks -> Generate Scripts”. Click past the welcome screen, choose “script entire database and all database objects”. On the “specify how scripts should be saved” page, click “advanced”. Under “General” in the pop up properties page, change “Types of data to script” from “Schema only” to “Schema and data”, and change “Script for Server Version” from “2012” to “2008”.
I then had to find some way to edit the start of this massive SQL file, to tweak how the database would be created — see this q: https://stackoverflow.com/questions/102829/best-free-text-editor-supporting-more-than-4gb-files
And finally I had to find some way to run the SQL script, which was too large to open in SQL Management Studio — see this q: https://stackoverflow.com/questions/431913/how-do-you-run-a-300mb-ms-sql-sql-file
For SQL migrations, use the free and open source SQL Database Migration Wizard.
I had a 5GB database with couple ~10 million records and tried the route via Generate Script and than ran it with sqlcmd.exe. First of all, the generated script was not always working correct. Secondly, sqlcmd.exe can fail on large files too, complaining about available memory. osql.exe works, but just takes ages (and has the same command line arguments).
Then I came across a wonderful tool for migrating SQL Server to SQL Azure databases. This works for SQL Server to SQL Server as well, for example if you’d like to migrate a SQL 2012 database to 2008 R2. It uses bcp.exe, which uses bulk copy. There is a GUI and command-line (Batch) version available and it’s open source. See http://sqlazuremw.codeplex.com/. In my case, the operation took 16 minutes.
In an advanced screen you can select that your target is SQL Server, not SQL Azure.
I’m new to SQL, but I managed to migrate from 2012 to 2008. To do so I used the SQL import and export utility. I chose the SQL 2012 server and the database I want to migrate and finally the 2008 server and created a new database as my database of destination. It worked.
Try APEXSQL tools. They have a tool that will script the database and script the data also.
After creating that schema on my 2008 machine, I was able to open the
“Export Data” wizard on the 2012 machine, and after configuring the
2012 as source machine and the 2008 as target machine, I was presented
with a list of tables which I could copy. I selected all my tables
(300+), and clicked through the wizard. Unfortunately it spends ages
generating its scripts, then fails with errors like “Failure inserting
into the read-only column ‘FOO_ID'”.
The problem is that it is not able to insert the ID columns, so:
- Select all the tables by ticking them
- Select all the tables
(the way to do this is click on the first one and hold shift and click on the last table)
- Click on “Edit Mappings” – you can see that just above the next button and next to preview .
- You will get a window, tick on the “Enable identity insert” and then click next and go ahead, this should work. It worked for me.
I followed Rich’s solution https://superuser.com/a/469597/312310 above and it worked great! Thanks – I can’t vote up yet in here!
The extra steps I had to do to get this to work are:
Change the logical file names and ensure the path is correct. I also had to change the generated log name, else it tried to overwrite the mdf file with the log and then threw this error https://stackoverflow.com/questions/7534664/sql-server-script-error-database-is-already-in-use
CREATE DATABASE [xxxxx] ON PRIMARY
( NAME = xxxxx’, FILENAME = N’C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxxxx.mdf’ , SIZE =
14400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
( NAME = N’xxxxxldf‘, FILENAME = N’C:\Program Files\Microsoft SQL
SIZE = 18560KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
I had to comment out the generated section and add the user permissions manually afterwards
USE [xxxxx] GO /****** Object: User [xxxxx] Script Date: 30/11/2014 12:44:07 ******/ CREATE USER [xxxxx] FOR LOGIN [xxxxx] WITH DEFAULT_SCHEMA=[dbo] GO sys.sp_addrolemember @rolename = N'db_owner', @membername = N'Umbraco' GO sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'Umbraco' GO sys.sp_addrolemember @rolename = N'db_datawriter', @membername = N'Umbraco' GO
Ran it up, and lo and behold I can now restore my new Umbraco 7.1.2 database to my web server that only has SQL Server 2008 R2!