6

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!

2 Answers 2

7

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
1
  • 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? Oct 22, 2012 at 11:08
1

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

1
  • Thanks for the tip on bringing the logins over...that turned out to be a huge help! Nov 18, 2010 at 16:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .