Solved

specified cast is not valid(sql manager ui)

Posted on 2016-08-07
5
315 Views
Last Modified: 2016-08-09
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.
0
Comment
Question by:nitin_s_shah
  • 2
  • 2
5 Comments
 
LVL 9

Accepted Solution

by:
Trideep Patel earned 250 total points
Comment Utility
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
 
LVL 3

Assisted Solution

by:Daniel Jones
Daniel Jones earned 250 total points
Comment Utility
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
 

Author Comment

by:nitin_s_shah
Comment Utility
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
 
LVL 3

Assisted Solution

by:Daniel Jones
Daniel Jones earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:nitin_s_shah
Comment Utility
Solution is very to understand and perfact.

Thanks Trideep Patel & Daniel Jones.

Thanks

Nitin Shah
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now