May 23, 2005 at 3:39 am
Hi guys!
I'm moving SQL Srv 2000 SP3 from an old server to a new one!
I want to keep everything exactly the same (dbs, logins, jobs, dts, backups, etc).
Do you have a general concept on how to do this?
Any special warnings on this process?
THX!
--
Bruno
May 23, 2005 at 4:17 am
See if this helps: http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 23, 2005 at 7:23 pm
THX for the link!
I had already checked it out....
I was just looking for an article with stuff to watch out for!
THX anyway again!
May 24, 2005 at 6:59 am
Hi Bruno,
Just to weigh in from the MS PSS section. The article Frank linked is what we would have given you had you called in for assistance.
You can detach your user databases from the old server and just copy the files over.
You will want to use backup/restore for the system databases. Once you have copied the data and log files for the user databases in place (assuming the drive structure and all the paths are the same.) you can restore the master, msdb and model databases.
Once Sql Server is restarted your user databases should all be present.
As extra protection (and you should have this for your disaster recovery plan in any event.) you should script out your users, jobs and replication, and save any dts packages to structured storage files. Links to articles on how to do this are in the main article.
Enjoy
Richard L. Dawson
Microsoft Sql Server DBA/Data Architect
I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
May 24, 2005 at 11:01 am
Thank you very much for your help!
You've just helped me understand a little bit more on SQL srv.
[]
--
Bruno
May 24, 2005 at 12:51 pm
"You will want to use backup/restore for the system databases."
Is it that easy? In the past I had to set sql server to single-user mode to restore "master. Not hard but takes a bit of speciall preparation.
Also, what do you do if the paths on the source\destinaiton servers are diferent (e.g. C:\MSSQL\ VS D:\MSSQL)?
TIA,
Bill
May 24, 2005 at 1:49 pm
If I am going as far as moving SQL Server whole installation I would rename drives so the path will be the same.
I did had cases where I myself wanted to chage a drive letter. In this case I did detach from the source server, copy files over in the new directory on a destination server and re-attach by using any of 2 sp_attach_db or sp_attach_single_file_db
I would like the explanations from Frank or Tinker what happens if we attach user databases or copy user databases to the server with different SQL Server SP or Patch level. I know that system databases are modified by patching that is why they always recommend to backup your master and other system databases after patching. Whay about user databases?
Yelena
Regards,Yelena Varsha
May 24, 2005 at 2:15 pm
Yelena,
Be wary of changing drives paths, especially if system databases are involved!
Copying user databases to a new path on a new server is easy (assuming that no tables in the databases have hard coded references to the old paths!). But trying to restore the old master database to the new machine (for user permissions, etc.) can be problematic because the old "master" contains pointers to the orignal locations on the old server. While your user databases will restore OK the old master will prevent the sql install from starting.
I have had plenty of experience, mostly troublesome, when it comes to making Disaster Recovery copies of Sql on new machines. The hard part is getting the sql server to start, getting the permissions right, and making DTS packages work in the new environment.
Bill
May 24, 2005 at 2:26 pm
If I recall correctly I did have some good luick with Sql 2K's "Copy Database Wizard" (under EM) which does the attach\detach for you.
Bill
May 24, 2005 at 8:14 pm
Bill,
You are sure right about restoring Master. I learned my lesson with restoring Master way back with SQL 6.5. I am a good learner 🙂 When I say I change drives I did not mean I restore Master. I attached user databases and moved logins using one of my scripts that takes care of mapping database users.
About Copy Database Wizard: CTP 2005 is cool. Copy database Wizard 2005 contains 2 options: Detach/Attach and DTS that pumps data. Which is better then 2000 that says it uses DTS, but actually it is Detach/Attach DTS, so it implies that you have to login to both boxes as a Windows Domain Login that has admin rights to both servers. In addition it is sort of off-line thing because of detaching in 2000.
Yelena
Regards,Yelena Varsha
May 25, 2005 at 8:28 am
Important!
We were carried away by the concept of moving files and did not talk about several other important things.
Bruno, Are you changing the server name? Are you changing the IP address? Are you changing the domain name? Do your applications have files outside the database? Like if the path to the files stored in the database but actual files in the filesystem?Let us know, it is important. There is a whole lot that has to be done when renaming the server and changing its IP, starts with correcting the server name running sp_dropserver /sp_addserver if needed in some cases, other tips are in my post at:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=180370#bm180385
A lot should be done in the applications with the name/IP.Domain change,
Let us know
Yelena
Regards,Yelena Varsha
December 6, 2005 at 2:47 pm
I am trying to set up a copy of our production site.
In order to do so, I wanted to do so by restoring user db's from backups and copying system mdf and ldf files to the exact same locations on the new machine.
I tried to start this out following the steps
http://www.databasejournal.com/features/mssql/article.php/3379901
Basically:
1)stop the source SQL Server.
2)Make a copies of the master.mdf and mast.ldf files. Move copies to the new server.
3)Start the SQL Service on the old server.
4)Stop the SQL Service on the new server.
5) replace the master.mdf and mast.ldf with the copies.
6) Start the SQL Service.
This isn't working. I am unable to connect to the SQL Server (does not exist or access is denied.)
I think that this is because the windows account that the sql service ran on on the old machine is different than the windows account on the new machine. The old machine was on a Domain, the new machine is in a workgroup.
Any advice?
-jmr
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply