Post reply

Rename a Server

  • February 14, 2006 at 8:52 am

    #111394

    Maybe a dumb question..  but if you have a default SQL Server Instance running on WIN2K and you rename the actual Server name..   do you also have to rename the SQL Server and if so, how do you do that??

  • February 14, 2006 at 9:28 am

    #620743

    you better rename the sql server also

    Renaming:

    sp_dropserver 'old_name'

    go

    sp_addserver 'new_name', 'local'

    go  

    + restart sql server

    http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B303774

  • February 14, 2006 at 10:07 am

    #620765

    Thanks much!!!!!!!!!!!!!

  • February 15, 2006 at 7:55 am

    #621045

    I just had to do this task a few weeks ago... and a quick little reminder because it's not the first thing that I thought of. You must script all jobs in the SQL Server Agent BEFORE the server name is changed, delete the job(s), and then add them back (using the script(s)) AFTER the change.

  • February 15, 2006 at 7:58 am

    #621048

    Awesome.  Thank you!

  • February 15, 2006 at 10:02 am

    #621099

    I am just curious!  Why would you have to script out all your jobs under SQL Agent?  

    I have renamed a couple of my servers before and I didn't have to do anything with the jobs.  Restarting the server after you drop and than add new servername should be enough.

    Did I miss something?   I would hate to have someone do something that may not be necessary.

  • February 15, 2006 at 10:26 am

    #621110

    Right,

    Because in sql server 2000 the jobs are "attached" to a servername to allow multiple instances.

    You can script out all jobs, or you can adjust originating_server in msdb.dbo.sysjobs

  • February 15, 2006 at 11:35 am

    #621150

    Sorry, I just didn't catch on to the scripting part.  I understand why he suggested scripting now. 

    When we changed our servers name we went directly to Sysjobs and made the change.

    Thanks for turning the light on for me!!!

  • February 15, 2006 at 1:33 pm

    #621187

    Don't delete your jobs, you will loose their history.

    Here's a supported migration path :

    -- Update Originating_Server sysjobs !! for SQLAgent

    -- http://support.microsoft.com/default.aspx?scid=kb;en-us;281642

    DECLARE @srv sysname

    SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)

    UPDATE msdb..sysjobs

    SET originating_server = @srv

    where originating_server = @OldName

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 9 posts - 1 through 8 (of 8 total)

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