0

I can manually generate a schema with data via Table Name > Tasks > Generate Scripts...

But it is possible to create a schema+data single sql file backup of a database and run it on another database programmatically ?

Maybe some SQL Script which will be scheduled by Job or by another approach, i dont know.

Thank you

Improve this question
2
  • if its about whole database why not backup and restore?
    – AA.SC
    Dec 4, 2014 at 10:03
  • Because of company rules, compliance and permissions and we dont need backup all database objects but only tables with data :-)
    – Muflix
    Dec 4, 2014 at 11:34

1 Answer 1

Reset to default
2

It is possible to generate DDL and Data from SQL Server Management Studio (SSMS):

Object Explorer > yourServer > yourDatabase > right-click > Tasks > Generate Scripts ...

enter image description here

Within the 'Advanced' options change 'Types of data to script' to 'Schema and Data.

Also, here is a simple example of using Powershell to script data for tables I did a few years ago:

Example: Scripting Data with SMO and Powershell

So it's possible, and you will find many Powershell examples for scripting tables. However backup and restore would be much more straightforward! If the tables are empty, then it's inconsequential if you move them over right?

Improve this answer
2
  • Thank you that looks good, i will try it. The tables will never be empty, there will be some test data and we will throw them away and get fresh production data on demand (running script) or automatically by schedule :-)
    – Muflix
    Dec 4, 2014 at 13:06
  • I just saved your script and it copy only one choosed table and not all tables but i think its really good starting script :-)
    – Muflix
    Dec 4, 2014 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.