Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Moving SQL Server 2000 to another server

    Good day,

    I currently have SQL Server 2000 SP4 running on a Windows 2000 Server. The hardware has gotten very dated and tired and on its last legs. I am in the process of setting up a new server, Windows 2003 Server, also SQL Server 2000 SP4, can't upgrade for various reasons and have to stick with SQL Server 2000.

    In the past when I had a problem and the servers where the same OS I merely stopped SQL Server and copied across all the mdf and ldf files to the new server and started the service and all worked fine, DTS's, Maintenance plans and so on came across. But now with the different OS's I can't do this as the moment I try and start SQL Server again on the new server it gives a long error.

    Does anyone know how I can copy across the master, msdb and model databases to my new server? Restore doesn't work either, I have tried that by starting the server in single user mode and doing a restore from a backup on the new server but this throws an error.

    I need to ensure that all the info that is stored in the model, msdb and master databases comes across such as users, DTS's, Maintenance Plans....etc

    Is there perhaps a script I can run that will export all the data of the system DB's so I can insert them into the system DB's on my new server? Seems the only possible option left but this I am not sure how to do?

    Please can some one help me.

  2. #2
    Join Date
    Oct 2003
    Posts
    60
    you can move users with a DTS. There is a transfer logins task you use.

    to move your DTS, you can open them on the old server and save them to the new one.

    Maintenance plans, I would suggest you just recreate them by hand. It takes little time and you can review them as you go to make sure it still makes sense.

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I would not restore master from one server to the other, you should hot have to.


    use sp_help_revlogin to transfer users and current passwords:
    How to transfer logins and passwords between instances of SQL Server

    For DTS
    select * from previous server msdb..sysdtspackages into current msdb..sysdtspackages, and adjust connection objects for new server, set up a temporary serverlink to get to other table
    insert into msdb..sysdtspackages
    select * from [serverlink].msdb.dbo.sysdtspackages

    For Agent jobs, simply extract and compile on new server

    backup and restore databases, and don't forget to fix broken users in each database.

    sp_change_users_login 'report' to list broken users
    sp_change_users_login 'username', auto_fix to repair


    also, any user error messages, backup devices, etc.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by PMASchmed View Post
    I would not restore master from one server to the other, you should hot have to.
    +2

    Quote Originally Posted by PMASchmed View Post
    use sp_help_revlogin to transfer users and current passwords:
    How to transfer logins and passwords between instances of SQL Server
    +2, done it many times, usually works like a charm

    Quote Originally Posted by PMASchmed View Post
    For DTS
    select * from previous server msdb..sysdtspackages into current msdb..sysdtspackages, and adjust connection objects for new server, set up a temporary serverlink to get to other table
    insert into msdb..sysdtspackages
    select * from [serverlink].msdb.dbo.sysdtspackages
    Never tried it before. If there are a lot of DTS packages, and this works, could be a real time saver. There is code out there that iterates through all the DTS packages and allows you to save them (either to a file or to a server).

    Quote Originally Posted by PMASchmed View Post
    For Agent jobs, simply extract and compile on new server
    +5, best approach. Don't try restoring msdb to a server w/ a different name


    Quote Originally Posted by PMASchmed View Post
    backup and restore databases, and don't forget to fix broken users in each database.
    If these are large databases and downtime is an issue, you can do a full backup, restore with no recovery, then backup the tail of the transaction log on the primary and then restore the log backup to the new server with recovery. That will reduce your downtime significantly.

    Quote Originally Posted by PMASchmed View Post
    sp_change_users_login 'report' to list broken users
    sp_change_users_login 'username', auto_fix to repair

    also, any user error messages, backup devices, etc.
    +2
    Last edited by hmscott; 01-13-10 at 16:10.
    Have you hugged your backup today?

  5. #5
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Thanks everyone I will give your suggestions a try and report back

  6. #6
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Sorry one thing I did not mention is that this new server will be named the exact same as the old server and even have the same IP address, so that when I disconnect the old server I just have to reconnect the new one and not change anything in any apps that access the SQL server or any of the pages in IIS. The only difference between the 2 servers will be that the old one is Windows 2000 Server and the new one is Windows 2003 Server, otherwise everything is to be the same. Is there any way to do the transfer of all the SQL stuff quickly now?

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by plsh View Post
    Sorry one thing I did not mention is that this new server will be named the exact same as the old server and even have the same IP address, so that when I disconnect the old server I just have to reconnect the new one and not change anything in any apps that access the SQL server or any of the pages in IIS. The only difference between the 2 servers will be that the old one is Windows 2000 Server and the new one is Windows 2003 Server, otherwise everything is to be the same. Is there any way to do the transfer of all the SQL stuff quickly now?
    I have never liked this approach, but it is doable. I am assuming that both servers will be joined to an AD domain.

    In the past what we have done is to start out with a different server name (say, SERVER_NEW).
    1. Transfer everything over (EXCEPT jobs).
    2. Remove the old server from the domain. Reboot.
    3. Shut down the old server (set SQL Services to manual start first).
    4. Remove the new server from the domain; set the SQL Services to manual start and reboot.
    5. Rename the new server. Re-IP it. Reboot
    6. Join the new server to the domain. Reboot.
    7. Start SQL Services. Verify that the services start normally. Check the error log to verify that it is using the new IP address.
    8. Rename the SQL instance (see the BOL entry about changing SQL Server host names, should be something in there about sp_dropserver and sp_addserver. Be sure that you are using BOL from SP3 as there was an omission in the original documentation)
    9. Run the scripts to add the jobs to the database (if you do this first, and then rename the server, be prepared for some chaos, as SQL will think that another server owns the job and won't let you edit it).

    It's not too terribly painful, but doable. You can avoid this by aliasing the server in DNS (it think you would use a CNAME record, or we did it by creating a separate (non AD integrated) local domain).

    Regards,

    hmscott
    Last edited by hmscott; 01-14-10 at 16:58.
    Have you hugged your backup today?

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Alternatively, I should mention one other option which I have only had to do twice (not by choice, and not by my design). In reading your scenario, it did not apply since you were upgrading from Win2K to Win2k3...

    Assuming that you have an OS backup and compatible hardware (or an intelligent restore process), you can:

    1. Remove the old server from the domain, shut it down
    2. Restore the OS backup to a new server
    3. Rebuild the master database (google rebuildm)
    4. Restore all the databases (including msdb)

    I was actually pleasantly (if unwillingly) surprised at how well this worked when I had to do it, but there were some mitigating circumstances:

    1. We had identical h/w
    2. The databases were very small
    3. The customer did not care that the server was down for 2 days (while I waited for the OS team to rebuild the server)

    Regards,

    hmscott
    Last edited by hmscott; 01-14-10 at 17:41.
    Have you hugged your backup today?

  9. #9
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Thanks to all for the help. In the end restoring MSDB sorted the problem and I ran the script above to extract all the users and then re-create them on the new server. All my DTS's, Scheduled jobs, maintenance plans etc came across and the logins are sorted thanks to the script.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •