Sql-server – SQL Server 2012 backward compatibility for backups with 2008

sql server

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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server 2008 upgrade advisor tool

The upgrade advisor will work, but you need to make sure to analyze a trace file of activity. Just pointing it to the database and running it will only analyze the TSQL code stored in the database and not any TSQL code within your application.

In addition to the upgrade advisor, you can run a Profiler trace to capture the events: "Depreciation Final Support" and "Depreciation Announcement". Both will identify code that will need to be evaluated because of syntax or reference to objects that don't exist in new version.

Check out Books Online for a decription of each of those events: Depreciation Event Category.

Sql-server – Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that’s in 2000 (80) compatibility mode

This may produce some false positives (e.g. you may have *= in a comment), but should be a good start:

SELECT 
  obj = QUOTENAME(SCHEMA_NAME(o.[object_id]))
      + '.' + QUOTENAME(o.name),
  o.type_desc
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.[object_id] = o.[object_id]
WHERE m.definition LIKE '%=*%'
OR m.definition LIKE '%*=%';

Best Answer

Related Solutions

Sql-server – SQL Server 2008 upgrade advisor tool

Sql-server – Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that’s in 2000 (80) compatibility mode

Related Question