advanced code snippet search

Posted By

Scooter on 05/27/11


Tagged


Versions (?)

Rename SQL Server Instance

 / Published in: SQL
 

URL: http://serverfault.com/questions/273840/automating-the-rename-of-a-sql-server/274100#274100

When upgrading your SQL Server, it can sometimes be easier to rename the new server to match the old server rather than update all the workstations to point to the new server. The name of the SQL Server instance also needs to be changed. The following script will take care of this, but the last part which updates the SQL Agent jobs only works against the master server in a multi-server environment. If you're upgrading your master server, or your only server, it will work fine.

Expand | Embed | Plain Text 
  1. DECLARE @var1 nvarchar(50)
  2. DECLARE @var2 nvarchar(50)
  3. SET @var1 = convert(nvarchar(50),@@SERVERNAME)
  4. SET @var2 = convert(nvarchar(50),SERVERPROPERTY('MachineName'))
  5. EXEC sp_dropserver @var1
  6. EXEC sp_addserver @var2, 'local'
  7. UPDATE msdb.dbo.sysjobs SET originating_server = @var2
  8. GO

Report this snippet  

You need to login to post a comment.