SQL Server - Problem in restoring Sql server 2008 back up to Sql server 2008 R2

Asked By Vimal Thavraj on 12-Jun-10 05:51 AM

In SQL Server 2008 R2 and Windows 7 Home Premium, Im receiving the following error when I try to restore the backup which is taken from SQL Server 2008.

TITLE: Microsoft SQL Server Management Studio


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)



The media family on device 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Loan.bak' is incorrectly formed. SQL Server cannot process this media family.

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476




So that I failed to get restored my DB.. 

Anyone please help me to get this work done, Any help appreciated

Thanks in advance.



Peter Bromberg replied to Vimal Thavraj on 12-Jun-10 11:42 AM
If it's not compatible, you can try:

1) stop the MSSqlServer service.
2) copy the MDF / LDF files from the database you need to temporary media or to the target machine.
3) Restart the MSSqlServer service.
4) Attach the MDF file from step 2 to the new server.

Alternative 2:

1) Use Sql Publishing Wizard to SCRIPT the entire database, all table, stored procs, etc.
2) Create a new empty database on the target machine.
3) Execute the script from step 1.
Vimal Thavraj replied to Peter Bromberg on 12-Jun-10 12:37 PM
Thanks peter for the reply,

I could not excecute your ideas since the pervious server mchine has got down and its not available now, please share your ideas in order to get the work done with the existing backup file.

will it be possible to try the backup installing in a Sql server 2008 mchine and try to take the backup again?

Mohan Raj Aryal replied to Vimal Thavraj on 13-Jun-10 05:28 PM
Since this is incompatibility issue, you need SQL Server 2008 and http://msdn.microsoft.com/en-us/library/ms177429.aspx there first. When you restore there, you will get the MDF file to be copied to R2 machine to follow Pete's first suggestion (I am not sure 100% whether this is supported though). 
After restoring the backup set to another database server, I would suggest to give a try http://msdn.microsoft.com/en-us/library/ms190775.aspx if you still see Copy Database Option on R2 option (I have not tried yet, but you can still give a try).

Also, I like Pete's second workaround to create SQL Scripts for whole database.