September 20, 2004 at 9:43 am
What is the best plan or method to copy databases from one server to another server? We have SQL Server 2000 running on Windows 2000 Server. We have Databases, Stored Procedures, Views, DTS Packages, Jobs, Logins and Users that would need to be copied. I have put together the following plan but wonder is this the best possible way to accomplish this task.
1) DTS Transfer Logins Task
2) DTS Transfer DB Task (to copy DB and Server-wide Information) instead of Copy SQL Server Objects Task; or should I use the Copy Database Wizard which allows for Logins, Shared SP from the Master DB, Jobs from Msdb and User-defined error messages to be copied.
3) DTS Transfer Jobs Task.
4) Open/save the DTS Packages (to save to new server).
5) Check Server Options and DB Options.
6) Shutdown old server and rename the new server (My Computer, Network Identification)
7) Reset up Custom Operators and Alerts.
8) Reset up Accounts and support for mail.
9) Recreate DTS Packages' Jobs (Schedule DTS Package)
10) Reschedule Jobs (DTS Packages, Backup, Maintenance, etc.)
What type of problems may I expect to encounter and how should I resolve them. Am I going to experience problems with orphaned users? I am looking for a thorough plan that works and would minimize my problems and clean-up work after the transfer. In this situation, I think we are going to keep the same server names.
September 21, 2004 at 8:13 am
If you are retiring the old server and the new server doesn't have any other SQL server databases on it, you can go for the easier approach:
Make sure both servers have the same service packs installed
Stop the SQLserver services on the old server and the new server.
Copy all the databases and log files from the old server (data folder) to the same folders on the new server.
switch off the old server
rename the new server and change its IP address to be the same as the old server (some client connections might be using IP address instead of server name)
Reboot the new server - SQL server should start automatically
Refresh the DNS(s) to pick up the server on its new network card address (will happen eventually on its own if you wait long enough)
The new server should be up and running.
--IF-- the SQL servers were on different service packs apply the latest of the two service packs again to the new server, at this stage.
Don't forget - test, test and test again.
Peter
September 21, 2004 at 8:56 am
You could use Snapshot replication for the database if it is one time task.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply