Few days back there was a question in our internal forum about moving SQL Server from Standalone to cluster environment. Based on my experience there are many ways to move standalone SQL Server to clustered SQL Server.
As you all know that SQL Server has two kind of databases
User databases can be moved to new location using “with move” option of Restore Database command. For system database you can follow http://msdn.microsoft.com/en-us/library/ms345408.aspx.
The purpose of this blog is not to tell what is already available :) I have a little trick to make things faster. Here we go…
First lets understand what we are doing and what’s the reason behind it. Whenever we start SQL Server, it goes to registry to find out location of master.mdf, maslog.ldf and ErrorLog files. One all three are located, logging would start and master database would be opened to know location of other databases mdf and ldf files.
I’m sure this goes without saying – you need a full backup of all databases before attempting this. I can’t imagine this failing – that’s all the more reason to remember to take backups. You need backups most when you’re (over)confident.
Lets say you Standalone SQL Server having databases on D drive and you want to decommission the server. Below are the steps.
2010-01-22 13:28:00.610 Server Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)
Happy Migration :)
DISCLAIMER: This postings is provided "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability.
I don't thing this will work for your sys DBs. especially master.
I have done this for stand alone instance (including system DBs :) ).
And why do you think so Steve? logically it should work if names are not changing (i did mention that in step 2)
I have not tried personally and its only theory.
How to do the reverse?
-- Switching from a cluster test environment to stand alone SQL 2008 servers?
What are the steps needed?
Same steps. Make sure the path of data drive is not getting changed.
if the dabatase path of standalone sql server is not the same with clustered sql? how to do ?