[Previous Post] [Next Post]
In our last post, we described how random acts of installation may result in two or more instances of SQL Server on the same machine. If you are reading this post, you are likely the proud owner of such a machine. Moving SQL Server databases is a very rare event for most ISV database administrators. This post takes you through a visual step-by-step on how to move databases from one SQL Server instance to another SQL Server instance on the same machine.
Before you move databases between instances, you should determine which services each instance is running. In the prior post, we saw several instances on our example machine as shown below.
In the above figure, you will see that one instance has:
The destination instance should be one that:
Supports the most sockets or the number of sockets on the computer (SQL Server 2008 licensing is determined by sockets and not cores)
The first step is to record where the files associated with the databases are located. The steps are:
If it is not needed, then you may wish to uninstall it.
Some products may leave the database instance behind -- don't worry about that, we will deal with those below.
If needed, it could be relocated to a different machine or may be moved to a different instance by uninstalling and reinstalling.
Before starting, use the TSQL below to obtain where files are located.
SELECT db_name(database_id), Name, type_desc,physical_Name FROM master.sys.master_filesWHERE db_name(database_id) NOT IN ('master','tempdb','model','msdb')
Open Microsoft Excel and do a paste from the clipboard (Control-V).
The next step is to detach each of the databases. The steps to detach one database are shown below.
We have detached all of the databases above. The next step is to attach each one to the destination instance.
Moving databases is quick and easy. The typical problem that I have seen is people not recording locations of the databases' files and then spending time searching for them when it is time to attach. Retrieving the locations of these files at the start, and saving them to Excel should eliminate this issue and speed the moving of databases.
One more step is needed, updating connection strings to point to the correct instance. See your ISV offering installation documentation for instructions on making this change.