Re: Move SQL Server to new hardware

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Alex (Yukon_at_nospam.nospam)
Date: 02/01/05 

Date: Tue, 1 Feb 2005 22:44:28 +0800

Found this sometime ago. It is from Tibor Karaszi. I have tried it and it
works, thanks Tibor.

----------------------------------------------------------------------------------------------------------------
One method, if you have the same directory structure on the two machines, is
to install on the new
machine, stop both SQL Servers and copy over *all* database files (mdf, ndf,
ldf). After that you
need to take care of two things (assuming the new machine has different name
from the old):

1. The machine name in master..sysservers:
EXEC sp_dropserver oldname
EXEC sp_addserver newname, LOCAL

2. The machine name for your SQL Server agent jobs. Here's my "canned
response on that":

The old machine name is still in msdb..sysjobs. Since you changed the
machine name, SQLAgent now
thinks that this job is owned by a master server. You can change

the machine names for all jobs with below SQL Statement. Warning: Hacking
system tables is not
supported and should be done with care (below should be rather

safe, though).

DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @srv

NOTE:
If this is a target server (you have jobs sent from a master server), you
have to exclude them, so
you don't transfer those jobs into local jobs:

WHERE originating_server = 'oldservname'

If you want to do this the supported way, see below KB article
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642 

-- 
Tibor Karaszi, SQL Server MVP
-----------------------------------------------------------------------------------------------------------------------------
"Randy Dalton" <RPDalton@bibb.com> wrote in message 
news:OIpxAuGCFHA.2804@TK2MSFTNGP15.phx.gbl...
> We need to move SQL to a new server hardware platform. Can you make any 
> suggestions or direct me to the relevent information. We are running 
> SQL2000 on Windows 2000 Advanced Server.
>
> Thanks

Relevant Pages

  • Re: Error for a job that uses a MSDASQL linked server
    ... the job is, i.e., on the server where you define the linked server. ... Tibor Karaszi, SQL Server MVP ... The related login has to be present in SQL1 and SQL2. ...
    (microsoft.public.sqlserver.server)
  • Re: MS SQL Backup Schedule
    ... Tibor Karaszi, SQL Server MVP ... where can I see the scheduled jobs? ... >> That dialog only creates an SQL Server agent job. ... >>> When I try to schedule a COMPLETE backup to Disk, ...
    (microsoft.public.sqlserver.server)
  • Re: Move SQL Server to new hardware
    ... Restore http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to ... > thinks that this job is owned by a master server. ... > the machine names for all jobs with below SQL Statement. ... > Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Failed to notify DBA via email
    ... then the mail will 'fail' even though ... > We have some SQL jobs those run on a schedule basis. ... > of mail server was not available but our exchnage server admin has looked ... > can go wrong in the SQL server that can cause this probelm? ...
    (microsoft.public.sqlserver.server)
  • Re: DBA cant see other jobs
    ... Well something sounds screwy with the Authentication :-) ... What's the OS on the server? ... > Terminal Service) to the SQL server in question, she can see ALL jobs. ...
    (microsoft.public.sqlserver.security)