January 8, 2010 at 5:58 am
Hi,
AS per my knowledge, if database backup from SQL-2005 required to restored in SQL 2000, then one way is to create a blank database and use Import wizard to acquire the data from SQL 2005 database. But I don't think this is the correct approach as the targeting system may be in the remote (or client) place.
Is there any way to take the backup in SQL 2000 (or SQL version 8.0) or SQL 7.0 format itself while taking the backup from SQL 2005? Please guide me.
January 8, 2010 at 6:43 am
No, you have to export everything to get from 2005 to 2000. If the target is in a remote place, you could install a local version of 2000, import data from 2005 to 2000 locally, then take a backup of your local 2000 and send it to the remote location.
January 8, 2010 at 7:25 am
Oh. I remember, when I worked with SQL 2005 version for first time, (some 2 years before), there was an option in SQL 2005 to restore the database as version 7 or 8 compatibility. or the option may be to take backup in version 7 or 8 compatibility. I am not remembering. Now am unable to find that option from our installed server. Any more help?
January 8, 2010 at 7:34 am
You can change the compatibility level of the database in SQL Server 2005 all you want. What you can't do is take a backup of the database and restore it to an older version of SQL Server. Once the database is attached to SQL Server 2005, it is a SQL Server 2005 database regardless of compatibility level. That only controls what syntax and features are available in a specific database. For instance, a SQL Server 2005 database using the compatibility level 80 can't use CTE's.
January 8, 2010 at 7:57 am
The compatibility level determines how the code is interpreted by the server. It allows keywords that did not exist in 2000 (or 7) but do exist in 2005 to still be used in code. For example, CROSS, is a new keyword in 2005, but it was a valid field name in 2000.
Once a database is on 2005, or 2008, it's structure is changed. So it can't be attached back on an earlier version. On top of that, the backup routines work as 2005, so a backup will be in the new format.
As mentioned, only an export/import of the data will move it back to an earlier version.
January 8, 2010 at 9:30 am
rajeev_vandakar-782919 (1/8/2010)
Oh. I remember, when I worked with SQL 2005 version for first time, (some 2 years before), there was an option in SQL 2005 to restore the database as version 7 or 8 compatibility. or the option may be to take backup in version 7 or 8 compatibility.
There has never been an option to backup or restore a database as a version 7 or 8 database. What there is is the ability to specify the compatibility mode, which controls what T-SQL constructs are valid and how the query processor handles them. It does not in any way change the structure of the database or backup file however. A database on SQL 2005 is a SQL 2005 database and that cannot be changed.
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
January 9, 2010 at 5:21 am
Thank you guys. As you all told, the option might be to restore the database as version 7/8 in SQL 2005. Now the only way for me is to migrate the data from SQL 2005 to 2000 before delivery to client. Thank you very much.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply