none
Error: Specified cast is not valid. (SqlManagerUI)

    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 Todd McDermidMVP Tuesday, June 01, 2010 4:20 PM Backup/restore problem (From:SQL Server Integration Services)
    Tuesday, June 01, 2010 6:25 AM

Answers

  • Do "RESTORE FILELISTONLY FROM DISK = N'E:\ERP\DW_31052010.bak'"  and adjust your RESTORE command's MOVE options to match the files as indicated, so that the RESTORE command can create those files for you.

    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, June 02, 2010 3:59 PM

All replies

  • This is a tool issue which i got some times back.

    Try using TSql

    RESTORE DATABASE [MyDatabase]   
    FROM    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorks2008.bak'   
            WITH  FILE = 1,     MOVE N'AdventureWorks2008_Data'   
                                TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase.mdf',    
                                MOVE N'AdventureWorks2008_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase_5.ldf',    
                                NOUNLOAD,  REPLACE,  STATS = 10 
    RESTORE DATABASE [MyDatabase]   
    FROM    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorks2008.bak'   
            WITH  FILE = 1,     MOVE N'AdventureWorks2008_Data'   
                                TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase.mdf',    
                                MOVE N'AdventureWorks2008_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase_5.ldf',    
                                NOUNLOAD,  REPLACE,  STATS = 10 
    RESTORE DATABASE <dbname> from <path>..........

    Tuesday, June 01, 2010 6:47 AM
  • Hi,

     

    Thank's for your answer.

    I tried it, but I got following message:

    Msg 3203, Level 16, State 1, Line 1

    Read on "E:\ERP\DW.bak" failed: 38(failed to retrieve text for this error. Reason: 15105)

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

     

    BR,

    Rauno

    Tuesday, June 01, 2010 7:03 AM
  • Did you have admin rights to do this operation. Run this as Administrator.
    Tuesday, June 01, 2010 7:07 AM
  • Yes I have Admin rights,

     

    Tuesday, June 01, 2010 8:16 AM
  • send ur script
    Tuesday, June 01, 2010 10:30 AM
  • Hi,

     

    Here is my script:

    RESTORE

     

    DATABASE DW

    FROM

     

    DISK = N'E:\ERP\DW_31052010.bak'

     

    WITH FILE = 1, MOVE N'DW_Log'

     

    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DW.ldf',

     

    NOUNLOAD, REPLACE, STATS = 10

     

    and here is the message i got:

    Msg 5133, Level 16, State 1, Line 1

    Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DW.ldf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).

    Msg 3156, Level 16, State 3, Line 1

    File 'DW_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DW.ldf'. Use WITH MOVE to identify a valid location for the file.

    Msg 3119, Level 16, State 1, Line 1

    Problems were identified while planning for the RESTORE statement. Previous messages provide details.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

     

    BR,

    Rauno

    Wednesday, June 02, 2010 8:05 AM
  • Do "RESTORE FILELISTONLY FROM DISK = N'E:\ERP\DW_31052010.bak'"  and adjust your RESTORE command's MOVE options to match the files as indicated, so that the RESTORE command can create those files for you.

    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, June 02, 2010 3:59 PM
  • Any suggestions for this case? Got the same error when use SSMS to restore, but got this message, backup "created using Microsoft Tape Format version 1.240. SQL Server supports version 1.0." when use tsql to restore with move or not, filelistonly, veryfiy, etc.. confirmed the backups were fine, but were taken from 2008R2 on Win2k8 server and try to restore to 2008R2 on Win2k3 server. Thanks!

    Tuesday, August 10, 2010 6:57 PM
  • So, in addition to the issue with SSMS, you have yeat another problem, probably unrelated. And that problem is that you cannot restore from the backup, even if we take the GUI (SSMS) out of the picture. That is a bit worse and I recommend you open a new thread on this focusing on the "tape format" error message - just to get apropriate focus on the issue. I can't say what the problem is, unless thet backup was in fact done to tape, which would sort of explain the problem (or at least might lead us to the explanation).
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, August 11, 2010 5:01 PM
  • Hi,

    I am having a similar issue:

    Restore 2000 backup to 2008 express

    When I try the restore I am getting:

    Specified cast is not valid. (SqlManagerUI)

    Any help would be appreciated.


    Thanks, Doug
    Sunday, January 09, 2011 4:56 PM
  • I would fiurt try do the restore using T-SQL commands directly (not click the GUI), just in case SSMS has some bug. You can script the RESTORE command from SSMS. Also, I would try RESTORE VERIFYONLY to see if the backup is healthy.
    Tibor Karaszi, SQL Server MVP | web | blog
    Sunday, January 09, 2011 5:53 PM
  • I have seem the same error even when using restore verifyonly.

    Since I've got this database from a Customer, not sure what was the backup method used by him, therefore not sure what the incompatibility or cause of the problem is.

    -DB

     

    Thursday, January 13, 2011 5:17 PM
  • I used your script when I got the same error as the original poster using the UI. I got this error message. It was a SQL Server 2005 database and I was restoring to a 2008 instance. Could that be a problem?

    10 percent processed.
    20 percent processed.
    30 percent processed.
    40 percent processed.
    50 percent processed.
    60 percent processed.
    70 percent processed.
    Msg 3183, Level 16, State 2, Line 1
    RESTORE detected an error on page (0:0) in database "AFMISWeb" as read from the backup set.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.


    Lisa Morgan

    Tuesday, September 20, 2011 7:39 PM
  • I believe there was a bug in Management Studio when restoring from 2005 to 2008. Using the restore command from a query window worked just fine. Bug possibly fixed in more recent builds of SSMS.
    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, September 21, 2011 4:41 PM
  • When you do what you guys say:

    Restore database

    from= N'C:\BI4ALL\Base_Dados\Backup_AgenciaViagens.bak' FILE = 1,

    Appears to me the following error:

    WITH

    MOVE N'Backup_AgenciaViagens_Log'

    TO

    N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Backup_AgenciaViagens_5.ldf',, replace, stats = 10

    NOUNLOAD

    Msg 3234, Level 16, State 2, Line 1

    Logical file 'Backup_AgenciaViagens_Log' is not part of database 'agenciaViagens'. Use RESTORE FILELISTONLY to list the logical file names.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Help me , i don't understand what is happening


    Friday, February 17, 2012 8:28 PM
  • Execute below whcih will tell you what files the database were using. The logical file name os what you then use in the MOVE option.

    RESTORE HEADERONLY

    FROM DISK = N'C:\BI4ALL\Base_Dados\Backup_AgenciaViagens.bak'

    WITH FILE = 1


    Tibor Karaszi, SQL Server MVP | web | blog

    Saturday, February 18, 2012 11:40 AM
  •  

    Hi

    I've executed the script and got the same errors.

    When trying the RESTORE HEADERONLY I receive a line with the values:

    BackupName = *** INCOMPLETE ***

    Position =1

    And the rest of the fields = NULL

    What does it mean? How can I progress with the DB restore?

    Wednesday, April 04, 2012 1:18 PM
  • The database may have been backed up with a passwords.

    I just got the same error... here is my situation and solution..

    Example...

    ===================

    Here is the backup code..

    ===================

    BACKUP DATABASE [myDatabase] TO DISK = \\myComputer\backup\myData.bak with INIT, PASSWORD='password123';

    ========================

    Here is the restore command...

    ========================

    RESTORE database myDatabase FROM DISK = N'c:\backup\myData.bak' with PASSWORD='password123';


    VFP Programmer

    Monday, June 04, 2012 7:49 PM
  • That is because the backup file failed halfway through, need to restore from tape or backup the file again :)
    Thursday, July 19, 2012 1:33 PM
  • May be the back varies the sql server version.

    DA

    Wednesday, December 19, 2012 4:03 AM
  • I faced the same error but found that the issue is due to SQL Server version. Backup was from SQL 2012 & Restore was on SQL2008.
    Friday, August 09, 2013 9:22 PM
  • Hi all, i have faced same problem  but i have discoverd the problem was with backup.

    1. first the backup on the machine or instance that you did the backup

    2. if it passes, try to import on other instance

    3. go to management studio and run as administrator

    4. right click on the database and select restore database "Make sure this database does not exits"

    5. point the the backup file and up on the dropdownList select the database "The Name of real database will appear"

    6. good luck.

    for me worked this way.

    Thursday, November 14, 2013 10:13 PM
  • Thanks for this tip!

    By following these instructions, I successfully managed to restore the database.  While still in SSMS, I checked the tables to make sure that it really restored all the data.  It did.

    However, when I tried to open the application that originally gave me this error, the same message:  "Specified Cast is not valid" - then throws me out of the program.

    So, while your suggestion was right on the money, it didn't solve the underlying issue.  And of course the program doesn't provide any details, the 5 words, Specified Cast is not valid, just sit there and give me the finger.....

    Any ideas?

    Thanks!

    Richard


    RICHARDG

    Thursday, December 05, 2013 1:11 AM