SQL Server - Restoring database data from sql server 2008 to sql server 2005

when restore the data i got an error like "RESTORE HEADERONLY is terminating abnormally. Microsoft SQL Server,Error:3241".how i prevent this error reply me urgent.

back the file up as a.BAK file and run the restorefilelistonly command to see the mdf, ldf file, amd run a command with move parameter to place the mdf file and ldf file to the location from the above command and restore the database.

/*
Run this to determine what resides in the BAK file, as in the MDF and LDF files
*/

restore filelistonly from disk='E:\SQLBKUP.bak'

/*
Only use the ALTER Command IF the DB is being used
*/
alter database PRIMISPo1_SITE SET SINGLE_USER with ROLLBACK IMMEDIATE;

restore database DBNAME from disk='E:\SQLBKUP.bak'
with move 'DBNAME' to 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\SQL.mdf',
move 'PRIMISPo1_SITE_log' to 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\SQL_log.ldf'

For restoring database follow this-

When restoring, select your backup file.  Next, click 'Options'  in the left pane (SQL Server 2005's management studio). 

In the right side, you will now see 'Restore options' (Check "Overwrite the existing database") and "Restore the database as:".  The latter is the important part.

There are two columns in the "Restore the database as:" section.  The left side is the original name of the files, and the right side is what you want to change the names of the files to.

Changing the name of the backup file does NOT change the name of the original .mdf and .ldf files.

In the right column, scroll all the way to the right to see the file name, and change it to your new preferred name: Josie.mdf for the primary file and Josie.ldf for the log file.

The actual names you give to the files do not have to have anything to do with the database name, but should so that you can easily reference them if backing up or physically moving/copying files.

Hi

But there are some alternatives which can help you to restore a database to a lower version of SQL Server. I divide into separate parts.

1. http://www.linglom.com/2009/08/14/restore-database-from-sql-server-2008-to-sql-server-2005-part-2-generate-sql-server-scripts-wizard/. The solution creates a SQL Server script file using a wizard. Then, you simply execute the script file on SQL Server 2005 or SQL Server 2000. So you will get everything as same as the source database on the destination. But there are some disadvantages:
   • If the source database contains lots of data, you will have a large script file.
   • The generated file is a plain text. Anyone who has access to the file can read it. So you should delete the script file after the restoration.
2. http://www.linglom.com/2009/08/17/restore-database-from-sql-server-2008-to-sql-server-2005-part-3-export-data-wizard/. This solution exports data to the targeted SQL Server using a wizard. It is more secure and effective than the first solution. But you can only export tables and views only.

refer
http://www.sqlservercentral.com/Forums/Topic584539-357-1.aspx
http://msdn.microsoft.com/en-us/library/ms187048.aspx

This error can occurs due to many reasons but you must ensure following before you start for any solution, it would save your lot of time.

1) Ensure Backup copy of Database is good.
- Take a backup and store on machine where you tried to take backup.
- Now create one dummy database and try to restore that database.
- If you are able to restore that database successfully on machine backup was created, than your backup copy is good.

You should alternately try taking backup using following command

BACKUP DATABASE NorthwindDB
TO DISK='C:\HostingSpaces\MyBackupCopy_NorthwindDB.bak' with FORMAT

If you are able to take backup successfully than Backup copy is good.

2) Ensure Backup copy doesn't get corrupted during dowload.
In my case, I have created a backup copy on Hosting Server, than stored it in .Zip Format, and than try to download it using FileZila, with default settings of filezila transfer type, i.e. Auto.

Till this point everything was going good, but still I was not able to restore DB.

3) Important : Open SQL Query window and check version of your SQL Server.
Run following command and see the output.

Select @@Version

Cause of Error
Even though you using SQL Server 2008, but it was connected to SQL Server 2005 instance on the machine i was trying to restore.

Remember:  Restoring Database from lower version to higher version would not give error. i.e. SQL Server 2005 to 2008 would not give error.  But restoring from higher version to lower version will always result in error.  In my case  as i was connected to SQL Server 2005 instance it results me in above error.

You should validate that the instance is the right version by "SELECT @@version".


Solution
You need to fix the connection so it is using the SQL 2008 instance.

Try to run the http://www.microsoft.com/express/database/ program again, and during Name Instance configuration, specify a Name Instance with a different name.  Example: MachineName\instancename

After installation, logged in with New Instance name created.

Try to create database, create tables for database and try to run restore again.  It will work this time.

refer this for more details
http://dotnetguts.blogspot.com/2010/06/restore-failed-for-server-restore.html

hi,

No, you can't restore a backup from a higher version of SQL Server on a lower version of SQL Server. Rather you can generate the script which will create the database structure and you can run those script in sql server 2008.
To create the scripts, run the "Generate SQL Server Scripts" wizard in SQL Server Management Studio by right clicking on the database and selecting "Tasks –> Generate Scripts."

Hope this will help you

Hi Srinu,

You can NOT restore a SQL server 2008 database backup to a SQL server 2005 instance , it is not supported as the backup are not backward compatible.

you can just export the data from 2008 to 2005 by some other means (Import export wizard , BCP or SSIS package).