restore SQL2008 backups in SQL2000

Question

restore SQL2008 backups in SQL2000

shen-dest

SSCrazy

Points: 2498
More actions
May 6, 2010 at 7:44 am

#222226

Hi all,
I have taken a backup in SQL Server 2008 and have to restore it in SQL2000, but i am getting the "The backed-up database has on-disk structure version 655. The server supports version 539 and cannot restore or upgrade this database.
" is there any workaroud for this issue.
Regards,
MShenel

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

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

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

What you're trying cannot be done. There is no way to take a database on a higher version and put it onto a lower version. Both backup/restore and detach/attach will fail.

You can script all the 2008 objects (make sure you script them for SQL 2000), export all the data then recreate the database on SQL 2000.

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

shen-dest SSCrazy Points: 2498 More actions · Answer 2

I am trying to move a 60G table to a SQL2000 history server.Using DTS would take days.

thanks.

Regards,
MShenel

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

It shouldn't take days configured correctly. I've copied much bigger than that in hours. DTS, SSIS or BCP are your options here. The 2008 backup cannot be restored to SQL 2000 in any way or form.

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

You ought to develop a process here to move the data regularly if this is history. A bcp out/in wouldn't take days.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 5

Basically SSIS, but you can downgrade using the Copy Database Wizard http://msdn.microsoft.com/en-us/library/ms188664.aspx

shen-dest SSCrazy Points: 2498 More actions · Answer 6

We aremoving data on daliy basis, but for some reasons the scheduled job failed for last 2 weeks and the amount of data grow.

now i am tring to bcp out data but i do get the err "

SQLState = S0002, NativeError = 208

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tblxxxxx'.

SQLState = 37000, NativeError = 8180

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be

prepared."

when i connect to server via isql,sqlcmd with the same servername and username i can query the table.

i got stuck, dont know how to handle.

Regards,
MShenel

shen-dest SSCrazy Points: 2498 More actions · Answer 7

the problem is solved. i explicitly define table_name like db_name.dbo.table_name and it worked.

my bcp command was like

"use db_name select * from table_name queryout "D:\QUERY.DAT" -c -C RAW -t "<ec>" -r "<er>" -Uuser_name -Sserver_name" but failed.

i did not get why i had to supply full name in my query.

Regards,
MShenel