Command to script database structure and data

Question

Every time I want to create a script of the database structure (and data) of our deployment database (a brand new database with the software's basic data), I have go to SSMS, select the database, Tasks -> Scripts -> Generate Scripts. Then I have a very specific setting which I have to change every single time to the same values.

This is very time consuming and looks like a task that could very well be automated. Is it possible to automate this task from within SSMS, with my specific settings? If not, is there a way to automate it outside SSMS? I do not discard writing a C# console program to perform that, this task is not for the final user it's for our labs.

UPDATE

The objective here, as usual, is getting the solution which will take less time. So, before I got to the C# coding level, I would like to try automating the existing tools. The order would be:

Can I automate from SSMS?
Can I automate by running a outside script?
How can I automate with C# coding? (the learning resources were pointed out by Nicholas Carey)

thomasswilliams · Accepted Answer · 2011-04-13 00:24:49Z

2

What are you using the script for? (I can think of backup, release versioning, source control for logging each change)

backup -> could be done by keeping a database backup
release versioning -> perhaps use SMO as suggested, you'd have control of what gets scripted and in what order
source control -> for the time it would take to develop/test/refine, Red Gate's http://www.red-gate.com/products/sql-development/sql-source-control/ might be a better answer

answered Apr 13, 2011 at 0:24

thomasswilliams

3982 silver badges10 bronze badges

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

1 Comment

Adriano Carneiro Over a year ago

The general objective is both release versioning and source control, but I already manage to do it using SVN. This is our deployment database, so it contains the structure and the basic data to get started. The only thing is that I would like to automate the processes. I just thought there might be a way to save my settings. I lose 2-3 minutes a day on that, so I don't think it's worth writing a C# program using SMO (maybe later). But you gave a good overview of the options.

Nicholas Carey · Accepted Answer · 2011-04-12 16:55:23Z

0

Look at SMO: http://msdn.microsoft.com/en-us/library/ms162169(v=SQL.100).aspx

Then script your database, put the scripts in source control and require that changes be made via scripts.

answered Apr 12, 2011 at 16:55

Nicholas Carey

75.1k16 gold badges96 silver badges138 bronze badges

Collectives™ on Stack Overflow

Command to script database structure and data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related