October 11, 2008 at 2:11 pm
Hi
I have a back up and I need restore this backup in a pc with SQL 2000 Server how i do?
Thank's
October 11, 2008 at 2:26 pm
if its a backup from a SQL sefver 2005 you will not be able to restore it to a SQL2000 instance. Restore it to a a SQL2005 instance first and logically extract the data and other objects using the export wizard.
IF it is a SQL200 backup look up restore filelistonly and restore database in BOL
---------------------------------------------------------------------
October 11, 2008 at 2:59 pm
Or you can try to change the compatibility level 80
try with this code then try to restore in SQL 2k!
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 80
GO
Compatility Level 80 means DB compatibility with SQL Server 2000! Andr remember that all DB created in new versions they never can downgrade an early version of SQL Server just if you take care with compatibility level!
Dugi
October 11, 2008 at 3:06 pm
sorry, a compatability level of 80 does not mean you can restore a 2005 backup to a sql 2000 instance.
---------------------------------------------------------------------
October 11, 2008 at 4:07 pm
I understan, now when i use the export, how to indicate at wizard that data are to SQL server 2000 or how to use the export wizard.
October 11, 2008 at 4:40 pm
compatibility level sets what set of syntax rules will be used for commands agains the database; it does not change "how" the data is stored or backed up. think about it...you can toggle between any compatibility level dynamically...70,80,90....of course it doesn't change the storage style...just syntax rules.
to back up to SQL 2000, you must use sql 2000; as someone already stated, you will need to transfer the data between the two using import/export. 2005 cannot create backups in a 2000 format.
Lowell
October 12, 2008 at 2:21 am
Hmm ... little bit missunderstanding here, I know the rule that you can work with DB in SQL 2005 and setting the compatibility level 80 for SQL Server 2000 then if you want to work on SQL Server 2000 you just detach DB from SQL 2005 and attach in SQL 2000 and everything works fine ...you no need backup from SQL Server 2005 to SQL Server 2000!
Am I right now, isn't it!?!?!?!??!?!?!?!?!?!?!?!? :w00t:
Dugi
October 12, 2008 at 2:41 am
Dugi (10/12/2008)
Hmm ... little bit missunderstanding here, I know the rule that you can work with DB in SQL 2005 and setting the compatibility level 80 for SQL Server 2000 then if you want to work on SQL Server 2000 you just detach DB from SQL 2005 and attach in SQL 2000 and everything works fine ...you no need backup from SQL Server 2005 to SQL Server 2000!
You cannot move a SQL 2005 database to SQL 2000 using backup/restore or detach/attach. The physical structure of the database file is different between the versions and SQL 2000 does not understand the SQL 2005 file format.
The compatibility level just affects the parser and query processor and change the way that T-SQL is interpreted and what command are permitted. It has no effect on the structure of the file.
A database attached to a SQL 2005 instance is a SQL 2005 database regardless of the compatibility level and cannot be downgraded.
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
October 12, 2008 at 2:53 am
GilaMonster (10/12/2008)
The compatibility level just affects the parser and query processor and change the way that T-SQL is interpreted and what command are permitted.
Ok I have experience from SQL Server 2008 to SQL Server 2005 ...that downgrade is impossible but the other things are wellcomed to me! Thanks for info!
October 12, 2008 at 3:21 am
Dugi (10/12/2008)
GilaMonster (10/12/2008)
The compatibility level just affects the parser and query processor and change the way that T-SQL is interpreted and what command are permitted.Ok I have experience from SQL Server 2008 to SQL Server 2005 ...that downgrade is impossible but the other things are wellcomed to me! Thanks for info!
I didn’t try to move a database from SQL Server 2008 to SQL Server 2005, but I’d be very surprised if it will work. SQL Server 2008 has features that SQL Server 2005 doesn’t have. How would SQL Server 2005 would know how to work with table variable, filestream, date datatype, etc’? How will it work with new T-SQL statements that don’t exist in SQL Server 2005? Since it won’t be able to recognize all the new features that don’t exist in SQL Server 2005, I don’t think that you can attach a database from a new version to a lower version.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 12, 2008 at 5:59 am
Adi Cohn (10/12/2008)
I didn’t try to move a database from SQL Server 2008 to SQL Server 2005, but I’d be very surprised if it will work.
It won't, regardless or whether or not new features are used.
The database files have a version stored in the header of the database's primary file. A database attached, restored or created on 2008 gets stamped with the version associated with 2008 (I don't know offhand exactly what the value is).
Each version of the DB engine understands file structures up to a certain version and will not attach/restore databases with a higher version in the header.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply