0

I have a SQL Server database that contains tables and other objects that I would like to script out on a regular basis. The idea is that I am going to create an autobuild that will create a container that is a scaled down version of this database (for testing).

I could easily just go script the database manually, but then I have to keep the resulting script up-to-date.

I am wondering if there a way to programmatically connect to a running SQL Server instance and (based off some input) generate scripts of specific tables, stored procedures, user defined types and data?

3
  • Autobuild a SQL Server database? This is well covered by using VS SQL database projects and deploying through .dacpac files Commented Dec 17, 2019 at 1:06
  • Perhaps read this sqlshack.com/… Commented Dec 17, 2019 at 1:16
  • With Sql Server, better to use the .bak file. You can restore from that on a different machine. Commented Dec 17, 2019 at 4:50

2 Answers 2

1

If you're coding with .NET other other languages that can call .NET objects, you can just use the scripting classes in SMO (SQL Management Objects).

There are worked examples in the SQL Server documentation here:

https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/scripting?view=sql-server-ver15

They are designed to do exactly what you're requesting.

Sign up to request clarification or add additional context in comments.

Comments

0

Well, I would think a backup would be the easiest way to go.

enter image description here

Or, setup several jobs, wrap everything in a SProc, and schedule that to run on whatever frequency you desire.

To create and attach a schedule to a job

    In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

    Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.

    Select the Schedules page, and then click New.

    In the Name box, type a name for the new schedule.

    Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.

    For Schedule Type, select one of the following:

        Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.

        Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.

        Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.

        Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.

enter image description here

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.