Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

One of our customers has installed a new SQL server and needs to take down the old SQL box. Both servers are running Microsoft SQL Server 2008. The applications all access the current SQL server via hostname so the plan is to transfer everything then cut over the connections by updating DNS.

My question is: "What is the best way to actually move the databases and all associated accounts to the new server?"

Nothing about the current set up is documented. I need to figure out how to move over all the databases, stored procedures, SQL agent jobs, and SQL authentication accounts to the new server (note: the only account password I have is the sa password).

I am not a MSSQL guru so any help would be appreciated. Let me know if I need to clarify anything.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

I guess a Backup/Restore would be appropriate here.

Update:

share|improve this answer
    
The backup/restore feature would handle the databases themselves but not the security settings, accounts, agent jobs, etc... –  Chris Frazier May 23 '12 at 18:03
1  
You can transfer logins by using: support.microsoft.com/kb/918992/en-us Not sure about the security settings/agent jobs –  MichelZ May 23 '12 at 18:10
1  
The Database copy wizard seems to be able to copy all associated objects: sql-articles.com/articles/dba/how-to-use-copy-database-wizard –  MichelZ May 23 '12 at 18:13
    
+1 +1. Thanks guys. Don't know how I missed that KB article and the blog post is a nice find too. –  Chris Frazier May 23 '12 at 20:30
    
I've added those the answer. Glad I could help out. –  MichelZ May 24 '12 at 6:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.