SQL Server Performance Forum – Threads Archive

SQL server migration

We are testing migration procedure from our old SQL server to a new one. For this test we copied backups of all user databases + master and msdb over to a new Win 2000 server. The new SQL server has all the same settings etc. but is in a different domain and has a different name than the old one. The sa login and password for the new SQL server are different from the old one’s.
On the old server all databases were on S drive, but on the new one they should all be on C.
When we try to restore the master database (specifying move to appropriate C: folder), it sends errors to eventviewer saying it was trying to open user databases from the S drive. Also upon successful completion of restoring when it tries to close the SQL server it says the connection could not be established. We really would like to get master to restore successfully on the new SQL server, is there a way to get it to work? If not do you have any suggestion of how else can we guarantee that all info from the master database gets onto the new SQL server?
We are planning on implementing merge replication after we successfully restore all database on the new SQL server…would replication take care of updating master to have all the info from the old SQL server?
Restoring user databases did not present any problems and neither did msdb btw. Thank you very much,
-olga

---

Hello, It’s very simple to migrate from old to new one. 1. Back up with all of the datafiles on the user database. 2. Copy the backup files to a location on the new SQL Server system properly. 3. Create empty user database on the new server. 4. Restore w/ datafiles and log files to the new database. Restoring w/ mdf or ndf you need to configure w/ "norecovery mode". After that, apply w/ transaction log files w/ recovery mode. Example: RESTORE DATABASE APP file = ‘appdata1’ from disk = ‘d:app_appdata1.bak’
with NOUNLOAD, STATS = 10, NORECOVERY, move ‘appdata1’ to ‘d:app_appdata1.mdf’,
move ‘applog1’ to ‘d:app_applog1.mdf’ — To read data file header info.
Restore filelistonly from disk = ‘d:app_appdata1.bak’
Restore headeronly from disk = ‘d:app_appdata1.bak’
Restore labelonly from disk = ‘d:app_appdata1.bak’
Thanks. Ted Kim
Sr. System Engineer
CJ Systems

---

See the articlehttp://vyaskn.tripod.com/moving_sql_server.htm Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

]]>