Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
2

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?

share|improve this question
3  
You can have one instances of SQL Server 2008 and one instance SQL Server 2012 installed at your server at the same time with different instance names. Perhaps that would that solve your issues with clients on different versions? – Mikael Eriksson Mar 27 '12 at 20:18
Similar question on SO: stackoverflow.com/q/1744808/95710 – DForck42 Mar 28 '12 at 5:43

2 Answers

up vote 11 down vote accepted

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.

share|improve this answer
Yup, I've had this exact same problem before. Ended up scripting out the entire database and then scripting out all of the data. Red Gate's SQL Compare and Data Compare came to the rescue that day. – DForck42 Mar 28 '12 at 5:41
up vote 4 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.