question

SQLLover21-0870 avatar image
0 Votes"
SQLLover21-0870 asked Monalv-msft commented

SQL Database/ Table Creation

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?

sql-server-generalsql-server-transact-sqlsql-server-integration-servicessql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft commented

Hi @SQLLover21-0870 ,

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:
44388-oledbsou.png
44527-oledbdes.png
44528-mappingdes.png
44593-df.png

Method 2:
44583-tssot1.png
44563-tssot2.png

Results:
44460-sd2.png

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.



oledbsou.png (18.9 KiB)
oledbdes.png (25.6 KiB)
mappingdes.png (28.5 KiB)
df.png (68.7 KiB)
tssot1.png (14.0 KiB)
tssot2.png (42.9 KiB)
sd2.png (36.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLLover21-0870 ,

May I know if you have anything to update?

Best Regards,
Mona

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.