SQL Server 2012 backward compatibility for backups with 2008

Question

I have a number of clients with SQL Server 2008 and that's what I have here on my server too. I use backup files to send databases back and forth between clients and at my office.

I have read that when you create a backup from SQL Server 2012 there is no way to restore it onto a 2008 instance. I assumed that the compatibility level would take care of this problem, but it doesn't. Therefore, I am at a loss as to how to upgrade. Other than upgrade all my clients all at once, which is impossible, I can think of no clean way to do this.

I have the need to send a database to a client as well as receive a database from a client. This is my first version upgrade on SQL Server, so I'm new to this problem. Any ideas on how to proceed?

Answer 1

There's two things involved here: the version number of the file, and the compatibility level. When you attach a database to a newer major version (like from 2008 to 2008R2, or 2008R2 to 2012) the database version is changed permanently, and you can't attach that database to an older version again.

The compatibility level is for parsing old-school deprecated T-SQL that used to work in older versions of SQL Server. It doesn't change how the data is stored on disk.

To give the database to someone on an older version of SQL Server, you'll need to export the data and import it into another database. Tools like Red Gate's Data Compare are helpful for this.

Answer 2

The compatibility level setting is used by SQL Server to determine how certain new features should be handled. This way a DB could be migrated to a newer version of SQL without having issues with the application. The compatibility level can be changed back and forth.

Unfortunately, the backup files are not backwards compatible. One method would be to use import/export to move your data out of your current DB to your older version instance.

Answer 3

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.

Answer 4

I have found BCP to be more effective than some of the tools for getting data into an earlier version of SQL Server, and for pulling data from RDS. (Thanks @ivan_posdeev.)

I first generate the schema by right-clicking on the database in SQL Server Management Studio, Tasks, Generate scripts. Tick all objects, in advanced objects ensure everything you need is going to be scripted (stats, indexes, etc), untick "USE database" if your destination database has a different name, set the compatibility to your destination database version, and generate a file that creates your schema. Create a database on your destination and run this file on it (using osql, sqlcmd or the GUI).

To move the data, run the following query twice on the source database, first commenting the second column to generate the batch file to extract data, then commenting the first column to generate the import batch file to run on your destination. (You need to add your source and destination servers, instance names, output and input file directories, usernames and passwords. To use integrated security replace the -U and -P options with just -T.)

This supports Unicode, if you don't need that, change the -N switch in both statements to -n.

SELECT 
   'bcp SOURCEDATABASE.' + s.Name + '.' + t.NAME  + ' out d:\dbdump\' + s.Name + '.' + t.NAME  + '.dat -N -S SOURCESERVER\INSTANCE -UUSER -PPASSWORD'
--    'bcp DESTINATIONDATASE.' + s.Name + '.' + t.NAME  + ' in d:\dbdump\' + s.Name + '.' + t.NAME  + '.dat -N -S DESTINATIONSERVER\INSTANCE -UUSER -PPASSWORD -E -h TABLOCK -b 1000 -e d:\dbdump\' + s.Name + '.' + t.NAME  + '.ERRORS.dat'
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
ORDER BY 
    s.Name, t.NAME

After running check files named schema.tablename.ERRORS.dat - will include any rows that failed, empty if none did.

MSDN reference for BCP here, more friendly walk through of the BCP options here.

I have found this vastly superior to generating scripts, and any of the tools I've tried. It also runs on RDS databases (which don't permit backups). The data files generated are 30% the size of the SQL scripts, running them takes a fraction of the time, and is much more reliable. (The scripts generated by SQL Server for scripting data always tripped up, sometimes predictably sometimes not, the SQL generated was not compatible with 2008R2 (eg, used nvarchar(0), often didn't complete for no discernible reason, etc. BCP also replicates any constraint violations, such as referential integrity.).