How to copy an entire SQL Server 2008 database using the WHERE clause to restrict the copied data
To provide a more realistic experience during development and testing, we want to automate the process of copying our SQL Server 2008 databases from production to development workstations. Since these databases range in size from a few GB to 1-2 TB, this will take all the time and is not suitable for some machines (I'm talking to you SSD). I want to be able to click a button or run a script that can clone the database structure and data - except , can provide suggestions WHERE
while copying the data to reduce the size of the database.
I found a few partial solutions, but nothing can copy the schema objects and custom bounded data without requiring a lot of manual labor to ensure that the objects / data are copied in the correct order to satisfy dependencies, FK constraints, etc. I fully expect to write a sentence WHERE
for each table by hand, but I hope the rest can be automated so we can use it easily, quickly and often. Bonus points if they automatically collect new database objects as they are added.
Any help is greatly appreciated.
source to share
Replication of snapshots with conditions for tables. This way you get your schema and data to be replicated when needed.
This article describes how to create merge replication, but when you choose snapshot replication, the steps are the same. And the most interesting part is Step 8: Filter Table Rows. of course, because doing so can filter out all unnecessary data for replication. But this step needs to be done for each object, and if you have hundreds of them, then you better analyze how to do it programmatically, rather than going through the wizard windows.
source to share