[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

specified cast is not valid(sql manager ui)

Posted on 2016-08-07
5
Medium Priority
?
6,092 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 1000 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 1000 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 1000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 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