We are installing a new instance of sql 2005 on a new server. These easiest way to move our user databases is to detach from sql 2000 server, copy the mdf and ldf to new 2005 server and attach. Is there a simple way of creating all the sql logins with passwords on the new 2005 server (assuming the user databases already have the database user setup from attaching)? Can we also transfer all the jobs (we have over 25 jobs on sql 2000)? I assume we need to create the backup device on the new server first? The data drives are different between the 2 servers.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Migrate sql 2000 jobs and sql logins
- Thread starter Luvsql
- Start date
firstly, you cannot just remount the live database files, instead do a full backup of the database on 2k and then restore onto the 05 server.
This backup will also include all your user information.
As for the jobs, you can script the jobs out but in my experiance, it would be better to manually re-create the jobs on the new server since the 2 systems are fairly different.
--------------------
Procrastinate Now!
This backup will also include all your user information.
As for the jobs, you can script the jobs out but in my experiance, it would be better to manually re-create the jobs on the new server since the 2 systems are fairly different.
--------------------
Procrastinate Now!
- Thread starter
- #3
According to Microsoft's Q314546, you can use Backup/Retore, Detach/Attach or IMport/Export wizard from sql 7.0/2000 to 2005. Are there undocumented issues doing it with attaching? Attaching should also include the database users and roles.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
