Restoring .bak file from SQL Server 2005 Compact Edition

  • dave 12950

    SSC Veteran

    Points: 221

    A customer has provided me with a back up file which he says is from SQL Server 2005 Compact Edition.  I'm unable to restore it in either of the following instances I have on my machine

    1. SQL Server 2014 (via Management Studio 2014)
    2.  SQL Server 2005 (via Management Studio 2005)

    In both cases, if I try to use the GUI to restore, it just doesn't show any backups in the file.

    When I restore with the following backup command...
    RESTORE DATABASE Framework FROM DISK = 'C:\SQLBU\Framework.bak'

    I get the following error in 2005 and 2014...
    Msg 3241, Level 16, State 0, Line 3
    The media family on device 'C:\SQLBU\Framework.bak' is incorrectly formed. SQL Server cannot process this media family.
    Msg 3013, Level 16, State 1, Line 3
    RESTORE DATABASE is terminating abnormally.

    I download SQL Server 2005 compact edition, but the install didn't allow me to create a named instance?  (Not sure if something went wrong, I tried to google to see how the compact edition works, but it is so old I'm not getting any useful results)  Can anyone tell me 'how do I create a 'compact edition' instance' (so I can try to restore to that) ?  Or any other solution that will enable me to restore the .bak (so I can port it to a newer version of SQL Server)

    Thanks to anyone who can help! 🙂

  • Sue_H

    SSC Guru

    Points: 90287

    Do you know how the backup was taken, was it with any third party products?
    You may also want to try executing:
    RESTORE HEADERONLY
    FROM DISK = 'C:\SQLBU\Framework.bak'

    If that doesn't work it could just be a corrupt backup.

    Sue

  • dave 12950

    SSC Veteran

    Points: 221

    The backup is auto generated, so I only know it is 'SQL Server 2005 Compact Edition'
    Thanks for the suggestion, but the 'HEADERONLY' command generates the exact same error.

  • Sue_H

    SSC Guru

    Points: 90287

    dave 12950 - Wednesday, April 26, 2017 4:15 PM

    The backup is auto generated, so I only know it is 'SQL Server 2005 Compact Edition'
    Thanks for the suggestion, but the 'HEADERONLY' command generates the exact same error.

    Duh...now that I thought about it, CE backups aren't in the same format anyway. I thought they had a different extension by default as well. You used to use some export tool that comes with CE and it exports the database to a .sql file. Maybe the customer renamed it and it is a .sql file? Who knows - it might be worth making a copy, renaming the extension to .sql and see if you can open it in notepad.
    You probably need to ask the customer what steps they took to make the backup.

    Sue

  • dave 12950

    SSC Veteran

    Points: 221

    Thanks for the reply.

    The backup is auto generated by his legacy software, and named with a .bak extension.  Opening it in notepad shows a mixture of binary symbols and some readable sql text....(screen capture attached)

  • Sue_H

    SSC Guru

    Points: 90287

    I haven't messed around with my HP Jornada in years but I'm pretty sure you really didn't do that. I copied the database file for the backups and it wasn't a bak file. That was the Microsoft approach to backups for it. To move the database to SQL Server, I had to use some export command from the command line to create an .sql file but it wasn't a Microsoft product. It had nothing but sql statements.
    He has some other software that generates the backups then - no idea what it could be but I would doubt it is usable by SQL Server due to the format differences.
    But about your install - it doesn't have instances. You just run the setup it to install it. It's a file based system so more like Access than SQL Server.
    There were compact edition tools you could use - check your SQL Server 2005 install as that may have them. In Management Studio you could connect to it - the connect to server had an option for CE once you installed the tools. I know there are other tools out there but for some reason I can't remember the file extension for CE. Searching on that will probably pull up different tools. While it's still open, Try searching CodePlex - they probably have something up there.

    Sue

  • dave 12950

    SSC Veteran

    Points: 221

    Thanks a million for pointing me in a good starting direction.  Really appreciate your time!!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply