Solved

specified cast is not valid(sql manager ui)

Posted on 2016-08-07
5
631 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
ID: 41746668
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 4

Assisted Solution

by:Daniel Jones
Daniel Jones earned 250 total points
ID: 41748286
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
ID: 41749084
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 4

Assisted Solution

by:Daniel Jones
Daniel Jones earned 250 total points
ID: 41749889
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
ID: 41749896
Solution is very to understand and perfact.

Thanks Trideep Patel & Daniel Jones.

Thanks

Nitin Shah
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

23 Experts available now in Live!

Get 1:1 Help Now