Is it possible to create/update a dev version of production SQL server using disk move?

Question

I have production sql server with 140 databases.

Question 1

I want to create a development environment so here is what I'm thinking:

1. Install SQL server on dev environment with same drive names as the data and log file paths as production (note: not the same physical disks, I mean just the drive names)

2. Clone the data and log disks from production

3. Take off the data and log drives from the dev server, and attach the production disk clones to the dev sql server.

Effectively since all configs are in the system dbs this should work. Is SQL server able to handle such a situation? I cannot find any msdn docs regarding this approach.

Question 2

Similarly to update this dev env every month with data from prod, normally I would have to individually restore the 140 dbs. Instead, what is the implication of the following:

Create clone of the 2 production disks [data and log file disk] (so this will be an exact copy of the data and log file drive from production), shutdown the dev SQL server machine, disconnect the data and log disks, attach the clone disks. Start the dev SQL server machine.

How does SQL server handle this situation?

Answer 1

This works, but you’ll need to be careful of a few things.

• Perhaps most importantly, you have production data in a development environment. If someone is trying to get access to confidential data, you’ve made it a whole lot easier. You might want a staging server as a go between to obfuscate the data before it gets cloned to development.
• If you are cloning disks from your live system, you’ll need to make sure the disks are consistent with each other as of the points you take the clones. In practise, this is usually fine as SQL Server is able to recover (it’s the same sort of thing that can happen if your system crashes).
• If you’re not using container databases then your logins are stored in master, this cannot be copied so easily. You will want some process which ensures your database users and your instance logins get reconnected. I set the SID of my SQL logins when I create them using the same SID for all environments. That way the link between DB user and instance login remains.
• AD users/groups might be different between environments, if you have logins that use these and they need to differ between environments then you’ll need to do something to link them back up. You can’t use the SID trick because these logins will get a SID from AD.
• Likewise, agent jobs, linked servers, system roles etc won’t be refreshed. If you test on development before you roll to production then this is fine. If you have many environments then you must make a point of keeping these things up to date.