Hello,
I'm tring to restor a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:
TITLE: Microsoft SQL Server Management Studio
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
ADDITIONAL INFORMATION:
Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0818&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476
BUTTONS:
OK
Why? and how to restore SQL 2005 DB to SQL 2000?
Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server. For more information about restoring databases, see Books Online topic RESTORE (Transact-SQL).
|||Hi,
If its any clue or consolation, I get an error 3205 in the case where I backup {example} db from SQL 2000 SP3,
1. restore example db to same SQL version on another computer.
2. restore example db to SQL 2000 upgraded to SQL express 2005
3. backup from SQL express 2005, restore to same version on another computer.
"Too many backup devices specified for backup or restore; only 64 are allowed.
(Microsoft SQL Server, Error: 3205)"
Also, when alternatatively trying to attach the .mdf and .ldf files for this SQL 2000 database to a SQL express 2005 version, I get a
"Error 602 Severity Level 21 Message Text
Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes."
Although when I ran DBCC CHECK, it returned 0 errors.
ANY RECOMMENDATIONS PLEASE !
-
PS. with respect to your problem: Did you try uninstalling SQL express and then backup from SQL 2000 and restoring from that .BAK (then re-install SQL express)?
The only way to get data from sql2005 to sql2000 is to export/import (SSIS packages can be setup to do this).
As for the 3205 problem, there was a problem with one of the SQL2000.SP3 QFE's where the backup header was written in a way that earlier versions of the server could not read. That was fixed in later versions. We recommend that you move to SQL2000.SP4.
When attempting to restore a sql2005-formatted backup, versions of SQL2000 earlier than SP4 may receive the 3205 due to changes in the format of the backup header.
As for the 602 during upgrade, that's new to me. You might want to contact our product support:
http://www.microsoft.com/services/microsoftservices/srv_support.mspx
Hope that helps.
|||
Dear ,
I have created a SQL DB 2005 ,and now my laptop have Win XP OS,that's why i can't install SQL 2005,and i want to restore the DB atleast to SQL Express installed with VS 2005..can i do that?
if you have any other suggestion please Advice...
i get excited after i knew that any SQL 2005 can't be restored to any earlier DB....what's my solution on my current state.
|||The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.
But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.
The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.
|||Moustafa, any backup created on SQL 2005 can be restored to any other edition of SQL 2005.|||i find this amazing that this is how MS decides to force an upgrade. I want to goto sql 2005 but I need to feel comfortable and if they don't allow a simple way to transform headers for backward compatibility I am seriously disappointed.
Who develops like this.......
|||Sorry to disagree PailnessProd, but on DBServers there's no safe backwards compatibility, never, as when a DB server goes to a next version, it implies structural changes, thus, making backwards compatibility pretty hard, as older versions DON'T have the structural changes. If they had, they would be the latest version.
so it's the correct way to go for Microsoft. :)
|||Steve,
Is it possible to "export all the tables from sql2005" if I'm using SQL Server 2005 Express?
Thanks,
Jason
|||Steve Schmidt wrote:
The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.
But that won't help you restore to sql2000. We can't attach or restore sql2005 databases on earlier versions. There are internal structural differences that make it impossible.
The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.
You can use the bcp utility to copy table data between any version of sql server.
I suggest you might start a new thread with a more specific question with respect to export/importing data, if that is your goal. You might get a wider audience of potential responders.
To reiterate the answer to the question "how to restore sql2005 to sql2000": it is simply not possible.
|||How to Downgrade a Database from SQL Server 2005 to SQL Server 2000
As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let say satisfactorily. I first didn’t knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!
At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.
Before beginning I must assume, firstly that the user, who will attempt such thing, has a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.
Step 1 Generating Scripts for the Database Elements and Structures
1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).
2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.
3) Set the following Elements to the following Values
a. Script Collation , set to TRUE
b. Script Database Create, set to TRUE
c. Script of SQL Version, set to SQL SERVER 2000
d. Script foreign keys, set to FALSE
e. Script Triggers, set to FALSE
Then Hit the Next button
4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
5) Click Finish
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.
Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.
Step2 Moving the data from 2005 to 2000
1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).
2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.
3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.
4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.
CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.
Step 3 Generating Scripts for the Database Foreign Keys and Triggers
Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:
1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.
2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.
3) Set all the Elements on the List to a False Value except the ones that follow:
a. Include IF NOT EXISTS , set to TRUE
b. Script Owner, set to TRUE
c. Script of SQL Version, set to SQL SERVER 2000
d. Script foreign keys, set to TRUE
e. Script Triggers, set to TRUE
Then Hit the Next button
4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.
5) At the screen that follows hit the Select All button and the Next.
6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
7) Click Finish Button.
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.
After these steps the database should be fully functional under the SQL Server 2000 edition.
www.hellasinternet.com
Panos Tzirakis & George Dounavis
|||I may have to differ with you on that point. I am trying to do just that with a small database created on my laptop running SQL Server 2005. I have created a backup and tried to restore it on the class server also running SQL Server 2005. The following is the error message:
TITLE: Microsoft SQL Server Management Studio
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
ADDITIONAL INFORMATION:
Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476
The above link is of no value as the 2005 to 2005 error occurance seems to be very rare and I have as yet found no one with a solution. I just spent an hour with the server admin for the class, and I seem to be the only one with this issue. Until a solution is found I am forced to create my assignments and work from the class server.
Since then, I have created a couple of databases, one empty, the other populated, with the exact same results. Do you have a solution for this particular error message?
|||Hello,
PanosTZ - thanks for your detailed instructions.
I've attempted to follow your instructions (panosTz). It's worked successfully for one of my databases but I'm getting the following error for the other.
"Generate Script Progress
- Determining objects in database 'RetailStore' that will be scripted. (Error)
Messages
Either the object or one of its properties is not supported on the target server version. (Microsoft.SqlServer.Smo)"
I can't get any information about the error received on any other forums or on the Microsoft KB.
Anyone who can explain what this means of point out area of fault would be much appreciated.
|||
Well, SQL Server 2005 Developer Edition is supported in Windows XP.
No comments:
Post a Comment