web analytics

MSSQL: Error: dbbackup failed: Unable to restore database

Sometimes, you may get an error as follows while restoring a database in MSSql

Error: dbbackup failed: Unable to restore database 'test_test'
Not valid backup
The media family on device 'C:
WINDOWSTEMPlocalhosttest_testtest.bak' is incorrectly formed.
SQL Server cannot process this media family.
VERIFY DATABASE is terminating abnormally.

Most probably this error occurs when you are trying to restore an SQL backup generated in SQL 2008 to an SQL 2005 server. So, to get it correctly restored, we need to make the backup 2005 compatible. You can do it as follows.

1) Open SQL Server Management Studio2008. In 'Object Explorer', right click the database that you want to convert.

Select 'Tasks' > 'Generate Scripts'. 

2) Click 'Next'. 

3) Select database and objects
Select the database that you want to convert, and check on 'Scripts all objects in the selected databases' 

4) Convert Options
Set options:
'Script for Server Version' = 'SQL Server 2005'
'Script Data' = 'True'
'Scirpt Database Create' = 'True'. If you set this as false, you should first create a database with that name
in SQL 2005.

5) Select option 'Script to file' and get the SQL script into a file.

6) View summary and click 'Finish'. 

7) Now you got a complete database creation script with data.

8) Go to SQL 2005, and execute this script in the query analyzer. 
Note: Certain datatypes of SQL 2008 like 'date' are not compatible with 2005 and hence you may hit error
while generating the script. If it happens so, modify your 2008 database, and alter the date datatype to
datetime and then generate the script.

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>