Wednesday, January 19, 2011

Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

According to Microsoft this error can occur when all the following conditions are true: 
  • You want to restore a database backup that spans across multiple backup devices, and you have not specified more than 64 backup devices.
  • You created the database backup on a computer that is running SQL Server 2000 Service Pack 3 (SP3) (Build 2000.80.869.0) or a later build of SQL Server 2000 SP3.
  • You try to restore the database backup on a computer that is running a build of SQL Server 2000 SP3 that is earlier than 2000.80.869.0.
Ref : http://support.microsoft.com/kb/833710

But today I was trying to restore a database backup that was taken from SQL Server 2005 instance and I restoring this to a SQL Server 2000 instance and I got this error. Unfortunately, I had split the backup into 10 stripes because the database was pretty large. So my initial thoughts suggested that this is due to the fact that I had 10 striped backup files. So I spent sometime in creating a new backup with just one file. But the error kept coming. Upon further research, I found that a lot of folks saw this error when they tried to restore a backup from a 2005 instance to database on 2000 instance.

So the error seems to be misleading in some ways. Due to the fact that the structure of the backup header files were changed in SQL Server 2005, you can not restore it to a SQL Server 2000 instance.
It might be worth trying the detach-attach method to work around this problem. One could also try to use the SSIS export/import method to get the data to a 2000 instance.

For now, I decided to upgrade my instance to 2005 and restore the DB.

Ref :
http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/46e3ecf9-6eab-4aa0-9453-11e7269efb6e/
http://dbaspot.com/forums/sqlserver-server/378212-error-too-many-backup-devices-specified.html
http://www.sqlcoffee.com/Troubleshooting023.htm



2 comments:

  1. hiiiiii
    this is dinesh from india
    i am using sql 2005 and 2000 i have create a backup in sql 2005 and i want restore this backup in 2000 sql server.
    but whil i try to restore it there is an error come
    error 3205 too many backup devices specified for backup or restore only 64 are allowed, RESTORE FILELIST is terminating abnormally
    what should i do

    ReplyDelete
    Replies
    1. You can not backup a database on SQL Server 2005 and restore it to SQL Server 2000. That is not possible.

      So your best bet is to use the detach-attach method. Even this might not work in all cases so might have to export all the tables and import them into 2000. There might be some objects that are not backward compatible.

      Delete