Replacing a SQL server
-
Hi guys,
I currently act as the DBA in our company - although most of my experience is on the programming side rather than admin.
A new server has been purchased and I've been tasked with finding the best way to take all the data, sp's, functions, jobs, maintenance plans, etc from the old sever and deploy on the new.
So.... what is the best way of making the new server an exact copy of the old with minimal down time?
Your help is much appreciated!
Thanks.
-
When I need to do this, I will install SQL Server software on the new server. Once I get the server set with the software, I will take backups of ALL the databases on the old server, perform a restore Master, then restore MSDB, then restore all the other databases. Your server should look the same. In essence, I approach it like I would be performing a Disaster Recovery test.
-
My process is similar except I never restore Master. I prefer to copy the users over via a script or DTS and then run a resync of the logins for each database. However if the new server is going to have the same machine name/IP address (ie meant to be an exact copy) then I will restore master. The question is is the new machine meant to completely mirror the old with the old one being redeployed/reimaged (if so restore master as dbamark said) or is the new one meant to run alongside for a bit. Both approaches are fine. You may want to test it out first.
Francis
-
Thanks for your help guys....
Can you let me know where I might find scripts for moving the users?
The machine will probably have a different name and IP - but once all data (etc, etc) has been copied then the old one would be removed.
-
1. See http://www.sqlservercentral.com/columnists/glarsen/migratingloginstoanotherserver.asp
2. If you set up the original server as a linked server on your new machine you could also execute the following code: (remember to change the IP address to whatever you call the linked server)
declare @login sysname , @password sysname
declare implogins cursor for
select name , password
from [172.139.40.20].master.dbo.syslogins
where isntname = 0 and charindex( 'repl_' , name ) = 0 and
charindex( 'distributor' , name ) = 0 and name != 'sa'
open implogins
while ( 'FETCH IS OK' = 'FETCH IS OK' )
begin
fetch implogins into @login , @password
if @@fetch_status < 0 break
exec sp_addlogin @login , @password , @encryptopt = 'skip_encryption'
end
deallocate implogins
go
3. You could also use the Transfer Logins task in DTS.
Francis
-
http://support.microsoft.com/kb/246133
Check out this link too. sp_helprevlogin works great for re-creating users on a different Instance with the same SID and password !!!
-
thanks - that has been most useful
Matt.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply