|
20151228 [SQL SERVER NAME CHANGE]
Posted by
nigel
on
Monday, December 28, 2015 (PST)
SQL;SQL SERVER;SQL 2005;SQL2005;
|
|
This SQL server name change steps applied to SQL2005. The use case is normal with cloning a SQL virtual server.
1. Connect to SQL server through local either by using . or (local).
2. Find the SQL server old name.
SELECT @@SERVERNAME;
GO
3. Drop the old server name.
EXECUTE dbo.sp_dropserver 'OLD NAME';
GO
4. Add the server as local.
EXECUTE dbo.sp_dropserver 'NEW NAME', local;
GO
5. Restart SQL service
6. Cleanup orphaned maintenance plans.
USE MSDB;
-- BEFORE CHECK
SELECT
*
FROM
sysmaintplan_subplans
SELECT
*
FROM
sysmaintplan_log
SELECT
*
FROM
sysjobs
SELECT
*
FROM
sysdtspackages90
-- REMOVE
DELETE
FROM
sysmaintplan_log
WHERE
plan_id IN (SELECT plan_id FROM sysmaintplan_subplans)
DELETE
FROM
sysmaintplan_subplans
WHERE
job_id IN (SELECT job_id FROM sysjobs)
DELETE
FROM
sysjobs
DELETE
FROM
sysdtspackages90
-- POST CHECK
SELECT
*
FROM
sysmaintplan_subplans
SELECT
*
FROM
sysmaintplan_log
SELECT
*
FROM
sysjobs
SELECT
*
FROM
sysdtspackages90
|
|