How to resolve Error-7150 in SQL Server

When You try to access your SQL server database and Error-7150 occurs what is the mean of this error. It means your collation get corrupted. But WHAT IS COLLATION? How it works in SQL Server Database?

Collation is a wonderful thing. It is basically related to different-different language. It's what makes sure that Greek people can see their data in a Greek order, and Germans can see theirs in a German order.

In SQL Server collations can be specified at many levels. When you install an instance of SQL Server, you specify the default collation for that instance. Each time you create a database, you can specify the default collation used for the database. If you do not specify a collation, the default collation for the database is the default collation for the instance. Whenever you define a character column, you can specify its collation. If you do not specify a collation, the column is created with the default collation of the database. You cannot specify a collation for character variables and parameters; they are always created with the default collation of the database.

But if you have a database that has different collation settings to the SQL instance it's on, it can be a pain.

Now if collation get corrupted than what steps should you taken?

You have a solution that Restore database.

But when restore a database it changes the collation setting for the new database.

Is there a way to restore and preserve the collation setting?

You can take these steps to restore database without changing collation setting:

CREATE DATABASE x COLLATE Albanian_BIN
GO
SELECT name, collation_name from sys.databases where name = 'x'

BACKUP DATABASE x TO DISK = 'C:\x.bak' WITH INIT
GO
RESTORE FILELISTONLY FROM DISK = 'c:\x.bak' WITH FILE = 1
GO
RESTORE DATABASE y FROM DISK = 'C:\x.bak'
WITH
MOVE 'x' TO 'c:\x.mdf'
,MOVE 'x_log' TO 'c:\x.ldf'
GO
SELECT name, collation_name from sys.databases where name IN('x', 'y')

If it is also not work than go for third party tools. These tools are very easy to use and You can use it without having any technical knowledge.

The best MS SQL Server recovery software in such case is Stellar Phoenix SQL Data Recovery, which recovers SQL databases created in MS SQL Server 7.0, 2000, 2005, and 2008. With the ability to recover MDF objects including NDF files, stored procedures, tables, triggers, collations, etc, this .MDF recovery tool is compatible with Windows 7, Server 2008, Vista, Server 2003, XP, and 2000.