Restore Problem SQL Server Version?

Question

I am getting following error.

Restore failed for Server

I have recently upgraded SQL Server 2005 Express to SQL Server 2008 Express.

• I have taken a Backup on Production Server, having SQL Server 2008
• Backup was good and I tried restoring Backup on Production Server, that works great.
• Than I downloaded that backup file in .Zip format with FileZila Software (And that is good too).
• But when I tried to restore that file I am getting following error.

Restore failed for Server

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

------------------------------ ADDITIONAL INFORMATION:

The media family on device 'C:\go4sharepoint_1384_8481.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.4053&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

Things I tried which didn't worked

Attempt 1 Try to take backup using following command

BACKUP DATABASE go4sharepoint_1384_8481 TO DISK='C:\HostingSpaces\dbname_jun14_2010_new.bak' with FORMAT

And tried restore that file.

Attempt 2 Somebody wrote to test Version of my SQL Server. When I tried to run following command in SQL Server 2008

Select @@Version

It is giving following output Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

My Production Database is in SQL Server 2008, and I am trying to restore DB in SQL Server 2008 but above version is showing as if i am using SQL Server 2005.

Any idea how can I restore SQL Server 2008 DB.

Many Thanks.

Answer 1

Is it possible that, instead of upgrading your 2005 instance to 2008, you instead installed 2008 side-by-side with 2005, and you're connecting to your existing 2005 instance? Look for other instances on your machine.

Answer 2

You cannot restore a backup from a higher SQL Server version on a lower version - this has never been able in SQL Server.

If you have a SQL Server 2008 backup, you can only restore that on a SQL Server 2008 (or later) machine - but not on a 2005 box. No trick, no workaround, no way to do it.

Answer 3

You have 2 options here.

Use WITH REPLACE while using the RESTORE command. This will overwrite the existing database and restore the database state represented by the backup files.

Delete the database to which you are trying to restore to and restore again using RESTORE command. This will create a new database with the state represented by the backup files.

Please note that in both these options you will lose the existing data of the database you are trying to restore to.

Answer 4

Make sure it is installed correctly - in your program files\microsoft sql server directory there whould be a 100/150 folder. Check out the install log in setup bootstrap\log.

Answer 5

As described above the reason for this behaviour is that by default SQL Server doesn't allow restoring a database backup to an older version of SQL Server, regardless of the compatibility level of the database the backup was taken from.

You can try to:

1. Start SQL Server Management Studio and connect to the instance where the database resides
2. In the database context menu navigate to Tasks | Generate Scripts…. This will invoke the Generate and Publish Scripts wizard
3. In the Introduction step of the wizard click Next
4. In the Choose Objects step of the wizard make sure that the Script entire database and all database objects option is selected and click Next
5. In the Set Scripting options step of the wizard:
6. Select the Save scripts to a specific location option
7. Specify whether the database objects and data will be scripted to a single file multiple files, as well as the path and encoding of the generated scripts
8. Click Advanced
9. In the Advanced Scripting Options dialog that will appear:
10. Specify the version of the SQL Server you need the database restored to in the Script for Server Version option
11. In the Script for Server Version option specify Schema and data
12. Set the following options to True: Script Logins Script Full-Text Indexes Script Triggers Click OK Click Next
13. In the Summary step of the wizard click Next
14. In the Save or Publish Scripts step of the wizard click Finish
15. Execute the generated SQL script(s) against the older SQL instance

Although the procedure listed above should work: 1. It doesn’t migrate all of the database objects (e.g. sequences or queues) 2. It might fail if your database contains dependencies which SQL Server fails to recognize (e.g. dependencies to databases residing on linked servers)

You can take a look at the rest of the article on Solution center. There is also third party tools you can take in consideration.