Home » Sharepoint 2010

Procedure to move all SP 2010 databases from SQL 2005 to SQL 2008 R2

I have a single SharePoint 2010 WFE with a single SQL 2005 64-bit backend.  I need to decomission the SQL 2005 server.  What is the best procedure to move all the SharePoint associated databases to SQL 2008 R2?

I have already read this MS doc on moving all databases: http://technet.microsoft.com/en-us/library/cc512725.aspx

So the actual move procedure is standard SQL fodder.

What I'm not clear about is what to do about the "other" SharePoint databases and how to reassociate them once the DBs are on the new SQL server.  For example, the Usage and Health DB is going to "Wss_Logging" and it specifies my old SQL server.  It is greyed so I cannot chagne it in Central Admin.  How do I let SharePoint know that these databases have been moved too?


8 Answers Found


Answer 1

To minimize downtime, here's how you can proceed
1) Transfer sql  Server logins from the SQL Server 2005 instance to the SQL Server 2008 R2 instance as per http://support.microsoft.com/kb/918992
2) Backup all of the SP2010 databases  on the SQL Server 2005 instance and restore them on the new SQL Server 2008 R2 instance (you can also use the detach/attach option mentioned in the TechNet article, although, I try to stay away from detach/attach to avoid database consistency issues)
3) You can either create a DNS alias that points to the new SQL Server 2008 R2 instance or use the stsadm utility or SharePoint Central Administrator to rename the SQL Server instance. For disaster recovery purposes, I configure all my server connections using DNS aliases. With DNS aliases, you don't have to worry about reassociate the databases once they are in the new SQL Server as the connection string is transparent
"BroussardGroup" <=?utf-8?B?QnJvdXNzYXJkR3JvdXA=?=> wrote in message news:a81135b2-9e49-4b39-92e0-d33f9f75ec0f...

I have a single SharePoint 2010 WFE with a single SQL 2005 64-bit backend.  I need to decomission the SQL 2005 server.  What is the best procedure  to move  all the SharePoint associated databases to SQL 2008 R2?

I have already read this MS doc on moving all databases: http://technet.microsoft.com/en-us/library/cc512725.aspx

So the actual move procedure is standard SQL fodder.

What I'm not clear about is what to do about the "other" SharePoint databases and how to reassociate them once the DBs are on the new SQL server.  For example, the Usage and Health DB is going to "Wss_Logging" and it specifies my old SQL server.  It is greyed so I cannot chagne it in Central Admin.  How do I let SharePoint know that these databases have been moved too?


Answer 2

Thanks for the response.  I don't think I explained myself well.  Your steps are covered in http://technet.microsoft.com/en-us/library/cc512725.aspx and that seems easy enough.

What I'm confused about is what to do about the SP 2010 Service Applications.  They all have databases  associated with them.  How do I reconnect those?  Or do they get rebuilt automatically?  I can't find any mention of them in any of the documentation I've dug up on the web.


Answer 3

Looking in Central Admin I'm wondering if I could just do a full farm backup then a farm restore selecting "new configuration" and then keeping all settings the same but specifying the new sql  server name in the various database fields?  Would this accomplish my goal of moving all farm db's and service application db's to the new sql server without destroying the farm?


Answer 4

The reason I specified the DNS aliases is because these Service Applications all point to a database residing in a SQL Server instance. Creating DNS aliases will take care of redirecting the Service Applications to the new SQL Server 2008 instance without having to do it the "hard way"
The approach is to shutdown all the Sharepoint-related services, move  the databases, create a DNS alias and, then, restart the SharePoint services. When the SharePoint services start up, it will look for the original SQL Server instance name which will now point to the new SQL Server 2008 instance

Answer 5

Yes you can copy farm config and for this refer to this TN article http://technet.microsoft.com/en-us/library/ee428315.aspx

Answer 6

The Move all databases  (SharePoint Server 2010) procedure was tested for moving all databases in a farm simultaneously, including the configuration, content, and service application databases.
Since I wrote the article, I'd be interested in understanding whether you followed the procedure  and used SQL Server aliases, or the stsadm -o renameserver command (or DNS aliases, as bass_player recommended) to point SharePoint to the new location for the databases, and where you ran into problems.

Answer 7

The "Move all databases" procedure  has the following Note: "The new database server must be running the same version of Windows Server and Microsoft sql  Server as the existing database server."

BroussardGroup appears to be wanting to move  their databases  from a SQL 2005 to SQL 2008 R2.  I need to do the same for a client...move all SP 2010 databases from a Win 2003 and SQL 2005 server to a new server running Win 2008 R2 and SQL 2008 R2.  Will the "Move all databases" procedure work for this?


Answer 8

We haven't written an updated version of the SharePoint Server 2007 article for upgrading your database layer yet. Refer to the article
Migrate an existing server farm to a 64-bit environment (Office SharePoint Server 2007) for recommended practices when upgrading a database server.


Setting up a new install for a client who will be using SharePoint Foundation 2010 in Standalone mode. They do not have SQL SERVER 2008 so need to utilize the Standalone option and the latest version of SQL Server 2008 Express R2. They are just getting started with SharePoint and it is unknown how much the technology will be embraced to afford to purchase SQL Server at this point. I wanted to get that explanation out of the way before everyone tells me the best practice of installing in Farm mode only! I realize the best practice but sometimes we have to roll with the punches because getting a client to begin using SharePoint on their current budget is better than never getting the chance to use it at all.

So SharePoint Foundation 2010 is installed with the default SQL Server 2008 R2 Express instance. Is there a procedure for moving the databases to a new drive? What is the default instance name that is created? I'm having a hard time locating that information in the documentation. I've done this before with WSS 3.0 without issue but wondered if there were any documents on moving the newest databases and what the default instance name is. Thank you in advance.


Will it be possible to install SharePoint 2010 on Windows 2008 R2 Web Edition and SQL Server 2008 Web Edition?


Hello All,

I need to find the BEST way to move SharePoint 2010 databases from SQL 2008 to a new SQL 2008 cluster. I have searched the net and found that MS has a technet article to move the database http://technet.microsoft.com/en-us/library/cc512725.aspx and other Blogs that suggest using SQL aliases: http://stsadm.blogspot.com/2008/06/moving-databases-easy-way.html

My current delpoyment is currenlty undergoing development and not yet deployed to the public, and I am leaning toward creating a new farm and have SharePoint taking care of creating the many databases, then attach the content database to the web application.

I need the feedback from the forum to which approach is the BEST for my scenario, since my deployment is not yet public. Also, I would like to know if anyone has tried the steps described in MS Technet article: http://technet.microsoft.com/en-us/library/cc512725.aspx .




I'm preparing a database to move from a SQL 2000 server to a SQL 2008 R2 server. Some time ago I created a VS Project on a test SQL 2008 server and copied the database to the test server. I forget the exact steps I took, but somehow in the process creating or comparing database schema, I ended up with a very helpful list of errors. The list included deprecated commands and several syntax errors (e.g. ambiguous column names, poor syntax, etc.) in the database. I planned to use the error list to make corrections to the database before copying it to the production SQL 2008 server.

Then I lost the test server (it was "repurposed" by the network admin) and my project. Now I can't remember exactly what I did to create the project, schema, and error list. Can anyone suggest what steps I should take to analyze a SQL 2000 database to identify both critical and non-critical errors that should be corrected before copying the database to a SQL 2008 server?


We are migrating an old application database from SQL 2000 to SQL 2008R2. There are 3 basic approaches that I can think of.

Take backup & restore on new server
Detach and Attach database file
Create empty database structure, and then pump data from old database to new one with SSIS

http://msdn.microsoft.com/en-us/library/ms189625.aspx says when you attach or restore 2000 data file on 2008 R2 it automatically upgrades it, which sounds good and later compatibility level can be changed to SQL 2008. One argument against this approach was it keep the data as fragmented as it was in the existing system. Whereas if we copy the data to empty tables it will be much more organised and less space consuming.

My question is, does this argument has any weight or will it really benefit copying the data?

Is it ok to work on a database attached to a SQL Server 2005 Express detatch it and move it to SQL Server 2008 Express, work on it and detach it and move it back to the 2005 Server ?

Using Backup and restore, what are the necessary step by step process to Move/migrate a SQL Server 2005 database on 'Server A' to SQL Server 2008 on 'Server B'

Pre-migration tasks, Migrations tasks, and post migration task.

Can you upgrade a SQL 2005 report model to SQL 2008 or SQL 2008 R2? We've tried to publish a SQL 2005 report model to a SQL 2008 R2 report server using Visual Studio 2005 and it looks like it doesn't work. What is the upgrade path for SQL 2005 Report Models?


I seemed cannot find a document or script describing what permissions cluster local account need to be granted in new active directory. Could somebody point me to the right direction please?

Please refrain from jokes like: “You have to destroy cluster completely, move nodes to new domain, [re]create cluster and reinstall all SQL server instances” - it’s not funny.


Currently, our Sharepoint Foundation 2010 's is installed in same server with SQL 2008 R2 express hosting the databases. We would like to move all the Sharepoint Foundation 2010 database to a new SQL 2008 R2 Standard Server. Can it be done?

In this technet document, http://technet.microsoft.com/en-us/library/cc512725.aspx, it stated "The new database server must be running the same version of Windows Server and Microsoft SQL Server as the existing database server." Does it mean that I cannot move the database to  SQL 2008 R2 Standard Server?


I have a VB.NET program that uses SQL DMO to setup the SQL Server for replication, create the publication with all the articles and start the snapshot creation. With SQL 2008 R2, DMO is unavailable (at least with SQL 2008 there was a backwards compatibility package that i could load to get it).

I have read that SMO was available in SQL 2000-2005 and i know there were some differences, but i am only using it for the replication setup. Then i saw some in the BOM about RMO.

I am a little confused about the RMO - is that just a sub-set of SMO?

My BIGGEST concern is that i write something and it works perfectly on R2 but fails on 2008 or 2005 (since we still have customers using those versions).

Can anyone point me somewhere that talks about changing from DMO replication setup to SMO/RMO.


I have been having an issue with migrating a website with a backend of sql 2005 database to a backend of a sql 2008 database. I've tried making using copy wizard, backup and restore and detach, copy reattach. The first symptom I receive is the user account(dbo) that the website accesses can't access the database. I reset the password and I start getting
Exception Details: System.Data.OleDb.OleDbException: Could not find stored procedure 'rsp_GetSetting'.

but I can still point the website to the old sql server and have it work. This is a major hold up in my project.


I have developed application using sql as backend .but now the problem what i am facing is . the database is saved as sql 2008 file . now i want to convert it as sql 2005 . but no effect takes place . plz tell me the possible solution.

hey all.

I am trying to create a database project for either sql server 2005/2008 ( I have both full versions installed) in Visual Studio 2010 RC.

I get an error that says it cannot find a dll file which is microsoft.sqlserver.management.sqlparser version 10. I can confirm that I do not have this on my C drive or in the GAC.

I have full service packs installed. Machine is up to date. I have tried repairing/re-installing sql server and visual studio with no luck.

Any ideas?



I've installed a new SS2008 R2 instance alongside a 2005 instance with the view to moving 2 of the 5 existing DBs to the new instance and leaving the others to function as usual.

Unfortunately there are a lot of SSIS packages that reference the relocating DBs so was wondering whether there was any way of setting up an alias (or similar) for these which would save me having to rework a load of packages?

I'm new to DBA-style tasks and have gone cross-eyed searching for solutions so any help would be much appreciated!




I am trying to install a new SQL server 2008 R2 (64 Bit). I need to move a small database from the old SQL Server Express 2005 (32 bit).

Making a backup of 2005 and trying to restore to 2008 R2 did not work.

I detached the SQL 2005 .mdf and the .ldf, copied to new server and tried to attach. I was using the management console to attach the database as I do not know the command line syntax. The current error is

"Could not continue scan with NOLOCK Due to Data movement. Converting Database "GMF" from version 622 to 661. Database "GMF " running the upgrade step from 662 to 25.(Microsoft SQL server 601)


Any suggestion on how to move this database would really be appreciated.






I developed CLR stored procedures assemblies using VS2005 and deployed in sql server 2005 are working fine. Currently we are migrating to sql server 2008. The same CLR assemblies are not working in 2008 version and raising following error. Here i created CLR assemblies in sql server 2008 using 'sa' user account which is having db_owner permission over 'mydatabase'.

I am getting an error message like "A .NET Framework error occurred during execution of user-defined routine or aggregate".

In sql logs showing messages like as

-- Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\Windows\Microsoft.NET\Framework\v2.0.50727\.

-- AppDomain 2 (mydatabase.dbo[runtime].1) created.


Please let me know what may be the cause for this type of issues.

Is there any specific backward compatibility settings has to be done over sql server 2008 or sql server 2008 R2 while using 2005 CLR routines?


Thanks in advance.



Hi there, I have installed the new SQL 2008 R2 and I am trying to connect the datasource in visual basic 2010.

When I choose the database I have the error: You cannot open this file because is version 661 (...) this server support versions 655 and earlier..

Are VB 2010 and SQL R2 incompatibles???





hi, can any one please let me know where cani find all the differences between SQL 2005 and SQL2008 R2. mostly looking for differences from BI perspective and Transact SQL.

workflow question:
If I create a cube in the Visual Studio edition that comes with Sql 08 R2, deploy that cube to my new Sql 08 R2 instance, than re-deploy that same cube (via deployment options in the VS solution properties) to a Sql 05 AS instance, what happens when I re-deploy it do Sql08 R2?  By virtue of deploying the cube to the Sql08 R2 AS instance, does it than take advantage of all the Sql08 R2 goodness? 

Also, I

Steve Walker

Also, how does one determine the compatibility level of an AS cube?  In Sql Server DB/Relational, there is a compatibility level.

<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure