Description: While trying to restore a database using SQL Server Management Studio, this error dialogue box pops up. When we go for detailed error, there is nothing much helps us to identify the root cause of the problem. Before restoring we should follow below steps.
Solution:
1. Make sure the source and target database server version is same or not.
2. Check whether the backup database copy is not corrupted.
RESTORE VERIFYONLY FROM DISK = N’C:\Backup\12345test.bak’
3. If using FTP transfer softwares to move the backup files, make sure to set the FTP transfer mode to BINARY from ASCII.
This Error Message Article explains the Microsoft SQL Server Management Studio error message “Specified cast is not valid. [SqlManagerUI]” and details how to fix the underlying problem that caused it to appear.
Explanation
This message appeared when a client tried to restore a SQL backup file made from another instance of SQL using a newer version.
Solution
SQL is generally not backwards compatible. The original back up file must be recreated in the same version of SQL that will restore it.
Assume that you use Microsoft SQL Server 2012 Integration Services [SSIS 2012] or SSIS 2014. When you try to load an XML document that has DateTime data type and contains time zone information, you receive the following error message:
[XML Source 1 [9]] Error: The error "Specified cast is not valid." occurred while processing "XML Source 1.Outputs[r].Columns[]".
Note The SSIS XML source component sets the data type of the column as DT_DBTIMESTAMP. It does not contain time zone information. In advanced editor, when you try to change the data type of the column for both External Columns and Output Columns to preserve the time zone information, you encounter the previously mentioned error message.
Resolution
The issue was first fixed in the following cumulative update of SQL Server.
Cumulative Update 1 for SQL Server 2014 SP1 /en-us/help/3067839
Cumulative Update 6 for SQL Server 2014 /en-us/help/3031047
Cumulative Update 5 for SQL Server 2014 /en-us/help/3011055
Cumulative Update 4 for SQL Server 2012 SP2 /en-us/help/3007556
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
//stackoverflow.com/questions/13604720/sql-server-database-restore-error-specified-cast-is-not-valid-sqlmanagerui[^]
the first answer is suggesting that its because the TSQL used to do the restore hasn't specified to replace the database
Permalink
Share this answer
Posted 11-Jun-14 4:08am
Simon_Whale
Add a Solution
This content, along with any associated source code and files, is licensed under The Code Project Open License [CPOL]
For my production website, SQL Server 2008 R2 Standard [version 10.50.1600.1], and for my localhost database, SQL Server Express edition with Advanced Services [version 10.50.1600.1].
A few days ago, my SQL Server crashed, necessitating the installation of a fresh 2008 R2 Express copy on my localhost. The prior versions I restored from the Express edition worked just fine, but when I try to restore the database from the production server's.bak file, I get the following error:
Error: Specified cast is not valid. [SqlManagerUI]
When I try to restore the database using the command:
Use Master Go RESTORE DATABASE Publications FROM DISK = 'C:\Publications.bak' WITH MOVE 'Publications' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\DATA\Publications.mdf',--adjust path MOVE 'AlPublications_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\DATA\Publications.ldf'
This makes different errors:
Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing 'Publications' database. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
The versions have all been cross-checked. I think they all match, as evidenced by the picture below. Prior to today, I could successfully restore a database from the standard edition to the express edition. I attempted to reconstruct the database after deleting it. That also fails.
I'm unsure of what I'm doing incorrectly. Regarding, I would appreciate the assistance. As it appeared, the problem was solved. The backup file was damaged. It worked when I tried it with a different file.