M a r k W o r r a l l . c o m
all articles / all Coding articles

How To fix a SQL Server Database Corrupted Due to Compatibility Level

SQL Server

A subsidiary of a client I was working for inherited a set of 4 databases with some valuable data in them. When they came to look at the data they claimed they were corrupted and couldn't be opened. They were sent to us and (somehow) ended up with me.

DB Build Errors 1

I restored the .bak files and sure enough, when I tried to look at the data in the database, there was nothing there. No tables, no views, no stored procedures. I could see from the size of the files they were far from empty, so there must be data in there, it just wasn't visible.

Being a developer, not a DBA, I started going through the lesser known properties of the database, and researching what they meant. When I got to Compatibility Level, which is under Options and was set = 80, I discovered the 80 meant the database was created with SQL Server 2000. Recent versions of SQL Server can't read versions that old.

The way I got around this was to create a new database, and under Options set the Compatibility Level to “SQL Server 2008 (100)”, i.e. Compatibility Level = 100. Then after the database was created I retored the .bak files into it. I was then able to access the data in all 4 databases using a more recent version of SQL Server.

Add Comment




capthca1 capthca2 capthca3 capthca4 capthca5 capthca6