June 15, 2009 at 11:28 pm
Hi All,
I have below plan to move existing SQL server 2005 to new server
*Backup or image the whole server - production server
*restore it on another new server with different name & ip but joined to domain(I am sure the sql server will crash to start)
*Later shutdown the old production server and then rename the new server to old production server(I am not changing the already assigned IP to new server)
In this case how to start crashed sql server(i believe to change wrong server name from registry to correct name)
Please advise me know the system procedures to do this tasks.
Is there any other best practise to be followed for moving sql server.
BR,
Parthi
June 15, 2009 at 11:53 pm
How to execute these procedures if SQL server was already down,
sp_dropserver 'old server name'
sp_addserver 'new server name', 'local'
as per http://support.microsoft.com/kb/303774/en-us
BR,
Parthi
June 16, 2009 at 1:58 am
Hi
There are various ways to migrate a server, well the first thing i will ask is how much down time can you afford & what resources have you got in store(hardware), if you can afford about 15-30 minutes, then i will go for log shipping, this is more easy and trusted and mostly less painful and quick. I always prefer where ever i can, create a warm server, log ship to that server, stop your production server, do a last transaction backup, restore on warm server, switch off production , change Warm server server name and IP, then you are ready to go, Don't forget to transfer user name and other privileges and the Agent jobs
Hope this helps 🙂
June 17, 2009 at 8:20 am
Is there any other best practice to be followed for moving sql server.
I would go for mirroring, as it has an ease of setting up and in case you want to use u r old server for reporting, you can use it.
for more details: Refer BOL
Hope this helps,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 17, 2009 at 8:35 am
i would take a backup copies of all the databases and do a restore on the new server or if this is much of a hassle and too much data, i would use PLATESPIN orTDMF to do the server migrations across long distances on live servers. this will reduce the downtime and can perform live server to server migrations. (Platespin.. needs the services down).
cost effective would be to take current backups and restore it on the new server. OR Copy the SQL Server Data Folder over to the new server and Just attach the required databases or if it going to be everything including the system databases...
Just Copy the database Folder containing the user and system databases over to the new server(hpoing the new server drive and data paths are the same as old server) and start the SQL services.
hope the Fodler structure/location/drives on the new and older server are the same.
Maninder
www.dbanation.com
June 21, 2009 at 6:20 am
Hi All,
Thanks a lot for your technical suggestion and information...
But what should I do for the below situation
* Take the complete image or backup of the server A that has SQL 2005 which is in production
* Built a new server B
* Shut down server A and connect server B to domain and rename it as A and restore it with backup of Old production server
*After restoration SQL server should be running on new A server
* unfortunately renamed the new server A as B and obviously this should crash SQL server
In case of such SQL crash how should I bring it online.
BR,
Parthi
June 21, 2009 at 6:42 am
Parthipan (6/21/2009)
* Take the complete image or backup of the server A that has SQL 2005 which is in production
if you're planning to take image of the entire disk, sql server files will come with it as well. In this case while restoring from db backup use WITH REPLACE clause.
take a Full backup here. If you can take the downtime, place the db in single user mode and then take backup. After taking backup shutdown the sql server isntance. OR you'll need to take a log backup as well before shutting down the database to capture additional changes to the database.Assuming recovery model is FULL.
* Shut down server A and connect server B to domain and rename it as A and restore it with backup of Old production server
This is fine. Just as previous case, if you have taken log backup, restore the full backup with NORECOVERY Clause and then log backup with RECOVERY Clause.
*After restoration SQL server should be running on new A server
That's right.
* unfortunately renamed the new server A as B and obviously this should crash SQL server
If there are 2 machines with same name i guess there should be a network conflict and one of them would not be able to get a network address. SQL Services should still be running i believe if you're using a local system account. In case of domain account, you may face issues as services wont start(unless there is a cached copy of the login credentials on the local machine).
have you already done the migration or are you planning to do it? Also what is the recovery model of ur db? How much db can you afford? Is that downtime enough for backup/copy/restoration of the database?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply