SQL Server 2008 – How to Copy Instance to Another Server

sql serversql-server-2008

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!

Best Answer

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
Related Topic