2005 back diff than 2008r2?



is there any difference in the header info of sql 2005 and R2? do they work same or differently?
LVL 5
25112Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
No.  You cannot restore a SQL backup from a later version of SQL to an earlier version.
0
 
25112Author Commented:
the database with compatibility 80 that is on R2- i am not able to back it up and restore it on a sql 2000 server.

if i put the compatibility 80 database on sql 2005, will i be able to back it up and later restore it on sql 2000 server?
0
 
25112Author Commented:
I see..

so I won't be able to restore a 2008/R2 on 2005 either?
and 2008R2 can't be restored on 2008?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
Yes; you will NOT be able to restore 2008 any to 2005 any.

2008R2 and 2008 I'm not sure, since they are both the same version, 2008.
0
 
LowfatspreadConnect With a Mentor Commented:
the golden rule is restore to the same version that you backed up with...

usually you will be allowed to restore with the next version of the product from the one that you have the backup for ... but that will convert all the internal structures to there new formats....

if you want to copy data from a higher database to version then
either use replication or BCP (but you need to be aware of version dependencies)


what exactly are you trying to do, and why (one-off, regular, etc)
0
 
LowfatspreadConnect With a Mentor Commented:
higher database to version


sorry a database in a higher version of the product to a lower version / edition...

database compatability levels may also come into play but that doesn't mean that the backup is a copy of the format that that version produces...
0
 
25112Author Commented:
thanks for your input..

>>what exactly are you trying to do, and why (one-off, regular, etc)

it is one-time..

big database several GBs need to brought from 2008 to 2000.. there are lot of tables.. both replication and BCP seem to be more involved projects.. are there any other options available?

0
 
LowfatspreadConnect With a Mentor Commented:
not really ... you could script the data  out as Insert statements....

but thats really the same effort as setting up a set of bcp'stable data

either way you need to ensure that the table datatypes are compatible

e.g. convert DATE and TIME datatypes to Datetimes
      bigint to Int?
      (n)varchar(max) to (n)Text....

you may also have collation issues...

replication maybe an easier option,,,

in any case it will not be a simple task, and the testing effort will probably be significant....

any triggers, constraints, identity columns etc to consider....

good luck



0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You might try snapshot replication and see if thet can put a copy of the db on the older SQL version for you.

Yes, the other methods are a pain, esp. for a one-time thing!
0
 
25112Author Commented:
good idea!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.