How can I copy a SQL Server 2008 instance to another server?
Kevin, the easiest way to bring across everything (if you are also keeping your instance name) is :-
- 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)
- Patch this server to the same level as your source
- Make a note of all the database paths on the source server (by issuing select name,physical_name from sys.master_files )
- Stop your source SQL Server instance
- Stop your destination SQL instance
- Copy all database files to your new server (retaining same paths)
- Start up your new instance (and old if you are retaining that).
- 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
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