Sql server Backup-Restore

[raghavendran]

I have two PCs. In m/c-A, I have Sql server 2008 installed and in m/c-B, Sql server 2005 is installed..

I use the following code to backup and restore directly through vb.net

Try
  vSqlText = "Backup DataBase Temple to Disk ='" & SourcePath & "'"
  vSql = New SqlCommand(vSqlText, cnnRice)
  vSql.ExecuteNonQuery()

Catch ex As Exception
   MsgBox(ex.Message, MsgBoxStyle.Critical, "Error has occured...")
End Try

Try
  vSqlText = "USE master ALTER DATABASE Temple  SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
  RESTORE DATABASE Temple FROM DISK = '" & SourcePath & "' 
  ALTER DATABASE Temple SET MULTI_USER ;"
  
  vSql = New SqlCommand(vSqlText, cnnRice)
  vSql.ExecuteNonQuery()

Catch ex As Exception
  MsgBox(ex.Message, MsgBoxStyle.Critical, "Error has occured...")
End Try

If I back-up from m/c-A (2008) and try to restore it in m/c-B (2005), it results in an error. Earlier I had Sql 2005 in both the machines. Back-up and Restore went on well without any hindrance.

The error message is:
The media family on device "E:\....\Backup.Bak' is incorrectly formed. Sql server cannot process this media family. RESTORE DATABASE is terminating abnormally

Is there any chance to restore in a different version of sql ? What i need to do ? Can anyone help me please ?

[techgnome]

You can take a back up and restore it to a server of an equal OR HIGHER version... so you can take a backup from SQL 2005 and restore it to SQL2005, SQL2008, SQL2008R2, SQL2010... etc.... what you cannot do is restore to a LOWER version. Even with SQL2008, there's a difference in the files from 2008 and 2008R2, so you can go from SQL2008 to SQL2008R2, but not the other way around.

So the bottom line is you can go upwards, but not backwards when restoring.

-tg

[raghavendran]

My situation is that I can't install Sql 2008 in m/c-B. It can have only 2005.
Now i learnt that higher version-to-lower version restore is also not possible.
I have some other method. Copy all the records from 2008 into a Access DB and from there back into 2005 with VB code.
I have P.Key-F.Key relation between certain tables. This will be a problem for me while restoring.
Whether any other simpler method is there to accomplish this ? or
How to take care of Primary key-Foreign key fields while restoring by MY METHOD(round-about) ?

[techgnome]

seems you've got a mess there. if you have SQL2008 on one, why can't it also be on the other? You're only other option is to go back to SQL2005 on the first machine, but since the database files have already been upgraded to SQL2008, that's not really going to be an option either. So it would seem you have an impossible situation. And a business case for moving all of the systems to SQL2008.

-tg