Hi,
we would like our SQL Server 2012 (production server) clone, to have a test server. It must be transferred about 5 databases (Schema and Data) and server-/ Database credentials. Is there a guide to implement this (min. cost, fast realization )
Thank you
Regards
Nicole
either backup/restore or detach/copy/attach
for the logins: Copy logins
There are a host of potential server level objects that you'll want to consider migrating (Logins, linked servers, server roles and memberships, SQL jobs, Alerts, Email, etc.) The list goes on... Most of these can be scripted out via SSMS and re-created on your test server as is. A few you might need to know the actual passwords since the scripts generated won't produce clear text. An example of this would be linked server security. I'd suggest going through SSMS and exploring each node and decide which objects you care about and then script/migrate to the new server.
An alternative approach, and one I have not attempted, would be to restore the master and msdb databases from the production server on the test server. This gets a bit involved for the master database but it seems doable. Anyone have any experience with migrating the master database like this?
There are also a few server configuration specifics that reside in the registry. Items like the default location for database data files, database log files and backup files. Depending on how exact a match you want, you might want to take a look there, also.