scripttables.sql<\/code> script that was generated, you will see something like the following, based on the database you have chosen:<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/02\/word-image-101539-2.png\")
<\/p>\n
Use this file to create the objects in your target database, typically using SSMS.<\/p>\n
As mentioned, you may need to manually create schemas, user defined types, and perhaps any scalar functions used in constraints.<\/p>\n
Step 2 \u2013 Export the Data From Tables.<\/strong><\/p>\nOpen the BCPOutSMO.ps1<\/code> script. <\/em>Change the $sourcedatabasename<\/code>, $sourcesevername,$basexport<\/code>. To match your environment. Execute the script and you should see a set of .txt files created in the $baseexport<\/code> folder. One for each table to be exported:<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/02\/word-image-101539-3.png\")
<\/p>\n
Step 3 \u2013 Load the data into the new database<\/strong><\/p>\nOpen the BCPinSMO.ps1<\/code> script and change the header variables to meet your environment. <\/em>The variables to change to your environment are $newdatabasename,$newservername,$baseexportpath<\/code>.<\/p>\nSet the newservername<\/code> and newdatabasename<\/code> to the target of your data. Point the $baseexporpath<\/code> to the folder where the .txt<\/code> files are from step 2. Then just run the script and the tables will be filled.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/02\/word-image-101539-4.png\")
<\/p>\n
Conclusion<\/h2>\n
As we can see, it is fairly simple to set up this set of code that will save a huge amount of time if you have to do this process more than a few times. If this is done frequently, with a bit more work the entire process could be built into a solution that calls the code in each file with just a little bit of work.<\/p>\n
Any time you find yourself doing repetitive management tasks, consider if PowerShell can help you achieve what you need, if only the parts are especially cumbersome like importing and exporting data from a database.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
I was talking with a good friend that has an environment with terabytes of information and to create a homolog or dev environment to him is a pain. He comes to me with a solution : A PowerShell script that gets only the first 1000 lines of each table, creates the inserts and schema files……<\/p>\n","protected":false},"author":221715,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,35],"tags":[4635,4151],"coauthors":[6819],"class_list":["post-101539","post","type-post","status-publish","format-standard","hentry","category-featured","category-powershell","tag-powershell","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101539","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221715"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=101539"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101539\/revisions"}],"predecessor-version":[{"id":101546,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101539\/revisions\/101546"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=101539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=101539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=101539"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=101539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/02\/word-image-101539-1.png\")
<\/p>\n