specified cast is not valid(sql manager ui)

i have  sql server r2 2008 on two different server. while restoreing database it give error as 'specified cast is not valid. (manager ui). what could be solution. both server has server collation different. how to make both server collation same?.

thanks to all in advance.
Nitin ShahAsked:
Who is Participating?
 
Trideep PatelProject LeadCommented:
The GUI can be fickle at times. The error you got when using T-SQL is because you're trying to overwrite an existing database, but did not specify to overwrite/replace the existing database. The following might work:
Use Master
Go
RESTORE DATABASE Publications
  FROM DISK = 'C:\Publications_backup_2012_10_15_010004_5648316.bak'
  WITH 
    MOVE 'Publications' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\DATA\Publications.mdf',--adjust path
    MOVE 'Publications_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\DATA\Publications.ldf'
, REPLACE

Open in new window

0
 
Daniel JonesData Research AnalystCommented:
Use below commands to investigate the backup file, and then restore:
RESTORE HEADERONLY FROM 'filename'
RESTORE FILELISTONLY FROM 'filename'
RESTORE DATABASE dbname FROM 'filename' WITH MOVE '' TO '', MOVE '' TO ''

Open in new window


The error message could be occur due to the following reasons
1. Backup taken on SQL 2012 and Restore Headeronly was done in SQL 2008 R2
2. Backup media is corrupted.

Another Solutions
1. Restore the backup file on same or higher version
2. Backup media corruption is a difficult situation because you need to look for another good/restorable backup or you can use third party SQL backup recovery software
1
 
Nitin ShahAuthor Commented:
Dear Patel & Jones

Great answer. Really help me to restore database. But can any one explain how to
 change server collation once it is installed.

Thanks for Kind help.

Nitin Shah
0
 
Daniel JonesData Research AnalystCommented:
USE master;  
GO  
IF DB_ID (N'MyOptionsTest') IS NOT NULL  
DROP DATABASE MyOptionsTest;  
GO  
CREATE DATABASE MyOptionsTest  
COLLATE Latin1_General_100_CS_AS_SC;  
GO  

Open in new window

 
After this, execute the SELECT statement to verify the collation setting.

SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'MyOptionsTest';  
GO  

Open in new window

0
 
Nitin ShahAuthor Commented:
Solution is very to understand and perfact.

Thanks Trideep Patel & Daniel Jones.

Thanks

Nitin Shah
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.