SQL Server 2008 R2

Will a db created in 2008 R2 run on 2008 non-R2?
Sheldon LivingstonConsultantAsked:
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot backup from R2 and restore on non R2...

you can however create all tables, views etc with features that are available on non-R2, and import data ...
just not with a simple backup/restore ...
Sheldon LivingstonConsultantAuthor Commented:
angelIII:  Would it be better to update the non-R2 to R2?
Sheldon LivingstonConsultantAuthor Commented:
Perhaps I should elaborate.  

Data was "converted" to an R2 db and then realized that the end user does not have R2...   so there are 3 options/concerns:

1 - Reconvert to 2008 non-R2
2 - Upgrade client to R2
3 - No concern... the R2 db will work when pushed to the clients server (which is non-R2).
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
to answer, you have to evaluate option 3 first.
if that works, well nothing to do then.

if not: option 2 has to be checked with the client

if that does not work: back to option 1
Sheldon LivingstonConsultantAuthor Commented:
angelIII:  So we're not sure if a SQL 2008 R2 db will just work under SQL 2008?

Option3 issue- If you were planning to Backup your R2 database and try to Restore to the clients sql 2008 non-R2 db then it wont work due to backward compatibility restrictions. R2 is a new version and is not backward compatibile with non-R2.

An alternative is to script out all data and database objects and run those scripts on SQL 2008. See Generate Scripts feature or run a DTS Export on R2 and an Import on clients non-R2 db. Those 2 should get around the limits of backup/restore.

1.Install an instance of SQL 2008 R2 on one machine and SQL 2008 on another
2.Open SQL Server Management Studio R2
3.Select your source Database
4.Select Tasks>Generate Scripts.
5.Select 'Script entire database and all database objects', press 'Next'
6.Select 'Save to File' and click on the 'Advanced' button
7.Select 'Script for Server Version' and select the version you want: 200/2005/2008
8.Select 'Type of data to Script' and select Schema/Data/both  
9.Click 'OK',Next and do it!
10.Copy the resulting file to the target machine with SQL 2008 [or whatever]
11.Log onto your SQL Management Studio and open the copied .sql file... be aware that there may be limitations on the file size.
There may also be issues with the order that the .SQL file inserts the data into the target database and if there are FK constraints in place, this could be an issue.... simply re-order the insert lists.
12.Once the .sql file is organised, parse it [just to sure], and then execute.

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
Sheldon LivingstonConsultantAuthor Commented:
Thank you
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 2008

From novice to tech pro — start learning today.