NEED Help - Migrating DB from 2000 / 2005

Question

Post reply

NEED Help - Migrating DB from 2000 / 2005

Bob Boursaw

SSC Eights!

Points: 865
More actions
June 4, 2007 at 10:38 am

#196260

Ok,
I have a DB that currently exists on 2000 with several SQL and NT associated logins. I would like to know the cleanest method for getting that DB and associated logins over to my new instance of 2005.
I believe I know how to do this but would love some confirmation that I'm correct.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mrivera Ten Centuries Points: 1050 More actions · Answer 1

Hi Bob,

I am currently planning a migration myself. I am planning on doing a backup/restore migration.

The db users will be restored but the logins will have to be created separately. My plan is to create them before I restore the database. The windows logins will map automatically to the users, but the sql logins won't unless they are created with the same SIDs as in the source database. You can get the SIDs from the sysxlogins table.

--mr

Bob Boursaw SSC Eights! Points: 865 More actions · Answer 2

Is there any automation process that will allow me to export the logins from one DB and them import them in to the new one? Or, is it just prefered to recreate them.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

You can use sp_help_revlogin to script all logins, SQL Server and Windows, in an instance. Download it from MS here: http://support.microsoft.com/kb/246133/en-us

I've used this for many server migrations and never had a problem with it.

Greg

chandrika5 SSCrazy Points: 2734 More actions · Answer 4

I am planning the same: backup old and restore to sql server 2005. My 2000 dbs have nothing fancy:

1. Regular maintenance plans: backup/Optimize/check integrity ...

2. Some instances have simple DTS packages.

I have to migrate about 15 instances that are on a 2-node cluster. We will have a 2-node 2005 cluster to start with.

When I run the 2005-upgrade advisor I get advisory that DTS is deprecated which I know. But what about the MSDB message:

SQL Server 2000 Meta Data Services packages are not supported :

Existing Meta Data Services packages should be moved to SQL Server storage

(in the msdb database) or to structured storage files before you upgrade.

SQL Server 2005 does not install support for SQL Server 2000 Meta Data Services;

therefore the Package Migration Wizard will be unable to access Meta Data Services

packages after upgrade in the absence of the SQL Server 2000 Client Tools.

AS all the scheduled jobs are stored in msdb, if I re-create these jobs in 2005, will that be enough to get over this message ??

Thanks,

mrivera Ten Centuries Points: 1050 More actions · Answer 5

I am also re-creating my jobs (which I have scripted).

So far, the only changes I had to make was on jobs that used sp_sendmail. It can be enabled, but I chose to use the new database mail. --no more Outlook --

For that, I am simply replacing master..xp_sendmail with msdb..sp_send_dbmail @profile_name = 'Profile Name' and changing all instances of @message to @body.

I still have to work on my DTS packages. I will post news as I have them.

-mr

chandrika5 SSCrazy Points: 2734 More actions · Answer 6

sp_sendmail is disabled by default.

Also I am not worried about the DTS packages / maintenance plans.

I am a little confused on the MSDB messages I got on the upgrade advisor.

I have one server that hosts about 6 instances, this is not clustered and the upgrade advisor can connect to one instance only. It is not able to connect to other instances, these are visible ??