November 14, 2005 at 11:16 am
We administer a Data Warehouse environment. We have a physical database server 'DWPROD' with a SQL Server instance also called 'DWPROD' (SQL Server 200).
The Infrastructure group here want us to move SQL Server to a new physical server / instance of SQL Server called 'DWSQL'. They want the new physical server and instance of SQL Server to keep this name as they are in line with corporate standards for naming instances of SQL server and physical servers.
We know we have to make changes to the Data Warehouse application server to point to the new 'DWSQL' database server.
But, what steps should I take to move the SQL server from 'DWPROD' to 'DWSQL'.
I have read a number of options regarding moving / copying SQL server ... copy the data and transaction log files, restore DWPROD backups to DWSQL etc.
But, what added complications exists because I am adopting a new name for the physical server and instance of SQL Server on that server?
What is the best way for us to proceed?
November 15, 2005 at 8:12 am
1. get a list of all SQL Logins and passwords, NT users and groups
2. add the SQL Logins, NT users and groups to the new server
3. get a list of your physical file name and path for your database and logs
4. make a list of database and owner of the database
5. verify that the database owner has SA authority on both the old server and new server
6. login as the database owner on the old server
7. deattach the database
8. copy the physical database (.mdf) and log (.ldf) from the old server to the new server
9. reattach the database on the old server
10. login as the database owner on the new server
11. attach the database on the new server
12. determine if there are any orphan logins on the new server
run exec sp_change_users_login 'report' in query analyzer
13. correct any orphan logins
run exec sp_change_users_login 'update_one','xxxxxxxx','xxxxxxxx'
replace xxxxxxxx with orphan login from step #12
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply