Re: Move SQL Server to new hardware
From: Alex (Yukon_at_nospam.nospam)
Date: 02/01/05
- Next message: Eric Lovelace: "Can import from xls but what about csv???"
- Previous message: tkmm: "RE: import *.dbs (prodas) data to ms-sql"
- In reply to: Randy Dalton: "Move SQL Server to new hardware"
- Next in thread: John Bandettini: "Re: Move SQL Server to new hardware"
- Reply: John Bandettini: "Re: Move SQL Server to new hardware"
- Messages sorted by: [ date ] [ thread ]
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
- Next message: Eric Lovelace: "Can import from xls but what about csv???"
- Previous message: tkmm: "RE: import *.dbs (prodas) data to ms-sql"
- In reply to: Randy Dalton: "Move SQL Server to new hardware"
- Next in thread: John Bandettini: "Re: Move SQL Server to new hardware"
- Reply: John Bandettini: "Re: Move SQL Server to new hardware"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
