The "Crazy George" - Not best practice but worked like a charm.

Finally we replaced our Test server.

A previous post outlined part of the discussion involved and the seriously twisted suggestion that a simple file copy of the entire data directory would be all that was required to bring the DBMS back up to its original state.

Although I am sure that other people have done this, I would like to enter the phrase "Crazy George" as the term for this method in honor of the lunatic DBA who suggested, tested and finally proved to us that it worked.

Process:

The boxes where different - More and faster processors, more RAM, better disks etc..and the OS on the new box was a Service Pack ahead...

  • Installed Windows OS on a new box and gave it the same name as the old box (off network)
  • Installed SQL Server with same Service Packs as old box and ensured that the default data directory was the same as well and then stopped SQL Server and all its related services. DTC, Search, Agent etc...
  • Copied the entire data directory (effectively a whole logical drive) from the old box to the new box giving us an identical copy.
  • Removed the old box from the network and added in the new box.
  • Started SQL Server
Outcome:

It worked perfectly except for 1 small problem - The DBMS was telling us that the Full Text catalogs where empty or not populated. Issuing the RebuildAll command from EM, brought the catalogs backup instantly with a status of Fully Populated.
All Jobs, DTS packages, Security settings (including Linked Servers) where maintained and according to the users/developers , the only difference now is the speed!.
Unfortunately, there was no replication on the box, so I cannot report on that..

The file copy was the longest part being about 2 hours and the rest of the process took about 30 minutes.

From the initial post, there was only one negative response to this method, although the reasons given where not of the highest quality (ie None). Its a brand new day Neil.

Print | posted on Friday, May 07, 2004 11:16 AM

Feedback

# re: The "Crazy George" - Not best practice but worked like a charm.

left by Tara at 5/8/2004 3:21 AM Gravatar
I just did the same thing recently, except it was our production disaster recovery server. The only difference is that I wanted to change the path of the data files. The source server had F:\MSSQL2000\MSSQL... Our standard dictates F:\MSSQL... So we installed SQL Server pointing to F:\MSSQL2000 so that we could essentially do what you did. Then I brought down SQL Server, moved the databases using:

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&sd=tech

So that brought over the databases to the correct path. But there was still stuff in MSSQL directory (such as FTDATA directory) that we needed to move. So I stopped SQL Server again, manually moved the files over, then modified the registry wherever I found F:\MSSQL2000 in HKLM. It worked! We had several days to bring up this server, otherwise I would not have done this.

# re: The "Crazy George" - Not best practice but worked like a charm.

left by Derrick Leggett at 5/8/2004 7:57 AM Gravatar
The EMC SnapView clones basically do this. We're in the middle of implementing this for our staging, reporting, and development environment. Loads of fun.

# re: The "Crazy George" - Not best practice but worked like a charm.

left by HaidongJi at 5/19/2004 1:16 PM Gravatar
Moved a server this way and it worked, for the most part (more on this later). Here are the specs:
Source: W2kServerStd. SQL2kStd 8.00.818
Destination: W2k3ServerStd. SQL2kStd 8.00.818
Things to consider:
1. Both source and destination should have the same SQL SP and patch level. In my case, SP3 and latest accumulative patch. Hence version 8.00.818 (SP3 with accumulative patch), not 8.00.760 (SP3). I would have second thought if MSSQL patch levels are different. It may work, I just don't know;
2. Make sure startup account remains the same, if it is a domain account;
3. Every directory that are referred in the old server should exist in the new server. For example, backup folders.

The things I noticed are listed below. They were not necessarily caused by this operation. Some junior admin may goofed and made those errors, I just don't know;
1. I noticed some login's default database got changed;
2. I had 5 triggers on a db, but only found 4 after the move.
Comments have been closed on this topic.