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.