Database no longer working

Question

I had a working database on the server using SQL Server 2005 Express. For some reason, the person in charge of the server replaced SQL Management Studio 2005 with SQL Management Studio 2008. Without thinking, I made a connection to the database using the newer version of SQL Management Studio 2008. Now I cannot get any access to the database. (My client in up in two hours and is going to kill me! - helpppppp...)

I get the following error:

The database '???.MDF' cannot be opened because it is version 655. This server supports version 612 and earlier. A downgrade path is not supported.
Cannot open user default database. Login failed.
Login failed for user '???'.

The ??? are just used as replacement text.

What happened. What does the error mean. What can be done. Mercry, help. 

Answer 1

hi,
my uderstanding is you did not just use SSMS 2008, but you (or someone else in the staff) "ported" the 2005 database to a 2008 instance, so that it has been upgraded and modified in it's internal metadata to be used with the 2008 version..
the downgrade is not possible.. you have to resort to manual script out the relative database objects to produce all the DDL statements to recreate the database in the 2005 edition.. after you recreated the empty 2005 database with all the required database objects (tables, views, udfs, sps, ..) you can then use a linked server approach to INSERT .. SELECT all the available rows from the 2008 database to the 2005 one..
regards

---

http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools

Answer 2

Andrea is correct, the only way to upgrade a database is by attaching it to an instance of SQL Server 2008, you could not cause the database format upgrade with just Management Studio 2008 connecting to SQL Server 2005. (Which is a supported scenario.) There is a one step method to script out the database and the data that avoids the manual linked server approach that Andrea suggests.

The Generate Script Wizard in Management Studio 2008 now supports creating the INSERT statements for the data as well as the CREATE statements for the objects. You'll need to attach the database to an instance of SQL Server 2008 so that you can connect to it, but then just run the Generate Script Wizard and be sure to select the option to script out the Data as well. For good measure make sure you set the Target option to 2005, this ensures that the script you generate will not have any features in it not supported by SQL 2005.

Once you have the script file, you can connect back to your SQL 2005 instance and run the script; this will create you database back from script. Good luck.

Regards,
Mike

---

If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.