2005 back diff than 2008r2?

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

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
Scott PletcherSenior DBACommented:
No.  You cannot restore a SQL backup from a later version of SQL to an earlier version.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior 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.
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)
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...
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?

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

Scott PletcherSenior 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!
25112Author Commented:
good idea!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.