Clicky



is there any difference in the header info of sql 2005 and R2? do they work same or differently?

asked 12/16/2011 12:16

25112's gravatar image

25112 ♦♦


10 Answers:
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?
link

answered

25112's gravatar image

25112

No.  You cannot restore a SQL backup from a later version of SQL to an earlier version.
link

answered 2011-12-16 at 08:16:34

ScottPletcher's gravatar image

ScottPletcher

I see..

so I won't be able to restore a 2008/R2 on 2005 either?
and 2008R2 can't be restored on 2008?
link

answered 2011-12-16 at 08:21:25

25112's gravatar image

25112

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.
link

answered 2011-12-16 at 08:45:15

ScottPletcher's gravatar image

ScottPletcher

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)
link

answered 2011-12-16 at 08:53:02

Lowfatspread's gravatar image

Lowfatspread

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...
link

answered 2011-12-16 at 10:00:17

Lowfatspread's gravatar image

Lowfatspread

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?

link

answered 2011-12-16 at 10:02:35

25112's gravatar image

25112

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



link

answered 2011-12-16 at 10:28:12

Lowfatspread's gravatar image

Lowfatspread

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!
link

answered 2011-12-16 at 10:41:03

ScottPletcher's gravatar image

ScottPletcher

good idea!
link

answered 2011-12-16 at 11:31:27

25112's gravatar image

25112

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/16/2011 12:16

Seen: 261 times

Last updated: 12/16/2011 03:35