How to create a copy of partial data of a SQL database?

Hi Experts,

We have a database of employees and many related tables (like EmployeesNotes, EmployeesSchedules etc..).

Now I'm looking to create a copy of that database with only part of the employees (that meet certain criteria).
For those employees I would need all related database tables.

What is the easiest way to accomplish that?

FYI- We have an Access FE application that I would like to setup as test linked to the copy of the database, so users can test if all data are included.
Would prefer something that would also be easy to fix/implement in case users find something missing, not to have to redo everything.
What would you suggest?

Thanks in advance!
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bfuchsAuthor Commented:
For the sake of easily setup a test scenario, I was thinking of creating a view based on Employees table with only employees needed.
First renaming the Employees table, and saving the view as the Employees table.
What are the issues you foresee with that approach?
PatHartmanCommented:
It is unlikely that you can make a single view to solve the problem.  Notes and Schedules may be related to Employees but they are not related to each other so any join that includes the three tables will produce a Cartesian Product essentially multiplying the data in notes times the data in Schedules.

To do this, you need to create a series of append queries.  You would create the tables in the second database.  Compact it and save it as a template.  Then when you want to copy data from the main database to the other database, you would copy the template and rename it.  As long as the name is the same as the name of the last version you linked to, the links don't need to be refreshed. If you constantly change the name of the small database, then you will need to use the linked tables manager to relink the tables every time you do this.

Then you need append queries and you need to run them in hierarchical order so that the data in parent tables gets added before the data in the child tables.  Include the autonumber in the select and append so that the records transfer to the template with their original PK intact.  That will enable you to also load the child tables without effort since their FK's will still work.  If the BE is Jet/ACE, this is very simple.  If the BE is SQL Server, it is a pain because you have to run additional queries in between to keep setting allow identity insert on and off between each "parent" table append query.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Wouldn't be more easy to have a full copy of the database and then delete all the records that you don't need?
This way you'll work with the same database object names. Only records are different.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bfuchsAuthor Commented:
Hi Experts,

@Vitor,
Wouldn't be more easy to have a full copy of the database and then delete all the records that you don't need?
Right, however the problem is that there are many tables involved and I need to run dozens of queries, and then later they realize something missing then I have to rewrite everything..

@Pat
will produce a Cartesian Product essentially multiplying the data...
I'm not I get what you're saying..
Basically I need a copy of the database with let say 10% of the employees and all related tables.
If I start by creating a view that selects those employees and name that view EmployeesTbl.
Then in access all forms based on EmployeesTbl table would contain the filtered data only.
Same are for all SQL views that are based on employees table, will they auto be looking for that view with that name, no?

FYI- This is an Access project linked to SQL BE.

Thanks,
Ben
Scott PletcherSenior DBACommented:
I'd create a "copy_process" table that contained the copy steps needed.  For many tables, the copy criteria is hopefully a simple comparison to a list and/or range of EmpNos to be copied.  For other tables to be copied, you may need some additional custom logic in the copy_process table.
 Then have a cursor read thru the table and process each copy.  

That keeps it simple to set up and to process.
PatHartmanCommented:
Assume your three tables are Students, Vehicles, Classes.  A student may have 0,1, or more vehicles and a student may have 1 or more classes.  A query that joins these three tables will produce a Cartesian Product because vehicles and classes have nothing to do with each other and just because they are both related to students, doesn't mean it makes sense to include them in the same query.  Here's what you get:
Students
Sam
Mary
Joe
Vehicles
Sam, bike
Mary, scooter, van
Joe, bike, car, scooter
Pets
Sam, Goldfish, Millie
Mary, Bird, Tweets
Mary, Dog, Spot
Joe, Snake, Slither
Joe, Dog, Bowser
Joe, Gerbil, Speedy

The join results in
Sam, bike, Goldfish, Millie
Mary, scooter, Bird, Tweets
Mary, scooter, Dog, Spot
Mary, van, Bird, Tweets
Mary, van, Dog, Spot
Joe, bike, Snake, Slither
Joe, bike, Dog, Bowser
Joe, bike, Gerbil, Speedy
Joe, car, Snake, Slither
Joe, car, Dog, Bowser
Joe, car, Gerbil, Speedy
Joe, scooter, Snake, Slither
Joe, scooter, Dog, Bowser
Joe, scooter, Gerbil, Speedy

Notice the apparent duplication. Sam has only one entry in each table so his result looks "normal".  Mary has two in each and so she ends up with 4 rows in the result set - 2 * 2.  Joe has three rows in each set and so ends up with 9 in the result set - 3 * 3.
bfuchsAuthor Commented:
Hi Scott,

Are you referring to copy process tables from one db to another, or copying data over from one table to another?

Can you give an example given the following 3 tables.

EmployeesTbl
ID (PK)
Firstname
LastName

ScheduleTbl
ID (PK)
EmployeeID
Day
FacilityID

NotesTbl
ID (PK)
EmployeeID
Notes
The employees criteria needed would be something like
where ID in (Select EmployeeID from EmployeesUnitsTbl where unit in ('a','b','c'))

Open in new window


Thanks,
Ben
Scott PletcherSenior DBACommented:
I will, but it will likely be tomorrow before I can post it.  I want it to be thorough and as "clean" as possible.
bfuchsAuthor Commented:
@Pat,

A query that joins these three tables will produce a Cartesian Product because vehicles and classes have nothing to do with each other..
I'm still not getting why are you stating that I will end up with a Cartesian product?
I'm not planning of removing any table, all I'm planning to do is replacing a table with a view.

Thanks,
Ben
Scott PletcherSenior DBACommented:
Here's what I have so far.  If it seems too complex, just let me know and I won't bother finishing it.

I try to make code as powerful and easy as possible.  Basically the "autoinclude" condition below would automatically add any table that had a column name of "EmployeeID" to the list of tables to be processed (copied).  That way you hopefully won't have to add too many table names by hand.  And even individual object_name(s) that you specify is(are) automatically matched as a LIKE pattern, so it's pretty easy to include a big list of tables easily ('Employee%' for example).

The function provides a "dependency level" for each table, based on FK definitions.  This makes sure that tables with FK requirement(s) are copied in a safe order.  Naturally I've included the function code as well.

/* Set up and load control tables. */

CREATE TABLE dbo.copy_process_header 
(
    process_id int IDENTITY(1, 1) NOT NULL
        CONSTRAINT copy_process_header__PK PRIMARY KEY,
    process_name varchar(100) NOT NULL
        CONSTRAINT copy_process_header__UQ UNIQUE,
    control_condition varchar(2000) NULL,
    destination_object varchar(500) NULL,
    last_run_datetime datetime NULL,
    num_of_objects_processed int NULL,
    error_message varchar(1000) NULL
)

CREATE TABLE dbo.copy_processes
(
    process_id int NOT NULL,
    step_number smallint NOT NULL
        CONSTRAINT copy_processes__DF_step_number DEFAULT 1,
    object_name_pattern varchar(100) NULL,
    select_clause varchar(8000) NULL,
    autoinclude_condition varchar(8000) NULL,
    CONSTRAINT copy_processes__FK_process_id FOREIGN KEY ( process_id )
        REFERENCES dbo.copy_process_header ( process_id ),
    CONSTRAINT copy_processes__PK PRIMARY KEY ( process_id, step_number )
)

INSERT INTO dbo.copy_process_header (process_name, control_condition, destination_object )
SELECT 'Copy_Selected_Emp#s_In_Table_To_New_Db', 'EXISTS(SELECT 1 FROM #EmpIDs WHERE EmpID = EmployeeID)',
    'some_other_db.dbo.$object$' /*$object$ means replace with same object_name as source data*/

INSERT INTO dbo.copy_processes ( process_id, step_number, object_name_pattern )
SELECT 1, 1, 'EmployeesTbl'
INSERT INTO dbo.copy_processes ( process_id, step_number, autoinclude_condition )
SELECT 1, 2, 'SELECT DISTINCT gt.dependency_level, t.object_name_in FROM sys.columns c INNER JOIN sys.tables t ON t.object_id = c.object_id LEFT OUTER JOIN dbo.Get_Tables_With_Dependency_Level() gt ON gt.object_id = t.object_id CROSS APPLY ( SELECT t.name AS object_name_in ) AS alias1 WHERE c.name = ''EmployeeID'' $add_to_where$ ORDER BY gt.dependency_level, object_name_in'

GO

/* Set controls for test run: this is the only part you hand-specify during actual processing. */

DECLARE @exec_sql bit
DECLARE @print_sql bit
DECLARE @process_key varchar(100) /*specify process_id *or* process_name*/

SET @process_key = 1
SET @exec_sql = 0
SET @print_sql = 1

----------------------------------------------------------------------------------------------------

/* Main code that does the processing (table copy'ing).  Prob make into a proc, with params of the 3 vars above. */

IF OBJECT_ID('tempdb.dbo.#EmpIDs') IS NOT NULL
    DROP TABLE #EmpIDs
CREATE TABLE #EmpIDs (
    EmpID int PRIMARY KEY
    )
IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL
    DROP TABLE #objects
CREATE TABLE #objects (
    ident int IDENTITY(1, 1) NOT NULL,
    dependency_level tinyint NOT NULL,
    object_name varchar(100) PRIMARY KEY
    )

DECLARE @autoinclude_condition varchar(2000)
DECLARE @control_condition varchar(1000)
DECLARE @object_name_pattern varchar(100) 
DECLARE @process_id int
DECLARE @process_key_contains_id bit
DECLARE @process_name varchar(100)
DECLARE @select_clause varchar(8000)
DECLARE @sql varchar(8000)
DECLARE @step_number smallint

IF @process_key LIKE '%[^0-9]%'
    SET @process_key_contains_id = 0
ELSE
    SET @process_key_contains_id = 1

SELECT @control_condition = cph.control_condition,
    @process_id = cph.process_id,
    @process_name = cph.process_name
FROM dbo.copy_process_header cph
WHERE ((@process_key_contains_id = 0 AND process_name = @process_key) OR
       (@process_key_contains_id = 1 AND process_id = @process_key))

DECLARE cursor_copy_processes CURSOR LOCAL FAST_FORWARD FOR
SELECT 
    cp.step_number, cp.object_name_pattern, cp.select_clause, cp.autoinclude_condition
FROM dbo.copy_processes cp
WHERE cp.process_id = @process_id
ORDER BY cp.step_number

OPEN cursor_copy_processes
/* Get and store all object_names that need to be processed. */
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM cursor_copy_processes INTO @step_number, @object_name_pattern, 
        @select_clause, @autoinclude_condition
    IF @@FETCH_STATUS <> 0
        IF @@FETCH_STATUS = -1
            BREAK
        ELSE
            CONTINUE;
    IF @autoinclude_condition > ''
    BEGIN
        SET @sql = 'INSERT INTO #objects ' + REPLACE(@autoinclude_condition, 
            '$add_to_where$', 'AND NOT EXISTS(SELECT 1 FROM #objects o WHERE o.object_name = object_name_in)')
        IF @print_sql = 1
            PRINT @sql
        EXEC(@sql)
    END /*IF*/
    ELSE
    BEGIN
        INSERT INTO #objects ( dependency_level, object_name )
        SELECT DISTINCT gt.dependency_level, t.name AS object_name
        FROM sys.tables t
        LEFT OUTER JOIN dbo.Get_Tables_With_Dependency_Level() gt ON gt.object_id = t.object_id
        WHERE t.name LIKE @object_name_pattern AND
            NOT EXISTS(SELECT 1 FROM #objects o WHERE o.object_name = t.name)
        ORDER BY gt.dependency_level, t.name
    END /*ELSE*/
END /*WHILE*/
DEALLOCATE cursor_copy_processes

/* Add code here to cursor thru the #objects table and copy each object_name to the destination. */

Open in new window


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Get_Tables_With_Dependency_Level] ()
RETURNS TABLE
AS
/* SELECT * FROM dbo.Get_Tables_With_Dependency_Level() */
RETURN (
    WITH cte_tables AS
    (
        SELECT
            OBJECT_SCHEMA_NAME(t.object_id) AS schema_name,
            OBJECT_NAME(t.object_id) AS table_name,
            t.object_id AS object_id,
            1 AS dependency_level
        FROM
            sys.tables AS t
        WHERE
            t.is_ms_shipped = 0
        UNION ALL
        SELECT
            OBJECT_SCHEMA_NAME(t.object_id),
            OBJECT_NAME(t.object_id),
            t.object_id,
            ct.dependency_level + 1
        FROM
            sys.tables AS t
        INNER JOIN sys.foreign_keys AS f ON 
            f.parent_object_id = t.object_id AND
            f.parent_object_id != f.referenced_object_id
        INNER JOIN cte_tables AS ct ON 
            f.referenced_object_id = ct.object_id
        WHERE
            t.is_ms_shipped = 0
    )
    SELECT dependency_level, schema_name, table_name, object_id
    FROM (
        SELECT 
            ct.dependency_level,
            ct.schema_name,
            ct.table_name,
            ct.object_id
            , ROW_NUMBER() OVER(PARTITION BY ct.schema_name, ct.table_name ORDER BY ct.dependency_level DESC) AS row_num
        FROM
            cte_tables AS ct
    ) AS derived
    WHERE
        row_num = 1
)
GO

Open in new window

PatHartmanCommented:
I'm still not getting why are you stating that I will end up with a Cartesian product?
I explained why ( two of the tables have no relationship to each other) AND I provided an example that shows what happens.  Try to substitute schedule for class and notes for vehicle and see if that makes sense.
bfuchsAuthor Commented:
@Scott,
Wow I didnt think this would go so in depth as to create all tables by code, thats really amazing.
Just wonder where do I run these functions/scritp?
In a new database and that would be looking up in the target database?
If it seems too complex, just let me know and I won't bother finishing it.
Well to be honest, I'm not so familiar with t-sql language.
Therefore if this is to be used as copy code and run then I will go with that, however if this will require to do modifications to the script that would probably be difficult, especially taking in consideration the short time frame I have to accomplish this task.

@Pat,
( two of the tables have no relationship to each other) AND I provided an example that shows what happens.
So in that case how do they currently work?!
I'm not trying to fix/modify the existing database whatsoever, the purpose of all this is to export a portion of data to a blank database and from there we will send it to a software company to load our data into their DB so we get to test their software..

Thanks,
Ben
Scott PletcherSenior DBACommented:
@Ben

While it's not clear above, the complexity is all "hidden" in code that's created only once, and it's just to give it max flexibility to adjust later.

When you actually used it, all you'd need to specify is that you want process #1 to run, and whether you want to print the sql generated and/or exec the sql generated.

/* Set controls for test run: this is the only part you hand-specify during actual processing. */

DECLARE @exec_sql bit
DECLARE @print_sql bit
DECLARE @process_key varchar(100) /*specify process_id *or* process_name*/

SET @process_key = 1
SET @exec_sql = 0
SET @print_sql = 1
PatHartmanCommented:
So in that case how do they currently work?!
The two tables are related to the parent table, just not to each other.  The point is you can make a query of employee and schedule or of employee and notes but you cannot do all three at once.

If a query of the three tables seems to be working, then you have only one instance of note or one instance of schedule for each employee in your data.  Find some employee that has more than one of each and look at the result.

Scott is doing a lot of work for you and i'm not sure that is what you need.  You are using SQL Server.  Are you REALLY going to create multiple copies of tables for people?

You mentioned that you want to create a view.  That is fine depending on what you are using to limit the data.  If you can limit the employees to be selected great.  You would then use the view instead of the employee table in all the joins.  But why would you even do that?  Why not simply use criteria to limit the data directly.  Do you really want to modify all the queries to create duplicates?
bfuchsAuthor Commented:
@Scott,
As mentioned I'm not sure where to run the code supplied.
To be safe I created a new DB and tried running from there.
See attached what I got.

While I assume this must be done in a copy of the production DB containing all tables (otherwise how will it get all tables), perhaps this is why I'm receiving this error..
However wondering what will this do, if tables already exists, how will I end up with let say EmployeesTbl containing only desired records?

@Pat,
Are you REALLY going to create multiple copies of tables for people?
No, the goal is to have one copy of the entire DB with only limited records, depended on employees as mentioned.

Thanks,
Ben
Untitled.png
bfuchsAuthor Commented:
Hi Experts,

Just updating..
I went ahead with my original plan regarding setting of a test BE & FE app.
Did the renaming approach, started by renaming Employeestbl to Employeestbl_All and then created a niew named Employeestbl as follows
Select distinct ... all my columns.. FROM         dbo.Employeestbl_All LEFT OUTER JOIN
                      dbo.EmployeesUnitstbl ON dbo.Employeestbl_All.ID = dbo.EmployeesUnitstbl.EmployeesID
WHERE     (dbo.Employeestbl_All.Title = 'MyTitle') OR
                      (dbo.EmployeesUnitstbl.Unit = 'MyUnit'') AND (ISNULL(dbo.EmployeesUnitstbl.Experience, 0) = 0)

Open in new window

So far the FE application looks fine to me, will have users test it by tom.

Now the main question remains, how to have this data exported into a new DB.
Possible Scott's solution is an answer to this, however still need explanation how this works & how to apply it.

Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,
Here is another question related to this job..
https://www.experts-exchange.com/questions/29094246/Queering-sys-tables.html#questionAdd
Thanks,
Ben
bfuchsAuthor Commented:
Further thinking, once I got an answer on the above question.
So I have a list of all tables containing column EmployeeID.
Now I would do the following
1- save all Needed employee ID's in a table, lets call it TempTbl
2- Save the list of tables in a table
3- write code to go in a loop for list of tables and do something like following (and code can be in VBA where I am more familiar with..)
Delete from & VarTable &  where EmployeeID not in (Select EmployeeID from TempTbl)

Open in new window


What do you say for this?

Thanks,
Ben
ZberteocCommented:
Always start with the parent tables and then go down the hierarchy. So, lets say that the Employees table with a PK as EmployeeID  is at the top level. First step is to isolate the EmployeeID that are part of the criteria. I would create a table with one column at least:

EmployeeSetFilter(EmployeeID in)

You will insert into this table all the EmployeeID values that you need:

insert into EmployeeSetFilter(EmployeeID) select EmployeeID from Employees where <condition_here>

Then you go starting with the Employee table at the top and all the related tale that have EmployeeID as FK and you select them like:

Select * from Employees where EmployeeID in (select EmployeeID  from EmployeeSetFilter)
Select * from EmployeesNotes where EmployeeID in (select EmployeeID  from EmployeeSetFilter)
Select * from EmployeesSchedules where EmployeeID in (select EmployeeID  from EmployeeSetFilter)

If you have tables at deeper level, that are referencing for instance EmployeesSchedules, Lets say EmployeeTask, then you do it using 2 sbubqueries:

select * from EmployeeTask where  EmployeesSchedulesID in (Select EmployeesSchedulesID from EmployeesSchedules where EmployeeID in (select EmployeeID  from EmployeeSetFilter))

And so forth. For any given query the last factor to filter down the rows will actually be select EmployeeID  from EmployeeSetFilter.

In the second/filtered database you will create all the tables that you need from the master database and then you will use the queries above to insert the rows you need. I will suggest to leave the FKs aside in this second database because the relations are anyway ensured by the master database and the actual queries you use for inserts. If you keep the FKs, you will have to make sure that you start the inserts with the very top level tables, parents, and then the next ones in the hierarchy and finish with the ones at the bottom. If you want to make this a process that you want to run periodically you will have ti truncate the target tables in the filtered database before you insert in the.
Mark WillsTopic AdvisorCommented:
I would like to take a big step back and understand what you are trying to achieve....

I understand you want to create a limited selection / subset of Employees (and related tables, or, pointing to related data)

For what purpose ?  For example:
a) Setting up a test / QA environment ?
b) Creating a secure environment to restrict / control access to nominated users?
c) are transactions / changes to be reflected in your 'full' database ?

Do you have referential integrity / constraints in your 'full' database ?

If you could please explain your goals / reasons, it might help us come up with a viable approach.
bfuchsAuthor Commented:
Hi Experts,
@Mark,
a) Setting up a test / QA environment ?
Yes, As mentioned above, we are about to purchase a new software and want to start testing it with part of our data, meaning a small group of users will move over and start using this new application, and this is why we want the entire database but limited data only.
Do you have referential integrity / constraints in your 'full' database ?
This maight be an issue here, since some tables are missing the referential integrity.
c) are transactions / changes to be reflected in your 'full' database ?
No.

Thanks,
Ben
bfuchsAuthor Commented:
@Zberteoc,
The problem I have with your approach is that will have to look into each table separately.
And not sure why are you suggesting inserts rather then copy the entire database and then delete the unnecessary stuff?
FYI- The copy of DB will not be used for edits/inserts at all.
It will be used for
1- users reviewing if all data its here.
2- new software corp will take data from there to load into their system.
As mentioned I think it will not be a one time thing to move data, as we work they will realize more stuff are missing, so defining rules what data to include will be changed..
And this is why I'm not looking to manually update all, and have to keep repeating after each change of rule..
Thanks,
Ben
Mark WillsTopic AdvisorCommented:
Here is a step by step guide how to clone a DB for test. It uses SSMS and probably easier to walk through being new to SQL Server.

Have a read of : https://blogs.technet.microsoft.com/sql_server_isv/2011/04/22/fundamentals-cloning-a-production-database-to-a-test-server/

Then you can happily delete the employees you DONT want.

Not having referential integrity will mean you probably have a lot of orphan rows around, but shouldnt be picked up if your queries always check for employee.

You can set about cleaning those orhans up over time, or, by checking agianst the employee table.

If you do cleanup the orphans, you will want to check your DB after the event. Probably worthwhile changing the recovery mode to "SIMPLE" for a test environment.

If by chance you are running SQL 2014 or more recent. There is a new DBCC command to CLONEDATABASE

See : https://www.mssqltips.com/sqlservertip/4410/clone-a-sql-server-database-using-dbcc-clonedatabase/
bfuchsAuthor Commented:
@Mark,
I know how to do a backup and restore in SSMS.
The help I need with is how to create a process to remove all data not belonging to those employees selected.
Re the orphan records I'm not concerned at this point, as they would also be deleted by this process I guess.
Thanks,
Ben
PatHartmanCommented:
Bfuchs, It's been eight days since you started this thread.  I told you the basic procedure on day one (You have to create a bunch of append queries to get the data you want from one table and append it to the same table in the new database) and nothing new has happened.  Exactly how many tables are involved?  How often do you have to refresh the data?  Do you have RI enforced?  What about cascade delete?  I've done this a number of times to create test databases.  Is that what you are trying to do?  Will you ever have to re import the copied data?  There is no quick and dirty way to do this.  It is unique to each application.
ZberteocCommented:
@bfuchs

It is up to you if you move what you are interested in or if you remove what you don't need anymore! The approach is similar. If you want to remove than you will have to either copy or restore the database into a new one and then remove what you don't need. The difference will be that in the EmployeeSetFilter table you will now insert what you DON'T need. However in the question you say that you want to create a subset of data based on a filter you use, which points toward my fist suggestions. In the case of removing the queries will be with DELETE instead of SELECT * and starting with the deepest level of tables if you keep the FKs, basically the opposite order:

delete from EmployeeTask where  EmployeesSchedulesID in (Select EmployeesSchedulesID from EmployeesSchedules where EmployeeID in (select EmployeeID  from EmployeeSetFilter))
...
delete from EmployeesSchedules where EmployeeID in (select EmployeeID  from EmployeeSetFilter)
delete from EmployeesNotes where EmployeeID in (select EmployeeID  from EmployeeSetFilter)
delete from Employees where EmployeeID in (select EmployeeID  from EmployeeSetFilter)


Here is how to copy a database:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/use-the-copy-database-wizard?view=sql-server-2017

Here is how to retore a database:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-backup-from-a-device-sql-server?view=sql-server-2017#SSMSProcedure
bfuchsAuthor Commented:
Hi Experts,

@Pat, @Zberteoc
There is no quick and dirty way to do this.

I see you both agree on the same approach, meaning I should write separate delete statements for each table.
So if I take that route
and starting with the deepest level of tables if you keep the FKs, basically the opposite order:
Since I only need thid DB for exporting data, there is no need to keep any FK relationships.
So I'm planning of deleting all of them, this way I dont need to worry for order of tables being deleted.
Is there an easy way to delete all database tables relationships?

Thanks,
Ben
PatHartmanCommented:
I don't think you should delete anything.  I think you should use append queries.  

If this is something you need to repeat, create a procedure that truncates the "test" tables.  Then run the append queries with the specified selection criteria.  Truncate is more efficient than delete.

If the data in the test database will be updated, you should KEEP the RI.  All you need to do is to put a little thought into the order in which you run your append queries.

I have a procedure like this that I use in a production database.  There is a set of test data embedded in the production data.  The client can update this audit (the app audits eligibility for medical insurance plans) to train people and learn how the app works.  They can revert to the original state of the audit by pressing a revert button.  That code deletes the training audit and then reloads it from a set of tables that contain the original values.
ZberteocCommented:
To get the DROP and CREATE statements/scripts for all the relational constraints run this script in ypur new/target database, make sure that is NOT in the original database), then copy the content of DropFKScripty column, paste it in a new query window and then execute it:
WITH RefColumns AS
(
       SELECT
              C.referenced_object_id AS [object_id],
              C.parent_object_id,
              STUFF((SELECT ', ' + QUOTENAME(B.name)
                     FROM sys.foreign_key_columns A 
                           JOIN sys.columns B ON B.[object_id] = A.referenced_object_id AND B.column_id = A.referenced_column_id
                           WHERE C.parent_object_id = A.parent_object_id AND C.referenced_object_id = A.referenced_object_id
                           FOR XML PATH('')), 1, 2, '') AS ColumnNames
       FROM sys.foreign_key_columns C
       GROUP BY C.referenced_object_id, C.parent_object_id
)
,ParentColumns AS
(
       SELECT
              C.parent_object_id AS [object_id],
              C.referenced_object_id,
              STUFF((SELECT ', ' + QUOTENAME(B.name)
                     FROM sys.foreign_key_columns A 
                           JOIN sys.columns B ON B.[object_id] = A.parent_object_id AND B.column_id = A.parent_column_id
                           WHERE C.parent_object_id = A.parent_object_id AND C.referenced_object_id = A.referenced_object_id
                           FOR XML PATH('')), 1, 2, '') AS ColumnNames
       FROM sys.foreign_key_columns C
       GROUP BY C.parent_object_id, C.referenced_object_id
)
 
SELECT
       'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' DROP  CONSTRAINT' + ' ' + QUOTENAME(FK.name) AS [DropFKScript],
       'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' WITH CHECK ADD  CONSTRAINT '+ QUOTENAME(FK.name) + CHAR(13) + CHAR(10) +
       'FOREIGN KEY(' + PC.ColumnNames + ')' + CHAR(13) + CHAR(10) +
       'REFERENCES ' + QUOTENAME(SCHEMA_NAME(RT.[schema_id])) + '.' + QUOTENAME(RT.name) + ' (' + RC.ColumnNames + ')' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +
       'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' CHECK CONSTRAINT ' + QUOTENAME(FK.name) + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
       AS [CreateFKScript]
FROM 
	sys.foreign_keys FK   
	INNER JOIN sys.tables PT 
		ON PT.[object_id] = FK.parent_object_id
	INNER JOIN ParentColumns AS PC 
		ON	PC.[object_id] = FK.parent_object_id 
		AND PC.referenced_object_id = FK.referenced_object_id
	INNER JOIN sys.tables RT 
		ON RT.[object_id] = FK.referenced_object_id
	INNER JOIN RefColumns AS RC 
		ON	RC.[object_id] = FK.referenced_object_id 
		AND RC.parent_object_id = FK.parent_object_id
WHERE 
	PT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
	AND RT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
ORDER BY PT.name
GO

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
If you use Truncate and Insert, you can set up the RI ONCE and not worry about it.
bfuchsAuthor Commented:
Hi Experts,
I want thank you all for participating in this thread.
Sorry for keeping this open that long..
Ben
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.