I was wondering if anyone has a neat (preferably automated) method of creating small testing databases from large production instances.
My requirement would be to copy the schema and a subset of configuration data from a production database into a test database. The subset of data would be a full copy of a subset of tables, rather than a subset of data within one or more tables. There is a mixture of SQL2000 and SQL2005 servers involved in this requirement. I'm familar with the scripting mechanisms of Enterprise Manager and Management studio and DTS packages, sufficent to perform a process like this manually, but want to productionise and schedule this process to be performed automatically.
I'm sure this must be a commonly performed task, so I'm interested to know if anyone has a "best practice" for this requirement.
I've always used backup/restore for this. of course that doesn't give you a subset of anything.
for me the issue with taking a subset would be: if in test you don't have all the data that prod is working with, how do you really know your stuff will work in prod?
Thanks Jezemine, The problem with backup restore, is that my production database is 3Tb+. However nearly all of this is historic transactional data. A small subset (1 or 2 Gb) is the configuration data that dictates how any transactions I post into the database will process. So it is this data (which is as important as source code wrt to the behaviour of the system) that I want to replicate to any test environment.
This won't help much at this point, but I frequently set up file groups in Microsoft SQL databases for just this purpose. The filegroups can be backed up individually, so it is easy to "pick and choose" at the table level what data you want.
If you are doing this "after the fact" where you already have a 3Tb database, my first thought would be to script the schema of the whole tamale (all tables) and play that script into an empty database container. Then I'd pick one of (linked server, DTS, BCP, copy wizard) to move the data from the production database to the container with only the schema in it.