Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
 Posted - 2007-12-26 : 17:56:04
|
| Hi Experts, I need an advice on this documentation. What are the things i missed. Can you please help me.1. Make a full backup of all databases from server 1 (for our safety)2. Copy all logins , Jobs , DTS packages, folders from server 1 to server 23. Copy any folders or files from the server 1 to the new server 2 that will be accessed by Jobs, DTS packages, stored procedures etc.4. Change all databases to Single user mode so no one can access the server( Downtime)5. Detach all databases from server1 ( There will be downtime)6. Stop the SQL Server, SQL Agent Services on server 17. Attach all databases to server 2 since both servers are in the same Datacenter.8. Connect to the server 2, using Enterprise Manager and make sure all the databases are displayed and that there is no suspect databases. Scan through the SQL Server error logs, SQL Agent error logs, and System and Application event logs, to make sure there are no errors. If we find any errors, fix them before you proceed any further.9. Once we are happy with the new server’s server 2 state, shutdown the SQL Server, SQL Agent and MS Search services.10. Rename the alias on server 111. Rename the alias on server 2 so all the applications will point towards it and reboot the server. 12. Start the SQL Server, SQL Agent and MS Search services on server 2. 13. After we have completed these steps, server 2 is ready to go. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-26 : 22:26:56
|
| Set db to single user mode will not prevent user connection, I'll set to read only so no one can make change to the db.I prefer backup/restore over detach/attach, less risk. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-26 : 23:16:45
|
| The reason i am using Attach\Detach is Both the servers are in Same SAN (i.e one server can access other servers data files and log files) |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-27 : 22:37:10
|
| Dosen't matter. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-27 : 23:06:18
|
| If both servers have the exact same paths, then all you have to do is:1. Stop the SQL Server service on Server1 and Server22. Copy all database files from Server1 onto Server2, place them in the exact same location as where they were on Server13. Start the SQL Server service on Server2You don't have to bother with DTS packages, job, logins, etc... as you've copied over the system databases as well.This is why I make sure that the paths will be the same between servers, it makes it so easy to quickly bring up a new server.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-28 : 03:05:28
|
quote:
Originally posted by sodeep
The reason i am using Attach\Detach is Both the servers are in Same SAN (i.e one server can access other servers data files and log files)
detach/attach has some risk to it. You might be able to successfully detach a database and then not be able to re-attach. Going with backup/restore is much safer.If you don't care about doing things a safe way, then by all means use your detach/attach method. -ec |
|
|
|
|
|
|
|
|
How is this Documentations for moving databases.