Best practice to downgrade a Ms Sql Server to a previous version

We need to downgrade ms sql server from 2014 back to 2008 and wanted EE advice if we are doint  it correctly.  We plan to first uninstall sql 2014, then reinstall sql 2008, finally restore the sql2008 databases (note when we installed sql2014, we didn't uninstall sql2008).  Please advice if we need to consider any steps omitted.  Thank you in advance.
rayluvsAsked:
Who is Participating?

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

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

Obadiah ChristopherCommented:
What happens to the new data that was created as part of Sql 2014
0
ste5anSenior DeveloperCommented:
For a downgrade of a production machine: Do clean reinstall. Otherwise there will be components left on your machine. When this is no a problem, then simply install SQL Server 2008 R2 - I really hope you mean 2008 R2 - in parallel.

For restoring the data - as already Obadiah pointed out - neither the database files (.mdf, .ldf, .ndf) nor backup files (.bak) are compatible downwards.

Thus you need to script out all objects and create them on the new server. E.g. by using the built-in script tasks or using external tools like SQL Compare (free for trial).

To recover the data, you can either also script it out or use a linked server or SSIS with the still existing 2014 instance.  

Last but not least: Why do you think you need to do this? Can you elaborate this? I never had such a use-case.. in such cases it was always sufficient to run the database in the according compatibility level.
0

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
bbaoIT ConsultantCommented:
if SQL 2008 wasn't uninstalled, was it stopped after 2014 installed and started? did 2014 manage any data previously managed by 2008?

BTW, can you please let us know why you need or have to downgrade SQL from 14 to 08?
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Bharat BhushanSolution ManagerCommented:
Are your databases restored to SQL 2014? Then you cant downgrade at all.

You cant restore a database backup from SQL 2014 to SQL 2008 at all.
0
rayluvsAuthor Commented:
Obadiah,

The new data as part of sql2014 is not important since we have copies of the same database in thier sql version (the version we need to downgrade).

ste5an,

Why you emphazie in "I really hope you mean 2008 R2"?
For restoreing the database, we dopn;t need to do anything the DB, we have the originasl in the sql2008 version we need to downgrade to.  As soon as we have sql2008, we "attached" the DBs (yes, we are going to do a clean uninstall of sql2014.  Question, if we have the original sql2008 database we dont need to "script out all objects"?

bbao,

no,  2014 didn't manage any data previously managed by 2008.  We need to downgrade to start a process of reinstallation of series of apps that has an upgrade path.

bharat,

Yes, our DB was restores to 2014, but we can delete them, we have 2008 versions.

As to "doangrade" we just want to have sql2008 back in the computer.
0
ste5anSenior DeveloperCommented:
Why you emphazie in "I really hope you mean 2008 R2"?
Cause there is a difference between SQL Server 2008 and SQL Server 2008 R2. And I cannot recommend using SQL Server 2008.. too many issues.

Question, if we have the original sql2008 database we dont need to "script out all objects"?
Only if you install the correct SQL Server 2008 (R2) version including service packs. Otherwise you may run in the same downgrade problem. You need to identify the correct version first (e.g. using PowerShell or with any hex editor reading the DWORD at offset 0x12064).

yes, we are going to do a clean uninstall of sql2014.
This is a hard, maybe an impossible task. You need, e.g. to uninstall the .Net frameworks installed by 2014. And there are some more components.. So when you need a clean SQL Server 2008 box, then you need a clean, thus complete reinstall. This includes the OS.

Hence, to happily repeat myself:

Also, can you elaborate, why you want to do this? Cause SQL Server 2008 R2 and prior are already out of support.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you don't need to downgrade the databases but the SQL Server instance only then uninstall the SQL Server 2014 instance and install SQL Server 2008 as this will let you keep the instance name (default or other).
0
rayluvsAuthor Commented:
ste5an,

Understood; does seem complicated "clean uninstall", but we will uninstall an install with you guys advice (we want to downgrade the sql server back to sql2008 because to restart a process of installs sql and other apps all the way to sql2017 latest version)

Got it Vitor.
0
rayluvsAuthor Commented:
Thank you guys!
0
bbaoIT ConsultantCommented:
> no,  2014 didn't manage any data previously managed by 2008.  

then i don't think any technical problem or difficulty to reuse 2008 for managing 2008's data. there is NO database convertion or platform migration issue here.
0
rayluvsAuthor Commented:
Exactly.  Thank you.
0
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
SQL

From novice to tech pro — start learning today.