SQL Server 2000 Transfer Logins and Associated Server Roles and Access to Specific Databases

Question

I work for a relatively small company less than 50 people and I'm probably the closest thing we have to a DBA...I'm actually a programmer but that's beside the point. We have a SQL Server 2000 with about 100 different databases. Almost all of those have an associated SQL Login and that Login is tied to a DB_OWNER database role for a particular database. We also have some logins that are set to the DB_DATAREADER database role.

We have purchased a brand new machine (current one 12 years old and worried about a hardware failure that may take our business down for an unacceptable amount of time). We are NOT upgrading the SQL Server Version. We are going to stick with 2000.

My question is what is the easiest way to do this. My thoughts are to detach all of the databases, copy them over to the new machine, and then reattach each of the databases. I'm going to keep the machine name and IP's the same and just remove old server when done so no connection strings anywhere have to be modified. That doesn't seem so bad and can easily get that done on a weekend. My problem with this method is that after I do that I need to delete User from the database, then recreate the login with username/password, and then assign the appropriate role for each user. I've only been here 5 years and I don't have all of possible usernames/passwords that each particular database and program is using. I don't want to break any existing programs or have to go to every single machine and update this...or possibly even have to find old source code and recompile...yes some of our legacy stuff has the username/password hardcoded in the source :(.

So I guess the main question is their a script that I can run on the existing server that will generate a script to run on the new machine to setup the existing logins, users, roles with same username/password as before?

If there is an easier way of transfering a sql server instance from one machine to another; I'm all ears.

FYI we have tried creating a VHD from the existing server to use in a virtual machine but have exausted that route. We never were able to get the machine to boot into windows. Think that was driver issues.

Answer 1

Unless you are running the 64-bit version of SQL server on the destination machine, the link supplied in the first comment should allow you to transfer logins successfully.

The section titled "A complete resolution to transfer logins and passwords between different versions of SQL Server" in that link provides you with a step-by-step procedure to accomplish your goal.

Although doing this is certainly very risky, you could always create an image of the existing server's drive using some tool like Norton Ghost and restore that image on the new hardware.

I have done this when migrating to a solid state drive from a traditional drive and it worked just fine although took a long time (several hours). Of course this approach won't work if you are changing the OS and may not work correctly due to hardware and driver differences.

It would certainly be much more work, but these kinds of upgrades give you a chance to correct some of the problems that are making it difficult for you to migrate the db server to a new installation (e.g.: hard-coded credentials, etc.). If you have the ability, time and desire to correct these issues, it may be worthwhile to do so.

Answer 2

I thought you might be able to shut the server down and then copy all database related files to the new server and restart the new sqlserver there having it use these copied files. Cannot find much more beside this though http://www.sqlservercentral.com/Forums/FindPost959329.aspx

This link gives you the support opinion of what to do http://support.microsoft.com/kb/314546

Considering the VHD route, did you also try a competing product? You might give VMWare a try, I have heard very good things about it and it might succeed where others failed. http://www.vmware.com/nl/products/datacenter-virtualization/vsphere-hypervisor/overview.html