August 12, 2009 at 9:32 am
Hi folks,
I'm trying to migrate a db on one SQL Server to another. The source is a 2008 server and the new db is being created on a 2005 server. I can backup the database in 2008 but when i come to restore it in 2005 i get an error:
Error 3205: Too many backup devices specified for backup or restore only 64 are allowed. RESTORE FILELIST is terminating abnormally
After researching i found out this can happen when restoring on a 2000 server from a 2005 backup but this isn't the case for me.
I've created a database in 2005. right clicked it -> tasks -> restore database -> from device -> i choose the .bak file and click ok and i get that error. Any suggestions as to why that would be, or another way of migrating between different version of SQL Server.
August 12, 2009 at 9:47 am
You can't restore a SQL Server 2008 backup to SQL Server 2005. If you need to move/deploy a database from SQL Server 2008 to SQL Server 2005, you need to script out the database structure on SQL Server 2008 (with no SQL Server 2008 specific features used) and run that on the SQL Server 2005 system, then use SSIS to move the data from SQL Server 2008 to SQL Server 2005.
This is regardless of the database compatacilty mode of the database. If it is attached to SQL Server 2008, it is a SQL Server 2008 database.
August 12, 2009 at 9:48 am
Databases can never be downgraded in version. A database created or attached to a SQL 2008 instance cannot be restored or attached to a SQL 2005 instance. The compat mode does not help here.
Your only options for downgrading are to script the DB out, run the scripts on 2005 then bcp the data out and load it into 2005, or to use SSIS's copy database wizard which does all that for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2009 at 10:07 am
thank you both for your replies.
I've tried using the copy database wizard but i get told that i cannot copy a database to an instance older than 2005. i think the problem is that 2000 was installed, then 2005 and 200 wasn't removed or something. will have to investigate that first
August 13, 2009 at 12:51 am
Hi
Jone,
SQl server dose not allow for backward compatibility for database you can restore SQL2K Database to SQL2k5 and SQL2K8,but it will not allow you to revert back.Better you can script your database/objects in SQL2k8 and execute on SQL2K5 and use BCP to move the data it is very faster then using SSIS.
Thanks and regards
Ashwin vp
Ashwin VP
CSC India ...
August 13, 2009 at 10:03 am
billy.jones (8/12/2009)
thank you both for your replies.I've tried using the copy database wizard but i get told that i cannot copy a database to an instance older than 2005. i think the problem is that 2000 was installed, then 2005 and 200 wasn't removed or something. will have to investigate that first
what's the compatibility level of the destination db? when you do an inplace upgrade it usually stays as 80. change it to 90 and see if it works
August 15, 2009 at 12:47 pm
How about copying the data first from 2008 version to 2005 version and then to 2000 version?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply