question

Sarit avatar image
Sarit asked

Can't restore SQL Server 2000 backup to SQL Server 2008

I have a backup file taken from a SQL Server 2000 database and I'm trying to restore it to a 2008 database. However, I'm getting this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The media family on device 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\customers.bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

sql-server-2008sql-server-2000backuprestore
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

John Sansom avatar image John Sansom commented ·
Would it be possible to post your T-SQL restore script for clarification. If you are using the SQL Server Management Studio GUI, just script out the process.
1 Like 1 ·

8 Answers

·
John Sansom avatar image
John Sansom answered

I have seen this error on a number of occassions and often it is becuase the backup file you are attempting to restore is corrupt.

Ideally you should look to source an alternative backup of the database in question.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Brian avatar image
Brian answered

That sounds like the error you get when trying to restore a backup created Via Litespeed not SQL. Are you sure that the backup file was created by SQL and not 3rd party Backup software?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso avatar image
Peso answered

You can try out Idera's new SQL Virtual Database here http://www.idera.com/Products/SQL-Toolbox/SQL-virtual-database/

to extract your data and transfer them to a SQL Server 2008 database.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Alvin Ramard avatar image
Alvin Ramard answered

I would try to restore the backup onto a SQL 2000 server. That should help you figure out whether or not the backup is corrupted.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

elian avatar image
elian answered

I successfully restored several SQL 2000 backups on SQL 2008 servers. Like said by other contributors, I suspect your bak file is corrupted or has been done using a 3rd party tool. Did you check if RESTORE VERIFYONLY works?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mikelito avatar image
mikelito answered
Hi Sarit, I have same issue, I'm running the Restore RESTORE VERIFYONLY as elian mentioned above, waiting for result...crossing fingers
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mikelito avatar image
mikelito answered
After execute RESTORE VERIFYONLY..this was the result: The file on device 'D:\xxxx.bak' is not a valid Microsoft Tape Format backup set. VERIFY DATABASE is terminating abnormally. So I don't sure about if this backup that I'm using is corrupt.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ncd avatar image
ncd answered
You might have made a backup split over two files, I got this error when a dev sent a .bak file to me for a restore - he didn't realise he had 2 backup destinations in the backup to box.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.