none
Restoring Database from .bak error: object cannot be cast from dbnull

    Question

  • I am attempting to restore a database on a Windows SQL 2008 Server from a .bak file. The .bak file was created on another machine running Windows SQL 2005 Server. It was copied between the two machines using an external HDD.

    The error message is "Object cannot be cast from DbNull to other types". It appears when I click the 'contents' button in the Specify Backup dialog box after pressing 'add' [media] to specify the name and location of the .bak file. The .bak was copied from the external HDD onto an internal HDD (d:\backups\testme.bak)

    I presume that the error message means that the contents of the file cannot be recognised. Does this mean that the .bak file is corrupt. What else might cause this problem?

    I can restore the database from a .DMP (dump) file, but would prefer to use .BAK so in future I can just restore the changed records.

    Steps

    1. Log-in as the System Administrator (sa).

    2. Select Databases and right click | Restore from Backup

    3. Restore dialog set To = Image (name of new database) and From = Device, then click '...' button

    4.  Specify Backup dialog box - click 'Add'

    5. Add Media dialog - Browse and select Image.bak (not in the default SQL directory for backups). Press OK to return to the Specify Backup dialog box.

    6. Specify Backup dialog box - click 'Contents' button and get error message "Object cannot be cast from DbNull to other types"


    will stott
    Tuesday, January 11, 2011 3:55 PM

Answers

  • Hi,

    I suspect that your backup file has been corrupted. Please also run RESTORE VERIFYONLY (http://msdn.microsoft.com/en-us/library/ms188902.aspx) statement to check this backup set. If it actually corrupted, please try to use another copy backup file of the source database.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by wpqs Wednesday, January 12, 2011 9:41 AM
    Wednesday, January 12, 2011 7:11 AM

All replies

  • You will probably need to go all scripty to fix this.

    The first command is:

    RESTORE HEADERONLY from disk = '<full path and filename of backup file>'

    Post back any errors or results.


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    Tuesday, January 11, 2011 4:39 PM
  • Hi,

    I suspect that your backup file has been corrupted. Please also run RESTORE VERIFYONLY (http://msdn.microsoft.com/en-us/library/ms188902.aspx) statement to check this backup set. If it actually corrupted, please try to use another copy backup file of the source database.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by wpqs Wednesday, January 12, 2011 9:41 AM
    Wednesday, January 12, 2011 7:11 AM
  • Looks like corruption.Another reason using WITH CHECKSUM, CONTINUE_AFTER_ERROR; within BACKUP DATABASE command

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 12, 2011 9:13 AM
  • executing a RESTORE VERIFYONLY query yielded the error message "not a valid Microsoft Tape Format backup". Therefore, it seems that my backup file is indeed corrupt.

    Makes the point that there is no point in doing backups without also doing an occasional restore!


    will stott
    Wednesday, January 12, 2011 9:44 AM
  • Yep, only the actual restore database command will 'say' whether the .BAK is corrupted or not..

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 12, 2011 11:58 AM
  • I had this problem restoring from SQL Express 2005 to 2008. I run the RESTORE VERIFYONLY as ChunSong suggested, and got an error about not been able to find C:\SQLDB\midatabase (I use a different folder for the DBs in this computer).

    So I just created C:\SQLDB folder with nothing in it. Try the restore utility again with the same error.

    Then I run the RESTORE VERIFYONLY again but this time it was successful.

    Then just opened the "New Query" window and run:

    RESTORE DATABASE MIDATABASE from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\MIDATABASE_2009.bak'  <=this is where the bak file was stored.

    An the DB was restored successfully!!!

     

    So, who knows, the restore tool is a piece of junk... its better to use the Query window.

    Regards

    Alex

    Tuesday, May 31, 2011 10:03 PM