Move SharePoint 2007 ContentDB from SQL 2005 to SQL 2008 R2

I am in process of moving our SharePoint 2007 instance to another server, I have done this before while maintaining the same SQL server version without problem. In this instance I am trying to put the Content DB that currently exists on SQL2005 SP3 to a SQL 2008 R2 server. When I try to connect the Content DB from the 2008R2 SQL server it fails with event log entries referring to schema not matching and invalid/missing stored procedures.

I have read it is possible to use SQL 2008R2 with SP 2007 and am guessing that would work if I build a new content DB. Has anyone tried to tackle moving the content db as described.

How I migrated the Content DB was to backup the existing 2005 DB, create a new DB on 2008 R2 and restore/overwrite with the file from 2005.
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.

ealockAuthor Commented:
Yes I have looked at those, I am trying to do this first in a test environment to verify operation to minimize downtime, the MOSS server is a new build out of Windows 2008 R2 and x64 MOSS. I have done this successfully while maintain the same version of SQL (2005 SP3)

It seems to be a SQL issue with the schema and stored procedures in 2005 vs SQL 2008R2. I ran into the same issue years ago when going from 2000 to 2005 and it was all work on the SQL side that resolved it, unfortunately I didn't keep any notes.
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You aren't only migrating the database but also upgrading it so isn't an one shot move.
This article explains how to do it.

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
ealockAuthor Commented:
Well I have it working although not a definitive answer on the actual resolution, I believe Vitor was on the correct trail with the upgrade of the database being required.

Steps to resolution I took to get this content database functional:

I could not get the content database to be accessible on the target SQL server, I tried all the suggestions without any luck

I built a test environment with servers running the same OS, SharePoint and SQL that I was trying to get to work in our production environment. Brought the content database backup into the test environment and it worked using the same steps I had been trying in production

I backed up the content database on the development SQL system and brought it over to production and restored to a new db

This is probably the key, the production system had an empty content database (no site collections), when I went via the GUI to add the content database brought over from production an error occurred stating the db had to be upgraded and that it needed to be done via stsadm -o addcontent db. The content db was added via the stsadm method successfully. The empty content db was then removed leaving only the added content db available to the :80 web application. It worked.

Apologies for not having a more precise explanation of the issue and resolution. Thanks to all who contributed
ealockAuthor Commented:
While the article provided did not provide resolution I am convinced that the production SQL server needed to upgrade this database before it was uasable
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 SharePoint

From novice to tech pro — start learning today.