SQL Database/ Table Creation

Question

There is a database named DB_PROD_20 in our production server. In that DB, there is a table with the following directory:

ServerName.DB_PROD_20.Schema1.TableName

I need to extract that table with the schema and the data from the PROD server to DEV server. This is what I planned to do :

1. setting up a PS script to copy the backup file from Data warehouse
2. create a sql agent job to restore to DEV
3. create another job to execute a SP to clean up the data after the restore

question:

Should I create an entire new DB or create a another table under a different database name per our requirement in our DEV server?

Answer 1

Hi @SQLLover21 ,

I need to extract that table with the schema and the data from the PROD server to DEV server.

We can use the following two methods in SSIS package:

1.We can use OLEDB Source and OLEDB Destination in Data Flow Task .

2.We also can use Transfer SQL Server Objects Task in Control Flow .

Please refer to the following pictures:

Method 1:

Method 2:

Results:

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I would suggest to create a DDL script for that table if it does not exist in DEV and then to export data to the file. This way it is easier to import data to the table in DEV.