Solved

specified cast is not valid(sql manager ui)

Posted on 2016-08-07
5
2,689 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

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 …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

696 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