June 18, 2003 at 5:23 pm
I would like to know if there are any problems copying all the DB files from one server to another.
1) Shutdown SQL2000 in server A.
2) Install SQL2000 in server B.
3) Copy all the files from A to B (same drivers).
4) Startup SQL2000 server B.
Someone told me that there is no problem.
Is that true?. I know about the detach and attach DBs, but we have to do it this way.
Please, I need help on this.
June 18, 2003 at 5:30 pm
Are you replacing server A or making a standby server?
June 18, 2003 at 5:42 pm
I am replacing the server.
June 18, 2003 at 5:47 pm
Also I red that I have to run the commands (in server B):
sp_dropserver 'SERVERA'
and then
sp_addserver 'SERVERB','local'
June 18, 2003 at 7:37 pm
is the new server going to acquire the properties (ip address, server name...) of the new server?
June 19, 2003 at 5:35 am
The copying DB's etc should all be fine, might not be as straight forward for your apps.
June 19, 2003 at 5:49 am
As far as I remember I have done it once copying master and all other database on other server (directory structure remains same) and it works.....Ur approach should work, appriciate if you let us know the results....
Cheers..
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
June 19, 2003 at 6:27 am
I just went through this and don't know if what I did was correct but it was the easiest way for me. Below is a simplified version.
1. Install SQL Server
2. Create the databases
3. Copied the info using DTS 'Copy SQL Server Objects'
4. Test Logins
5. Test Roles
6. Test Access
7. Re-copy using DTS prior to 'Go Live'
The only part that was a problem was the DTS packages themselves (I use quite a few). I had to export them individually and re-import the files in the new server.
Good Luck,
Joe Johnson
NETDIO,LLC.
June 19, 2003 at 7:38 am
Try this :
Install SQLServer B
launch Query Analyser on server A
execute the storedproc sp_detach_db <DB_NAME>
Copy your DB(s) to the new server (not the systems DB)
launch query analyser on server B
execute the storedproc sp_attach_db <dbname>, <complete_file_Path_and_name>
this do what your want
Remember, if your new server does not have the same name and IP address as your old server, you have to adapt your client applications
Claude
June 19, 2003 at 7:54 am
Neder: The serverB will have a different name and a different IP.
Johnsonj: Unfortunately I can not your method, becouse I will not have both machines at the same time.
For been more precise, my job consist in moving a disk array from serverA to serverB.
Someone, not familiar with SQLServer will take the backups (It's far from me). I will tell him to shutdown SQL2000 and backup everything.
Then the disks will be moved to serverB.
And then I will arrive to install SQL2000 in serverB an restore the backups. ServerA will be online (with less disks) and I hope serverB will be OK.
June 19, 2003 at 8:51 am
Depends on how did you setup your serverA and it may not work once the disk array has been moved to serverB.
I would suggest you doing following steps.
1. alwary make backup to all databases.
2. install SQL Server 2000 exactly same as your serverA.
3. rename serverA and shut it down.
4. move disk array from serverA to serverB.
5. start SQL Server in serverB.
6. if you are sucessful, rename serverB to serverA so you don't have to reconfigure client machines to connect to new server name.
7. run sp_dropserver and sp_addserver SPs to correct server name inside the SQL Server.
8. try to start SQL Server in old server. If you can't, you have to uninstall SQL Server and reintall it.
June 19, 2003 at 9:23 am
Maybe I did not explain my task very clear.
I have to move an SQL2000 from serverA
to serverB with differents IPs.
ServerA will not be a Database server anymore, but it will
be online. The disks of serverA will be installed on serverB
(in this disk reside the database).
1) Shutdown SQL2000 in server A.
2) Backup all database files.
3) Move disks from ServerA to serverB.
4) Install SQL2000 in server B.
5) Restore files from backup (same drivers).
6) Startup SQL2000 server B.
7) perform SP_dropserver and SP_addserver on serverB.
It seems to me that this is not certified by Microsoft, but
Should this work?.
Thanks a lot.
June 19, 2003 at 10:24 am
Since the database files are already on the disks being moved, all you need to do is to attach the database to the newly installed server. All server logins need to be either transfered from server A or re-created on server B. You may need to sync the logins with the 'sp_change_users_login' stored procedure. DNS alias needs to be created pointing to the new server so that the client apps will not need to be modified.
WM
June 19, 2003 at 11:35 am
I don't want to attach the files, just put them in the same driver than the SERVERA.
I'm planning backup and restore the system databases too (master, model and msdb).
Then it wouln't be necessary to perform sp_change_user_logins and all work fine?
My great doubt: if I copy all the database files, SQL2000 will function. Of course I will have to change clients to point to the new server.
June 19, 2003 at 11:37 am
Ive just done something similar....
Your basic approace should not be a problem. The simplest way restore the databases is:
1) Take a backup of user databases to the disks that will be shipped to you
2) On the new server, MSSQL2K is installed and operational
3) Run a simple restore command like:
RESTORE DATABASE IGAM FROM DISK = 'd:\backup\IGAM_db_200304240502.bak' WITH REPLACE
GO
RESTORE DATABASE IGAM1 FROM DISK = 'd:\backup\IGAM1_db_200304240502.bak' WITH REPLACE
GO
Where IGAM, IGAM 2,... are the names of the user databases and the path to the databases is... 'd:\backup\...
You dont need to create the databases before running a restore, that makes it very easy, its just a backup and restore. No need to be overly complicated...
"If it is not broken, dont fix it"
"To break a thing to find out how it is made is to leave the path of wisdom"
4) Test the new databases from the app (new ODBC connection?)
If your new install of MSSQL2K is healthy your databases will be operational.
You will also want to script EVERYTHING so you have a backup of EVERYTHING handy should something happen.
Also, test the restore before you have the disks shipped to make sure the backups are OK and have another copy of these backups somewhere safe.
cheers and good luck! (Let us know how it goes)
Isaiah
-Isaiah
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply