Re: Move SQL Server to new hardware
From: John Bandettini (JohnBandettini_at_discussions.microsoft.com)
Date: 02/01/05
- Next message: Leon Shargorodsky: "RE: Scheduling DB Scripting"
- Previous message: Steen Persson: "Re: Backing up to Colorado external tape"
- In reply to: Alex: "Re: Move SQL Server to new hardware"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 1 Feb 2005 07:35:04 -0800
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/scripts/scriptdetails.asp?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.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: Leon Shargorodsky: "RE: Scheduling DB Scripting"
- Previous message: Steen Persson: "Re: Backing up to Colorado external tape"
- In reply to: Alex: "Re: Move SQL Server to new hardware"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
