Ошибка specified cast is not valid sql

• Remove From My Forums

• Question

• My environment is:

  Operating System: Windows 7 Pro (32bit), Finnish

  I use Virtual PC including in Windows 7

  Operating system in Virtual PC: Windows Server 2008 Standard.

  SQL Sever 2008 R2

  Under SQL Server Management Studio,

  I have a database backup file created by different user in same environment (Virtual PC + Windows Server2008 + SQL Server 2008 R2)

  When I try to restore a Database from backup file I get following message:

  TITLE: Microsoft SQL Server Management Studio
  ——————————

  Specified cast is not valid. (SqlManagerUI)

  ——————————
  BUTTONS:

  OK
  ——————————

  What I made wrong ?

  • Moved by

    Tuesday, June 1, 2010 4:20 PM
    Backup/restore problem (From:SQL Server Integration Services)

As I always say, Errors are a great source of learning and I am fortunate to have you as my readers. Because one of my blog reader reported an interesting issue which is worth blogging. This is his initial email:

Hi Pinal,
Your blogs have been very helpful to me to find solution of almost any SQL problem so far. This time, I am writing directly to see if you have time and provide some help to me.
I have a backup from database in SQL Server 2008 R2. When I want to restore this backup to SQL Server, I get this error: “Error: Specified cast is not valid. (SqlManagerUI)” How to I resolve this error?
Thanks!

We then exchanged various mails with screenshots, query, output and with his patience, we were able to find the cause of the issue. Here we go to what happened behind the scenes:

Here were the steps to restore the database:

As soon as OK was clicked, this was the error.

If we click on red cross icon at left bottom, we would see below

Here is the partial text of the message.

Specified cast is not valid. (SqlManagerUI)
------------------------------
Program Location:
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)

As we can see above that SSMS is trying to populate the grid with the details about the backup. When I put profiler on my machine, it was running Restore Headeronly command on the selected file. So, I asked to run the command manually. You can refer my earlier blog for this SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

When we ran the command, we found below

Above could be because of two reasons (which I found)

1. Backup taken on SQL 2012 and Restore Headeronly was done in SQL 2008 R2
2. Backup media is corrupted.

Solution of 1^st one is easy – restore it on same or higher version. 2^nd one is a difficult situation because you need to look for another good/restorable backup. In my case, it was first situation so we were good.

Another possible reason of “Specified cast is not valid” would be password protected backups. In such backups, headeronly would return “*** PASSWORD PROTECTED ***” in the first column as shown below.

So, next time you ever see any UI error, find the T-SQL statement by using profiler and run that directly to see “real” error message.

Have you ever come across situation where UI was giving some misleading error? Please comment and share your knowledge.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Related Posts

• Remove From My Forums

• Question

• Environment is SCOM2012, patched to CU1.

  Went in this morning to add some new nodes to distribute our special SQL run-as account to some new computers recently added to monitoring. 

  Double-clicking the account in the console gives an immediate pop-up with the message:  ‘Specified cast is not valid’.  After I clear the message, my normal properties window appears but on the Distribution tab, all of my computers are gone (it’s
  blank).  I dare not add and save for fear of overwriting my list.  Has anyone seen this?  I’m not sure what would have caused this.  We have added and deleted a few computers in the last month but it’s been weeks since I last had to perform
  this operation.  The details of the message are below.  Thanks for any help.

  C

  ———————-

  Note:  The following information was gathered when the operation was attempted.  The information may appear cryptic but provides context for the error.  The application will continue to run.

  System.InvalidCastException: Specified cast is not valid.
     at Microsoft.EnterpriseManagement.Monitoring.Internal.MonitoringObjectGenerated.get_Id()
     at Microsoft.EnterpriseManagement.Common.EnterpriseManagementObject.GetObjectWrappersPostProcessing[T](ReadOnlyCollection`1 resultSet, IEnterpriseManagementObjectCreatable`1 constructorHelper)
     at Microsoft.EnterpriseManagement.Common.EnterpriseManagementObject.CreateMultiple[T](IList`1 dataAccessResultSet, EnterpriseManagementGroup managementGroup, Dictionary`2& instanceById, ObjectQueryOptions queryOptions)
     at Microsoft.EnterpriseManagement.Common.EnterpriseManagementObject.CreateMultiple[T](IList`1 dataAccessResultSet, EnterpriseManagementGroup managementGroup, ObjectQueryOptions queryOptions)
     at Microsoft.EnterpriseManagement.SecurityConfigurationManagement.GetApprovedHealthServicesForDistribution[T](ISecuredData securedData)
     at Microsoft.EnterpriseManagement.Mom.Internal.UI.Common.SDKHelper.<>c__DisplayClass2b.<GetApprovedHealthServicesForDistribution>b__2a(Object sender, ConsoleJobEventArgs e)
     at Microsoft.EnterpriseManagement.Mom.Internal.UI.Console.ConsoleJobExceptionHandler.ExecuteJob(IComponent component, EventHandler`1 job, Object sender, ConsoleJobEventArgs args)