Backup 2005 to 2000

Question

Backup 2005 to 2000

river1

SSC-Insane

Points: 24309
More actions
July 6, 2009 at 6:54 am

#202028

Hi,
If i have a backup from SQL Server 2005, is there any possibility of restoring it no SQL server 2000?
tks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MannySingh SSChampion Points: 12465 More actions · Answer 1

if the compatibility is set to 80 then yes you can move the database to 2000 if not.. then NO. as per my understanding.

Or create a new database on 2000 and copy the object structures over from 2005 to 2000.. then copy data... then other objects like SP/Functions.. is you are not usign the new syntax..

Maninder
www.dbanation.com

MichaelJasson SSCrazy Points: 2732 More actions · Answer 2

Do you mean that if we set compatibility level = 80 and then we take the backup of that DB. Later on this DB can be restored to sql server 2000.

Here I have a large backup of sql server 2000 and want to restore this in 2005. Should I change the compatibility level to 80? Actually the DB was corrupted last night and now the customer has given me a .bak file from sql server 2000 (legacy).

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

ps. One Orange Chip Points: 29422 More actions · Answer 3

compatibility level of 80 of a sql server 2005 database means some of the behaviours of the database to be compatible with sql server 2000.

ref. http://msdn.microsoft.com/en-us/library/ms178653.aspx

You cannot restore a database which is in sql 2005(any compatibility mode) to previous version. The only way to do this is to script objects and data, create a database in sql 2000 and then apply those scripts and bcp in the data.

ref. http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/46e3ecf9-6eab-4aa0-9453-11e7269efb6e/%5B/url%5D

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

river (7/6/2009)
If i have a backup from SQL Server 2005, is there any possibility of restoring it no SQL server 2000?

No. A SQL 2005 database is in the SQL 2005 format and cannot be restored or attached to a SQL 2000 instance, regardless of the compatibility level.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

river1 SSC-Insane Points: 24309 More actions · Answer 5

I have been testing and you can not....

e.g:

I have made a backup from a SQL Server 2000 database and restore it on a SQL Server 2005 engine.

I didn't changed the recovery model (it was on 80).

that i backed up the database from SQL Server 2005.

I have then, tried to restore on 2000, and i could not do the operation.

so, you can not restore a database that as compatibility level 80 on SQL 2005 to SQL server 2000.

Steve Jones - SSC Editor SSC Guru Points: 734605 More actions · Answer 6

If you need to restore a database from 2005 to 2000, no matter what the compatability, you can only script out objects and permissions, and run that on 2000. There is always the chance that some code will not work as 2005 has new keywords.

To move the data, you would need to use SSIS or BCP.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

river (7/6/2009)
I have then, tried to restore on 2000, and i could not do the operation.

You won't. Databases 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRocker SSCrazy Points: 2217 More actions · Answer 8

Backup, Log Shipping won't work for downgrade.

Use replication, Snapshot works in your case.