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.

What is the best way to create an exact copy of an entire SQL Server 2008 instance to another server? I want to copy everything: user accounts, logins, permissions, databases and all database objects, etc. I know how to copy things for each database in the instance, but I'd like to mirror the entire instance on another server. I'd also like to make this an automated process.

Unfortunately, this really bumps up against my SQL Server knowledge.

Thanks!

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

Kevin, the easiest way to bring across everything (if you are also keeping your instance name) is :-

  1. Install a new SQL instance on the new server, ensuring name and paths are identical and ensuring you install all the currently installed SQL components (such as reporting services etc)
  2. Patch this server to the same level as your source
  3. Make a note of all the database paths on the source server (by issuing select name,physical_name from sys.master_files )
  4. Stop your source SQL Server instance
  5. Stop your destination SQL instance
  6. Copy all database files to your new server (retaining same paths)
  7. Start up your new instance (and old if you are retaining that).
  8. Finally on your new instance, if you are not keeping the same machine host name then reflect this by changing the server\instance name via following this article http://msdn.microsoft.com/en-us/library/ms143799.aspx
share|improve this answer
    
Perfect. Thanks for the help! –  Kevin Babcock Nov 18 '10 at 16:07
    
What about instance Default settings as 'concat_null_yields_null', default index fill factor, 'scan for startup procs' and all these other small 'details' that can so drastically change the behaviour you are expecting from the new server? –  Hans Lindgren Oct 22 '12 at 11:08
add comment

For mirroring, see ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/8b37c0eb-8a70-45bd-9cc7-c54300711749.htm.

To get the logins that are on your source created on your target, run the sp_help_revlogin stored procedure: http://support.microsoft.com/kb/918992

share|improve this answer
    
Thanks for the tip on bringing the logins over...that turned out to be a huge help! –  Kevin Babcock Nov 18 '10 at 16:06
add comment

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.