Free utility for script DB objects in ms sql

Question

I am trying to implement database source control.

the tool I need is to create a separate file for each object in the database, preferably located in folders, for example

stored procedures of the table views function

and it would be great to also delete the results of certain queries in order to track data changes in several configuration tables ...

I wonder if there is already a tool that can handle such things ...

-

just clear a few things ...

I am already using sql delta to handle update scripts ...

I would like to have DB scripts for use with subversion, so I can keep track of which objects were changed with each commit without studying update scripts ...

I am developing a good vb script with SQL Distributed Management Objects (SQL-DMO), I will tell you how this happens ...

What is good about my own solution is that I can also include query outputs or executing a stored procedure, track changes in certain tables, server configuration, database growth, well, whatever I select a text file ...

Answer 1

I use ScriptDB just for this purpose. The only thing I had to change was to delete the creation date of the scripts in the generated files. Otherwise, files are always marked as modified in Subversion.

Here is the package I am using. svnclient is a tool from codeplex svncompletesync.codeplex.com to check all files from a folder in subversion .:

svn checkout "http://svn/myproject" D:\Projekte\db_svn\myproject

ScriptDB "D:\temp\scriptdb" myserver mydb mylogin mypwd

del "D:\Projekte\db_svn\myproject\Schema Objects\\*.sql" /q /s

xcopy "D:\temp\scriptdb\myserver\mydb\Schema Objects\\*.sql" "D:\Projekte\db_svn\myproject\Schema Objects" /e /y /i

svnclient "D:\Projekte\db_svn\myproject" -m "commit durch svncompletesync"

Answer 2

If you understand correctly, you need two things: first you need to generate scripts from the database metadata (tables, views, stored procedures, etc.), and once this is done, you need to use some kind of consistent methodology for the version script.

If you already have metadata and data in the database, I don’t see what would prevent you from using SQL Management Studio (or SQL Enterprise Manager) to generate scripts from database objects: see How to create a script (SQL Server Management Studio) . This should work for SQL Server 2000, 2005, etc. Keep in mind that you can configure script generation options, for example. instead of one huge script, you can use separate scripts for each object. You may need to write some scripts to populate the tables with data (I'm not sure if the wizard supports data extraction).

Once you get the scripts, you probably have to manually distribute them between specific folders, and when this is done, you should be prepared to test them in the original control. From now on, you need to find out the methodology for subsequent installations, upgrades and repairs of the database. This is a rather difficult task, encompassing something that takes longer than a simple answer. If you are interested in possible options, check out my Database Update , which mentions a number of approaches and links to several articles on database versioning (sorry for self-promotion, but I don't want to repeat the same information).

Answer 3

Most of the tools in this field are not free, but there is an open source project, ScriptDB , that can satisfy your scripting needs.

This will not solve the problem of how to apply scripts to the database in the correct order - if you do not want to pay, you may have to improvise your own.

Answer 4

You can try Wizardby , which is not exactly what you are asking for, but you can still help you manage change management of the database. It can redesign your database schema (well, its subset) and then write the so-called “migrations” in a special platform independent DSL:

 version 20090331140131: oxite_FileResource: FileResourceID type => PK, primary-key => true SiteID type => Guid, nullable => false FileResourceName type => LongName CreatorUserID references => oxite_User Data type => Binary ContentType type => AnsiString, length => 25, nullable => false Path type => String, length => 1000, nullable => false State type => Byte, nullable => false CreatedDate type => DateTime, nullable => false ModifiedDate type => DateTime, nullable => false oxite_UserFileResourceRelationship: UserID references => oxite_User FileResourceID references => oxite_FileResource: add index unique => true index "" columns => [UserID, FileResourceID], unique => true, clustered => true