I need to move a database from sql 2000 to sql 2005. My research suggests that I can backup a sql 2000 db from enterprise manager and restore it with sql 2005 management studio. Are there step by step directions for this or can someone tell me how to do this?
what about logins? Are they transfered in this process. My research also suggests the I use copy database wizard by this failed on me and I can't find anything in the event log or anywhere else to tell me why. Please help. Thanks
IMO, I would probably suggest backup and restore tactic for user databases. As for logins, either you dump the data into a file by doing a BCP out (bulk copy program) on master..syslogins table of the old server and BCP IN to the same table of the new server, however, since I've no experience on SQL2005, please check the structure of master..syslogins table if they're similar on both versions. As for the sysusers of user databases, you can make sp_adduser statements by doing a SELECT 'sp_adduser ' + NAME + ', ' + NAME + ' FROM MASTER..SYSLOGINS
The BCP of syslogins trick quit working in SQL server 7.0. SQL 2005 has an all new system table structure, so I greatly doubt it would work here. For the logins, look up the CREATE LOGIN command. You will want to pay especial attention to the SID parameter. If you keep the SID for each user the same for SQL 2005 as it was on SQL 2000, they will match up with no problems. If you do not supply a SID for the CREATE USER command, a new random SID will be generated, and you will have to drop and recreate the user in the database (or recreate the login with the right SID).
Any jobs/maintenance plans that will have to come over?