Moving databases to new server
-
My server is dying...it's getting obsolete and it doesn't have enough umph. We currently have SQL Server 2000 Enterprise Edition on Windows Advanced Server 2000. We are installing SQL Server 2000 EE on a Windows 2003 Adv Server.
I have the user database files and the TEMPDB on a SAN system. Master, Model and MSDB are on the server itself.
Once the new server is built and SQL Server installed, I intend to stop services on the original server, move the system databases to the SAN. Then connect the new server and stop SQL Server services, rename the system database files, and move the original system database files over to the new server.
This way I should keep all of my user accounts/passwords and all of my jobs.
Has anyone done this? See any problems with this method?
-SQLBill
-
If you've installed with the same drivemappings/locations, there should be no problems, if you move all db-files to these locations (as they were at the "old" server).
Otherwize, you may have to follow the Move systemdatabases path (before or after you've switched the HW), but you'll have to start at the path-locations where you've stopped at the old server. (becaues master-db points to msdb and model at the old-server's location)
If the servername is equal, you even don't have to perform the "new servername"-procedure.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Server name and IP are changing, but that should be it. Master,Model, and MSDB are currently on the C drive and I intend to put them on the C drive on the new server.
Thanks for the input.
-SQLBill
-
If they remain at the same path, there should be no issue if your sqlserver is at exactly the same version/sp/hf...
Just perform the rename-server procedure :
-- (http://www.databasejournal.com/scripts/article.php/1496451)
Create proc uspRenameServer
@pNewName varchar(256)=null--If NULL we will attempt to rename server to the WINS machine name
/*
Purpose: renames SQL server.
Server: all
Database: DBAservice
Developed: Yul Wasserman 03/08/02
*/
AS
Declare @OldName varchar(256)
Declare @NewName varchar(256)
set @OldName=''
select @OldName=isnull(srvname,'') from master.dbo.sysservers where srvid=0
If @pNewName is NULL
Begin
create table #NName (NName varchar (256))
insert #NName exec master.dbo.xp_getnetname
select @NewName=Nname from #Nname
drop table #Nname
End
ELSE If @pNewName is not NULL
Begin
select @NewName=ltrim(rtrim(@pNewName))
End
If @OldName<>@NewName
BEGIN
IF @OldName <>''
BEGIN
print 'Attempting to drop server '+@OldName
Exec master.dbo.sp_dropserver @OldName
END
print 'Attempting to add server '+@NewName
Exec master.dbo.sp_addserver @NewName,'local'
END
If isnull(@@Servername,'')<>@NewName
Begin
Print 'Please shut down and restart SQL Server in order to complete renaming.'
End
Else If isnull(@@Servername,'')=@NewName
Begin
Print 'SQL Server is already named ' +@NewName
End
-- Update Originating_Server sysjobs !! for SQLAgent
-- zie http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE msdb..sysjobs
SET originating_server = @srv
where originating_server = @OldName
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
In case of trouble:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
holds the path to master.db and errorlog,
master..sysfiles and master..sysaltfiles hold the paths to all other dtabases...
karl
Best regards
karl
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply