Move SQL Server to new hardware

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
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...b;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
|||Randy
In addition you may find these articles useful.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users http://www.support.microsoft.com/?id=168001 User Logon and/or
Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Hope this helps
John
"Alex" wrote:

> 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...b;en-us;281642
>
> --
> Tibor Karaszi, SQL Server MVP
> ------
>
> "Randy Dalton" <RPDalton@.bibb.com> wrote in message
> news:OIpxAuGCFHA.2804@.TK2MSFTNGP15.phx.gbl...
>
>