Do you use source control for your database items

Question

I feel that my shop has a hole because we don't have a solid process in place for versioning our database schema changes. We do a lot of backups so we're more or less covered, but it's bad practice to rely on your last line of defense in this way.

Surprisingly, this seems to be a common thread. Many shops I have spoken to ignore this issue because their databases don't change often, and they basically just try to be meticulous.

However, I know how that story goes. It's only a matter of time before things line up just wrong and something goes missing.

Are there any best practices for this? What are some strategies that have worked for you?

User · Answer

I use SQL CREATE scripts exported from MySQL Workbech, then using theirs "Export SQL ALTER" functionality I end up with a series of create scripts(numbered of course) and the alter scripts that can apply the changes between them.

3.- Export SQL ALTER script Normally you would have to write the ALTER TABLE statements by hand now, reflecting your changes you made to the model. But you can be smart and let Workbench do the hard work for you. Simply select File -> Export -> Forward Engineer SQL ALTER Script… from the main menu.

This will prompt you to specify the SQL CREATE file the current model should be compared to.

Select the SQL CREATE script from step 1. The tool will then generate the ALTER TABLE script for you and you can execute this script against your database to bring it up to date.

You can do this using the MySQL Query Browser or the mysql client.Voila! Your model and database have now been synchronized!

Source: MySQL Workbench Community Edition: Guide to Schema Synchronization

All this scripts of course are inside under version control.

User · Answer

This has always been a big annoyance for me too - it seems like it is just way too easy to make a quick change to your development database, save it (forgetting to save a change script), and then you're stuck. You could undo what you just did and redo it to create the change script, or write it from scratch if you want of course too, though that's a lot of time spent writing scripts.

A tool that I have used in the past that has helped with this some is SQL Delta. It will show you the differences between two databases (SQL server/Oracle I believe) and generate all the change scripts necessary to migrate A->B. Another nice thing it does is show all the differences between database content between the production (or test) DB and your development DB. Since more and more apps store configuration and state that is crucial to their execution in database tables, it can be a real pain to have change scripts that remove, add, and alter the proper rows. SQL Delta shows the rows in the database just like they would look in a Diff tool - changed, added, deleted.

An excellent tool. Here is the link: http://www.sqldelta.com/

User · Answer

The best practice I have seen is creating a build script to scrap and rebuild your database on a staging server. Each iteration was given a folder for database changes, all changes were scripted with "Drop... Create" 's . This way you can rollback to an earlier version at any time by pointing the build to folder you want to version to.

I believe this was done with NaNt/CruiseControl.

User · Answer

Yes  of course  We generate dumps of our PostgreSQL schemas whenever there s a change and check it in  It s already saved us many times  and I ve only been at my job a few months

User · Answer

I believe that every DB should be under source control  and developers should have an easy way to create their local database from scratch   Inspired by Visual Studio for Database Professionals  I ve created an open-source tool that scripts MS SQL databases  and provides and easy way of deploying them to your local DB engine   Try http   dbsourcetools codeplex com    Have fun  - Nathan

User · Answer

I always check my database structure dumps into source control   Full database dumps however I normally just compress and put away for storage

User · Answer

I use ActiveRecord Migrations   This Ruby gem can be used outside of a Rails project and there are adapters to handle most databases you ll come across   My tip  if you are able to run your project off Postgres  you get transactional schema migrations   That means you don t end up with a broken database if a migration only half-applies

User · Answer

FYI This was also brought up a few days ago by Dana     Stored procedures DB schema in source control

User · Answer

We maintain DDL  and sometime DML  scripts generated by our ER Tool  PowerAMC    We have a bench of shell scripts which rename the scripts starting with a number on the trunk branch  Each script is committed and tagged with the bugzilla number   These scripts are then at need merged within the release branches along with the application code   We have a table recording the scripts and their status  Each script is executed in order and recorded in this table on each install by the deploying tool

User · Answer

My team versions our database schema as C  classes with the rest of our code   We have a homegrown C  program   lt 500 lines of code  that reflects the classes and creates SQL commands to build  drop and update the database   After creating the database we run sqlmetal to generate a linq mapping  which is then compiled in another project that is used to generate test data   The whole things works really well because data access is checked at compile time   We like it because the schema is stored in a  cs file which is easy to track compare in trac svn

User · Answer

I have everything necessary to recreate my DB from bare metal  minus the data itself   I m sure there are lots of ways to do it  but all my scripts and such are stored off in subversion and we can rebuild the DB structure and such by pulling all that out of subversion and running an installer

User · Answer

We use replication and clustering to manage our databases, as well as backups. We use Serena to manage our SQL scripts and configuration implementations. Before a configuration change is made, we perform a backup as part of the change management process. This backup satisfies our rollback requirement.

I think it all depends on scale. Are you talking about enterprise applications that need offsite backups and disaster recovery? A small workgroup running an accounting application? Or everywhere in between?

User · Answer

I version control the create script  and I use the svn version tag within it  Then  whenever I get a version that is going to be used  I create a script in a dbpatches  directory named as the version to roll up to  The job of that script is to modify a current database without destroying the data  dbpatches   for example  might have files named 201  220  and 240  If the database is currently at level 201  apply patch 220  then patch 240   DROP TABLE IF EXISTS  meta   CREATE TABLE  meta       property  varchar 255      value  varchar 255     PRIMARY KEY   property     ENGINE InnoDB DEFAULT CHARSET latin1  INSERT INTO  meta  VALUES   version     Rev  240        Don t forget to test your code before considering a patch good  Caveat emptor

User · Answer

Here is a sample poor man s solution for a trigger implementing tracking of changes on db objects   via DDL stateements   on a sql server 2005   2008 database  I contains also a simple sample of how-to enforce the usage of required someValue  xml tag in the source code for each sql command ran on the database   the tracking of the current db version and type   dev   test   qa   fb   prod   One could extend it with additional required attributes such as     etc   The code is rather long - it creates the empty database   the needed tracking table structure   required db functions and the populating trigger all running under a  ga  schema    USE  master  GO          Object   Database  DBGA DEV     Script Date  04 22 2009 13 22 01         CREATE DATABASE  DBGA DEV  ON  PRIMARY    NAME   N DBGA DEV   FILENAME   N D  GENAPP DATA DBFILES DBGA DEV mdf    SIZE   3072KB   MAXSIZE   UNLIMITED  FILEGROWTH   1024KB    LOG ON    NAME   N DBGA DEV log   FILENAME   N D  GENAPP DATA DBFILES DBGA DEV log ldf    SIZE   6208KB   MAXSIZE   2048GB   FILEGROWTH   10   GO  ALTER DATABASE  DBGA DEV  SET COMPATIBILITY LEVEL   100 GO  IF  1   FULLTEXTSERVICEPROPERTY  IsFullTextInstalled    begin EXEC  DBGA DEV   dbo   sp fulltext database   action    enable  end GO  ALTER DATABASE  DBGA DEV  SET ANSI NULL DEFAULT OFF  GO  ALTER DATABASE  DBGA DEV  SET ANSI NULLS OFF  GO  ALTER DATABASE  DBGA DEV  SET ANSI PADDING ON  GO  ALTER DATABASE  DBGA DEV  SET ANSI WARNINGS OFF  GO  ALTER DATABASE  DBGA DEV  SET ARITHABORT OFF  GO  ALTER DATABASE  DBGA DEV  SET AUTO CLOSE OFF  GO  ALTER DATABASE  DBGA DEV  SET AUTO CREATE STATISTICS ON  GO  ALTER DATABASE  DBGA DEV  SET AUTO SHRINK OFF  GO  ALTER DATABASE  DBGA DEV  SET AUTO UPDATE STATISTICS ON  GO  ALTER DATABASE  DBGA DEV  SET CURSOR CLOSE ON COMMIT OFF  GO  ALTER DATABASE  DBGA DEV  SET CURSOR DEFAULT  GLOBAL  GO  ALTER DATABASE  DBGA DEV  SET CONCAT NULL YIELDS NULL OFF  GO  ALTER DATABASE  DBGA DEV  SET NUMERIC ROUNDABORT OFF  GO  ALTER DATABASE  DBGA DEV  SET QUOTED IDENTIFIER OFF  GO  ALTER DATABASE  DBGA DEV  SET RECURSIVE TRIGGERS OFF  GO  ALTER DATABASE  DBGA DEV  SET  DISABLE BROKER  GO  ALTER DATABASE  DBGA DEV  SET AUTO UPDATE STATISTICS ASYNC OFF  GO  ALTER DATABASE  DBGA DEV  SET DATE CORRELATION OPTIMIZATION OFF  GO  ALTER DATABASE  DBGA DEV  SET TRUSTWORTHY OFF  GO  ALTER DATABASE  DBGA DEV  SET ALLOW SNAPSHOT ISOLATION OFF  GO  ALTER DATABASE  DBGA DEV  SET PARAMETERIZATION SIMPLE  GO  ALTER DATABASE  DBGA DEV  SET READ COMMITTED SNAPSHOT OFF  GO  ALTER DATABASE  DBGA DEV  SET HONOR BROKER PRIORITY OFF  GO  ALTER DATABASE  DBGA DEV  SET  READ WRITE  GO  ALTER DATABASE  DBGA DEV  SET RECOVERY FULL  GO  ALTER DATABASE  DBGA DEV  SET  MULTI USER  GO  ALTER DATABASE  DBGA DEV  SET PAGE VERIFY CHECKSUM   GO  ALTER DATABASE  DBGA DEV  SET DB CHAINING OFF  GO  EXEC  DBGA DEV  sys sp addextendedproperty  name N DbType    value N DEV   GO  EXEC  DBGA DEV  sys sp addextendedproperty  name N DbVersion    value N 0 0 1 20090414 1100   GO    USE  DBGA DEV  GO         Object   Schema  ga     Script Date  04 22 2009 13 21 29         CREATE SCHEMA  ga  AUTHORIZATION  dbo  GO EXEC sys sp addextendedproperty  name N MS Description    value N Contains the objects of the Generic Application database     level0type N SCHEMA   level0name N ga  GO         Object   Table  ga   tb DataMeta ObjChangeLog     Script Date  04 22 2009 13 21 40         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO SET ANSI PADDING ON GO CREATE TABLE  ga   tb DataMeta ObjChangeLog        LogId   int  IDENTITY 1 1  NOT NULL       TimeStamp   timestamp  NOT NULL       DatabaseName   varchar  256  NOT NULL       SchemaName   varchar  256  NOT NULL       DbVersion   varchar  20  NOT NULL       DbType   varchar  20  NOT NULL       EventType   varchar  50  NOT NULL       ObjectName   varchar  256  NOT NULL       ObjectType   varchar  25  NOT NULL       Version   varchar  50  NULL       SqlCommand   varchar  max  NOT NULL       EventDate   datetime  NOT NULL       LoginName   varchar  256  NOT NULL       FirstName   varchar  256  NULL       LastName   varchar  50  NULL       ChangeDescription   varchar  1000  NULL       Description   varchar  1000  NULL       ObjVersion   varchar  20  NOT NULL   ON  PRIMARY  GO SET ANSI PADDING ON GO EXEC sys sp addextendedproperty  name N MS Description    value N The database version as written in the extended prop of the database     level0type N SCHEMA   level0name N ga    level1type N TABLE   level1name N tb DataMeta ObjChangeLog    level2type N COLUMN   level2name N DbVersion  GO EXEC sys sp addextendedproperty  name N MS Description    value N dev   test   qa   fb or prod     level0type N SCHEMA   level0name N ga    level1type N TABLE   level1name N tb DataMeta ObjChangeLog    level2type N COLUMN   level2name N DbType  GO EXEC sys sp addextendedproperty  name N MS Description    value N The name of the object as it is registered in the sys objects      level0type N SCHEMA   level0name N ga    level1type N TABLE   level1name N tb DataMeta ObjChangeLog    level2type N COLUMN   level2name N ObjectName  GO EXEC sys sp addextendedproperty  name N MS Description    value N      level0type N SCHEMA   level0name N ga    level1type N TABLE   level1name N tb DataMeta ObjChangeLog    level2type N COLUMN   level2name N Description  GO SET IDENTITY INSERT  ga   tb DataMeta ObjChangeLog  ON INSERT  ga   tb DataMeta ObjChangeLog    LogId    DatabaseName    SchemaName    DbVersion    DbType    EventType    ObjectName    ObjectType    Version    SqlCommand    EventDate    LoginName    FirstName    LastName    ChangeDescription    Description    ObjVersion   VALUES  3  N DBGA DEV   N en   N 0 0 1 20090414 1100   N DEV   N DROP TABLE   N tb BL Products   N TABLE   N  some   N  lt EVENT INSTANCE gt  lt EventType gt DROP TABLE lt  EventType gt  lt PostTime gt 2009-04-22T11 03 11 880 lt  PostTime gt  lt SPID gt 57 lt  SPID gt  lt ServerName gt YSG lt  ServerName gt  lt LoginName gt ysg yordgeor lt  LoginName gt  lt UserName gt dbo lt  UserName gt  lt DatabaseName gt DBGA DEV lt  DatabaseName gt  lt SchemaName gt en lt  SchemaName gt  lt ObjectName gt tb BL Products lt  ObjectName gt  lt ObjectType gt TABLE lt  ObjectType gt  lt TSQLCommand gt  lt SetOptions ANSI NULLS  ON  ANSI NULL DEFAULT  ON  ANSI PADDING  ON  QUOTED IDENTIFIER  ON  ENCRYPTED  FALSE   gt  lt CommandText gt drop TABLE  en   tb BL Products  -- lt Version gt  some lt  Version gt  amp  x0D   lt  CommandText gt  lt  TSQLCommand gt  lt  EVENT INSTANCE gt    CAST 0x00009BF300B6271C AS DateTime   N ysg yordgeor   N Yordan   N Georgiev   NULL  NULL  N 0 0 0   INSERT  ga   tb DataMeta ObjChangeLog    LogId    DatabaseName    SchemaName    DbVersion    DbType    EventType    ObjectName    ObjectType    Version    SqlCommand    EventDate    LoginName    FirstName    LastName    ChangeDescription    Description    ObjVersion   VALUES  4  N DBGA DEV   N en   N 0 0 1 20090414 1100   N DEV   N CREATE TABLE   N tb BL Products   N TABLE   N  2 2 2    N  lt EVENT INSTANCE gt  lt EventType gt CREATE TABLE lt  EventType gt  lt PostTime gt 2009-04-22T11 03 18 620 lt  PostTime gt  lt SPID gt 57 lt  SPID gt  lt ServerName gt YSG lt  ServerName gt  lt LoginName gt ysg yordgeor lt  LoginName gt  lt UserName gt dbo lt  UserName gt  lt DatabaseName gt DBGA DEV lt  DatabaseName gt  lt SchemaName gt en lt  SchemaName gt  lt ObjectName gt tb BL Products lt  ObjectName gt  lt ObjectType gt TABLE lt  ObjectType gt  lt TSQLCommand gt  lt SetOptions ANSI NULLS  ON  ANSI NULL DEFAULT  ON  ANSI PADDING  ON  QUOTED IDENTIFIER  ON  ENCRYPTED  FALSE   gt  lt CommandText gt CREATE TABLE  en   tb BL Products   amp  x0D       ProducId   int  NULL  amp  x0D       ProductName   nchar  10  NULL  amp  x0D       ProductDescription   varchar  5000  NULL amp  x0D    ON  PRIMARY  amp  x0D     amp  x0D   lt Version gt  2 2 2  lt  Version gt  amp  x0D   amp  x0D     amp  x0D   lt  CommandText gt  lt  TSQLCommand gt  lt  EVENT INSTANCE gt    CAST 0x00009BF300B62F07 AS DateTime   N ysg yordgeor   N Yordan   N Georgiev   NULL  NULL  N 0 0 0   INSERT  ga   tb DataMeta ObjChangeLog    LogId    DatabaseName    SchemaName    DbVersion    DbType    EventType    ObjectName    ObjectType    Version    SqlCommand    EventDate    LoginName    FirstName    LastName    ChangeDescription    Description    ObjVersion   VALUES  5  N DBGA DEV   N en   N 0 0 1 20090414 1100   N DEV   N DROP TABLE   N tb BL Products   N TABLE   N  2 2 2    N  lt EVENT INSTANCE gt  lt EventType gt DROP TABLE lt  EventType gt  lt PostTime gt 2009-04-22T11 25 12 620 lt  PostTime gt  lt SPID gt 57 lt  SPID gt  lt ServerName gt YSG lt  ServerName gt  lt LoginName gt ysg yordgeor lt  LoginName gt  lt UserName gt dbo lt  UserName gt  lt DatabaseName gt DBGA DEV lt  DatabaseName gt  lt SchemaName gt en lt  SchemaName gt  lt ObjectName gt tb BL Products lt  ObjectName gt  lt ObjectType gt TABLE lt  ObjectType gt  lt TSQLCommand gt  lt SetOptions ANSI NULLS  ON  ANSI NULL DEFAULT  ON  ANSI PADDING  ON  QUOTED IDENTIFIER  ON  ENCRYPTED  FALSE   gt  lt CommandText gt drop TABLE  en   tb BL Products   amp  x0D   lt  CommandText gt  lt  TSQLCommand gt  lt  EVENT INSTANCE gt    CAST 0x00009BF300BC32F1 AS DateTime   N ysg yordgeor   N Yordan   N Georgiev   NULL  NULL  N 0 0 0   INSERT  ga   tb DataMeta ObjChangeLog    LogId    DatabaseName    SchemaName    DbVersion    DbType    EventType    ObjectName    ObjectType    Version    SqlCommand    EventDate    LoginName    FirstName    LastName    ChangeDescription    Description    ObjVersion   VALUES  6  N DBGA DEV   N en   N 0 0 1 20090414 1100   N DEV   N CREATE TABLE   N tb BL Products   N TABLE   N  2 2 2    N  lt EVENT INSTANCE gt  lt EventType gt CREATE TABLE lt  EventType gt  lt PostTime gt 2009-04-22T11 25 19 053 lt  PostTime gt  lt SPID gt 57 lt  SPID gt  lt ServerName gt YSG lt  ServerName gt  lt LoginName gt ysg yordgeor lt  LoginName gt  lt UserName gt dbo lt  UserName gt  lt DatabaseName gt DBGA DEV lt  DatabaseName gt  lt SchemaName gt en lt  SchemaName gt  lt ObjectName gt tb BL Products lt  ObjectName gt  lt ObjectType gt TABLE lt  ObjectType gt  lt TSQLCommand gt  lt SetOptions ANSI NULLS  ON  ANSI NULL DEFAULT  ON  ANSI PADDING  ON  QUOTED IDENTIFIER  ON  ENCRYPTED  FALSE   gt  lt CommandText gt CREATE TABLE  en   tb BL Products   amp  x0D       ProducId   int  NULL  amp  x0D       ProductName   nchar  10  NULL  amp  x0D       ProductDescription   varchar  5000  NULL amp  x0D    ON  PRIMARY  amp  x0D     amp  x0D   lt Version gt  2 2 2  lt  Version gt  amp  x0D   amp  x0D     amp  x0D   lt  CommandText gt  lt  TSQLCommand gt  lt  EVENT INSTANCE gt    CAST 0x00009BF300BC3A69 AS DateTime   N ysg yordgeor   N Yordan   N Georgiev   NULL  NULL  N 0 0 0   SET IDENTITY INSERT  ga   tb DataMeta ObjChangeLog  OFF         Object   Table  ga   tb BLSec LoginsForUsers     Script Date  04 22 2009 13 21 40         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO SET ANSI PADDING ON GO CREATE TABLE  ga   tb BLSec LoginsForUsers        LoginsForUsersId   int  IDENTITY 1 1  NOT NULL       LoginName   nvarchar  100  NOT NULL       FirstName   varchar  100  NOT NULL       SecondName   varchar  100  NULL       LastName   varchar  100  NOT NULL       DomainName   varchar  100  NOT NULL   ON  PRIMARY  GO SET ANSI PADDING ON GO SET IDENTITY INSERT  ga   tb BLSec LoginsForUsers  ON INSERT  ga   tb BLSec LoginsForUsers    LoginsForUsersId    LoginName    FirstName    SecondName    LastName    DomainName   VALUES  1  N ysg yordgeor   N Yordan   N Stanchev   N Georgiev   N yordgeor   SET IDENTITY INSERT  ga   tb BLSec LoginsForUsers  OFF         Object   Table  en   tb BL Products     Script Date  04 22 2009 13 21 40         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO SET ANSI PADDING ON GO CREATE TABLE  en   tb BL Products        ProducId   int  NULL       ProductName   nchar  10  NULL       ProductDescription   varchar  5000  NULL   ON  PRIMARY  GO SET ANSI PADDING ON GO         Object   StoredProcedure  ga   procUtils SqlCheatSheet     Script Date  04 22 2009 13 21 37         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO CREATE PROCEDURE  ga   procUtils SqlCheatSheet                   as                  set nocount on                   --what was the name of the table with something like role                                    SELECT   from sys tables where  name  like   POC                                      -- what are the columns of this table                                     select column name   DATA TYPE   CHARACTER MAXIMUM LENGTH   table name  from Information schema columns where table name  tbGui ExecutePOC                                      -- find proc         --what was the name of procedure with something like role                                    select   from sys procedures where  name  like   ext                   exec sp HelpText procName                                               exec sp helpText procUtils InsertGenerator                                    --how to list all databases in sql server                                     SELECT database id AS ID  NULL AS ParentID  name AS Text FROM sys databases ORDER BY  name                                      --HOW-TO LIST ALL TABLES IN A SQL SERVER 2005 DATABASE                                    SELECT TABLE NAME FROM  POC  INFORMATION SCHEMA TABLES                 WHERE TABLE TYPE    BASE TABLE                    AND TABLE NAME  lt  gt   dtproperties                    ORDER BY TABLE NAME                                      --HOW-TO ENABLE XP CMDSHELL START                 -------------------------------------------------------------------------                 -- configure verbose mode temporarily                  -- EXECUTE sp configure  show advanced options   1                  -- RECONFIGURE WITH OVERRIDE                  --GO                    --ENABLE xp cmdshell                  -- EXECUTE sp configure  xp cmdshell    1                   -- RECONFIGURE WITH OVERRIDE                  -- EXEC SP CONFIGURE  show advanced option    1                    -- SHOW THE CONFIGURATION                  -- EXEC SP CONFIGURE                     --turn show advance options off                  -- GO                  --EXECUTE sp configure  show advanced options   0                  -- RECONFIGURE WITH OVERRIDE                  -- GO                  --HOW-TO ENABLE XP CMDSHELL END                 -------------------------------------------------------------------------                  --HOW-TO IMPLEMENT SLEEP                  -- sleep for 10 seconds                  -- WAITFOR DELAY  00 00 10  SELECT   FROM My Table                     LIST ALL PRIMARY KEYS                   SELECT                    INFORMATION SCHEMA TABLE CONSTRAINTS TABLE NAME AS TABLE NAME                    INFORMATION SCHEMA KEY COLUMN USAGE COLUMN NAME AS COLUMN NAME                     REPLACE INFORMATION SCHEMA TABLE CONSTRAINTS CONSTRAINT TYPE           AS CONSTRAINT TYPE                  FROM                    INFORMATION SCHEMA TABLE CONSTRAINTS                    INNER JOIN INFORMATION SCHEMA KEY COLUMN USAGE ON                    INFORMATION SCHEMA TABLE CONSTRAINTS CONSTRAINT NAME                      INFORMATION SCHEMA KEY COLUMN USAGE CONSTRAINT NAME                  WHERE                    INFORMATION SCHEMA TABLE CONSTRAINTS TABLE NAME  lt  gt  N sysdiagrams                   ORDER BY                    INFORMATION SCHEMA TABLE CONSTRAINTS TABLE NAME ASC                                      --HOW-TO COPY TABLE AND THE WHOLE TABLE DATA   COPY TABLE FROM DB TO DB                 --                                                  START                                     use Poc Dev                 go                 drop table tbGui LinksVisibility                  use POc test                 go                 select                    INTO  POC Dev   ga   tbGui LinksVisibility                  from  POC TEST   ga   tbGui LinksVisibility                                       --HOW-TO COPY TABLE AND THE WHOLE TABLE DATA   COPY TABLE FROM DB TO DB                 --                                                    END                 --                                                    SEE TABLE METADATA START                                       SELECT c name AS  COLUMN NAME   sc data type AS  DATA TYPE    value  AS                   DESCRIPTION    c max length as  MAX LENGTH    c is nullable AS  OPTIONAL                     c is identity AS  IS PRIMARY KEY  FROM sys extended properties AS ep                  INNER JOIN sys tables AS t ON ep major id   t object id                  INNER JOIN sys columns AS c ON ep major id   c object id AND ep minor id                    c column id                  INNER JOIN INFORMATION SCHEMA COLUMNS sc ON t name   sc table name and                  c name   sc column name                  WHERE class   1 and t name    tbGui ExecutePOC  ORDER BY SC DATA TYPE                                      --                                                    SEE TABLE METADATA END                                   select   from Information schema columns                 select table name   column name from Information schema columns where table name  tbGui Wizards                                       --                                                    LIST ALL TABLES AND THEIR DESCRIPTOINS START                                     SELECT T name AS TableName  CAST Props value AS varchar 1000   AS                 TableDescription                 FROM sys tables AS T LEFT OUTER JOIN                  SELECT class  class desc  major id  minor id                  name  value                 FROM sys extended properties                 WHERE  minor id   0  AND  class   1   AS                 Props ON T object id   Props major id                 WHERE  T type    U   AND  T name  lt  gt  N sysdiagrams                   ORDER BY TableName                                    --                                                    LIST ALL TABLES AND THEIR DESCRIPTOINS START                  --                                                    LIST ALL OBJECTS FROM DB START                                      use DB                 --HOW-TO LIST ALL PROCEDURE IN A DATABASE                 select s name from sysobjects s where type    P                  --HOW-TO LIST ALL TRIGGERS BY NAME IN A DATABASE                 select s name from sysobjects s where type    TR                  --HOW-TO LIST TABLES IN A DATABASE                  select s name from sysobjects s where type    U                  --how-to list all system tables in a database                 select s name from sysobjects s where type    s                  --how-to list all the views in a database                 select s name from sysobjects s where type    v                                                           Similarly you can find out other objects created by user  simple change type                     C   CHECK constraint                   D   Default or DEFAULT constraint                   F   FOREIGN KEY constraint                   L   Log                   FN   Scalar function                   IF   In-lined table-function                   P   Stored procedure                   PK   PRIMARY KEY constraint  type is K                    RF   Replication filter stored procedure                  S   System table                   TF   Table function                   TR   Trigger                   U   User table   this is the one I discussed above in the example                   UQ   UNIQUE constraint  type is K                    V   View                   X   Extended stored procedure                                       --                                                    HOW-TO SEE ALL MY PERMISSIONS START                                       SELECT   FROM fn my permissions NULL   SERVER                    USE poc qa                  SELECT   FROM fn my permissions  NULL   database                    GO                                     --                                                    HOW-TO SEE ALL MY PERMISSIONS END                                    --find table                 use poc dev                go                select s name from sysobjects s where type    u   and s name like   Visibility                 select   from tbGui LinksVisibility                                     find cursor                use poc       go         DECLARE  procName varchar 100          DECLARE  cursorProcNames CURSOR         SET  cursorProcNames   CURSOR FOR         select name from sys procedures where modify date  gt   2009-02-05 13 12 15 273  order by modify date desc         OPEN  cursorProcNames         FETCH NEXT         FROM  cursorProcNames INTO  procName         WHILE   FETCH STATUS   0         BEGIN          set nocount off          exec sp HelpText  procName --- or print them         -- print  procName          FETCH NEXT         FROM  cursorProcNames INTO  procName         END         CLOSE  cursorProcNames          select   error                            --  SEE STORED PROCEDURE EXT PROPS              SELECT ep name as  EXT PROP NAME    SP NAME    value  as  DESCRIPTION  FROM sys extended properties as ep left join sys procedures as sp on sp object id   ep major id where sp type  P                -- what the hell I ve been doing lately on sql server 2005   2008 select o name     SELECT  definition  AS  text    FROM sys all sql modules where sys all sql modules object id a object id FOR XML PATH      TYPE  AS Statement Text   a object id  o modify date from sys all sql modules a left join sys objects o on a object id o object id order by 4 desc    -- GET THE RIGHT LANG SCHEMA START  DECLARE  template AS varchar max  SET  template    SELECT   FROM  object name    DECLARE  object name AS sysname  SELECT  object name   QUOTENAME s name          QUOTENAME o name  FROM sys objects o INNER JOIN sys schemas s     ON s schema id   o schema id WHERE o object id   OBJECT ID QUOTENAME  LANG       TestingLanguagesInNameSpacesDelMe     IF  object name IS NOT NULL BEGIN     DECLARE  sql AS varchar max      SET  sql   REPLACE  template    object name     object name      EXEC   sql  END -- GET THE RIGHT LANG SCHEMA END   --  SEE STORED PROCEDURE EXT PROPS end                set nocount off GO EXEC sys sp addextendedproperty  name N AuthorName    value N Yordan Georgiev     level0type N SCHEMA   level0name N ga    level1type N PROCEDURE   level1name N procUtils SqlCheatSheet  GO EXEC sys sp addextendedproperty  name N ProcDescription    value N TODO ADD HERE DESCRPIPTION     level0type N SCHEMA   level0name N ga    level1type N PROCEDURE   level1name N procUtils SqlCheatSheet  GO EXEC sys sp addextendedproperty  name N ProcVersion    value N 0 1 0 20090406 1317     level0type N SCHEMA   level0name N ga    level1type N PROCEDURE   level1name N procUtils SqlCheatSheet  GO         Object   UserDefinedFunction  ga   GetDbVersion     Script Date  04 22 2009 13 21 42         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO CREATE FUNCTION  ga   GetDbVersion    RETURNS VARCHAR 20      BEGIN         RETURN convert varchar 20     select value from sys extended properties where name  DbVersion  and class desc  DATABASE         END GO EXEC sys sp addextendedproperty  name N AuthorName    value N Yordan Georgiev     level0type N SCHEMA   level0name N ga    level1type N FUNCTION   level1name N GetDbVersion  GO EXEC sys sp addextendedproperty  name N ChangeDescription    value N Initial creation     level0type N SCHEMA   level0name N ga    level1type N FUNCTION   level1name N GetDbVersion  GO EXEC sys sp addextendedproperty  name N CreatedWhen    value N getDate       level0type N SCHEMA   level0name N ga    level1type N FUNCTION   level1name N GetDbVersion  GO EXEC sys sp addextendedproperty  name N Description    value N Gets the current version of the database      level0type N SCHEMA   level0name N ga    level1type N FUNCTION   level1name N GetDbVersion  GO         Object   UserDefinedFunction  ga   GetDbType     Script Date  04 22 2009 13 21 42         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO CREATE FUNCTION  ga   GetDbType    RETURNS VARCHAR 30      BEGIN         RETURN convert varchar 30     select value from sys extended properties where name  DbType  and class desc  DATABASE         END GO         Object   Default  DF tb DataMeta ObjChangeLog DbVersion     Script Date  04 22 2009 13 21 40         ALTER TABLE  ga   tb DataMeta ObjChangeLog  ADD  CONSTRAINT  DF tb DataMeta ObjChangeLog DbVersion   DEFAULT   select ga GetDbVersion     FOR  DbVersion  GO         Object   Default  DF tb DataMeta ObjChangeLog EventDate     Script Date  04 22 2009 13 21 40         ALTER TABLE  ga   tb DataMeta ObjChangeLog  ADD  CONSTRAINT  DF tb DataMeta ObjChangeLog EventDate   DEFAULT  getdate    FOR  EventDate  GO         Object   Default  DF tb DataMeta ObjChangeLog ObjVersion     Script Date  04 22 2009 13 21 40         ALTER TABLE  ga   tb DataMeta ObjChangeLog  ADD  CONSTRAINT  DF tb DataMeta ObjChangeLog ObjVersion   DEFAULT   0 0 0   FOR  ObjVersion  GO         Object   DdlTrigger  trigMetaDoc TraceDbChanges     Script Date  04 22 2009 13 21 29         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO create trigger  trigMetaDoc TraceDbChanges  on database for create procedure  alter procedure  drop procedure  create table  alter table  drop table  create function  alter function  drop function    create trigger   alter trigger   drop trigger  as  set nocount on  declare  data xml set  data   EVENTDATA   declare  DbVersion varchar 20  set  DbVersion   select ga GetDbVersion    declare  DbType varchar 20  set  DbType   select ga GetDbType    declare  DbName varchar 256  set  DbName   data value    EVENT INSTANCE DatabaseName  1     varchar 256    declare  EventType varchar 256  set  EventType   data value    EVENT INSTANCE EventType  1     varchar 50    declare  ObjectName varchar 256  set  ObjectName     data value    EVENT INSTANCE ObjectName  1     varchar 256    declare  ObjectType varchar 25  set  ObjectType    data value    EVENT INSTANCE ObjectType  1     varchar 25    declare  TSQLCommand varchar max  set  TSQLCommand    data value    EVENT INSTANCE TSQLCommand  1     varchar max    declare  opentag varchar 4  set  opentag    amp lt   declare  closetag varchar 4   set  closetag    amp gt   declare  newDataTxt varchar max   set  newDataTxt  cast  data as varchar max   set  newDataTxt   REPLACE   REPLACE  newDataTxt    opentag     lt       closetag     gt    -- print  newDataTxt declare  newDataXml xml  set  newDataXml   CONVERT   xml    newDataTxt  declare  Version varchar 50  set  Version    newDataXml value    EVENT INSTANCE TSQLCommand CommandText Version  1     varchar 50     -- if we are dropping take the version from the existing object  if    SUBSTRING  EventType   0   5      DROP  set  Version    select top 1  Version   from ga tb DataMeta ObjChangeLog where ObjectName  ObjectName order by  LogId  desc     declare  LoginName varchar 256   set  LoginName    data value    EVENT INSTANCE LoginName  1     varchar 256    declare  FirstName varchar 50  set  FirstName   select  FirstName  from  ga   tb BLSec LoginsForUsers  where  LoginName     LoginName  declare  LastName varchar 50  set  LastName     select  LastName  from  ga   tb BLSec LoginsForUsers  where  LoginName     LoginName  declare  SchemaName sysname  set  SchemaName    data value    EVENT INSTANCE SchemaName  1     sysname    --declare  Description xml  --set  Description    data query    EVENT INSTANCE TSQLCommand text          --print  VERSION IS      Version --print  newDataTxt --print cast  data as varchar max     -- select column name from information schema columns where table name   tb DataMeta ObjChangeLog  insert into  ga   tb DataMeta ObjChangeLog     DatabaseName     SchemaName    DbVersion     DbType    EventType    ObjectName    ObjectType     Version    SqlCommand     LoginName     FirstName    LastName      values    DbName   SchemaName   DbVersion   DbType   EventType    ObjectName    ObjectType     Version   newDataTxt    LoginName     FirstName     LastName   GO SET ANSI NULLS OFF GO SET QUOTED IDENTIFIER OFF GO DISABLE TRIGGER  trigMetaDoc TraceDbChanges  ON DATABASE GO         Object   DdlTrigger  trigMetaDoc TraceDbChanges     Script Date  04 22 2009 13 21 29         Enable Trigger  trigMetaDoc TraceDbChanges  ON Database GO

User · Answer

Yes  we source control our sql scripts too with subversion  It s a good practice and you can recreate the schema with default data whenever needed

User · Answer

The new Database projects in Visual Studio provide source control and change scripts.

They have a nice tool that compares databases and can generate a script that converts the schema of one into the other, or updates the data in one to match the other.

The db schema is "shredded" to create many, many small .sql files, one per DDL command that describes the DB.

+tom

Additional info 2008-11-30

I have been using it as a developer for the past year and really like it. It makes it easy to compare my dev work to production and generate a script to use for the release. I don't know if it is missing features that DBAs need for "enterprise-type" projects.

Because the schema is "shredded" into sql files the source control works fine.

One gotcha is that you need to have a different mindset when you use a db project. The tool has a "db project" in VS, which is just the sql, plus an automatically generated local database which has the schema and some other admin data -- but none of your application data, plus your local dev db that you use for app data dev work. You rarely are aware of the automatically generated db, but you have to know its there so you can leave it alone :). This special db is clearly recognizable because it has a Guid in its name,

The VS DB Project does a nice job of integrating db changes that other team members have made into your local project/associated db. but you need to take the extra step to compare the project schema with your local dev db schema and apply the mods. It makes sense, but it seems awkward at first.

DB Projects are a very powerful tool. They not only generate scripts but can apply them immediately. Be sure not to destroy your production db with it. ;)

I really like the VS DB projects and I expect to use this tool for all my db projects going forward.

+tom

User · Answer

For oracle I use self-written java programm oracle-ddl2svn for auto track changes of oracle DDL scheme in SVN

User · Answer

A big problem  often overlooked  is that for larger web based systems  it is required to have a transitional period or bucket testing approach to making new releases  This makes it essential to have both rollback and a mechanism for supporting both the old and new schema in the same DB  This requires a scaffolding approach  made populist by the Agile DB folks   In this scenario  lack of process in DB source control can be a total disaster  You need old schema scripts  new schema scripts and a set of intermediate scripts  as well as a tidy up  once the system is fully on the new version  or rolled back    Rather than having scripts to recreate schema from scratch  what is required is a state based approach  where you need scripts purely to move the DB into the state you require  both forward and back  from version to version  Your DB becomes a series of state scripts  which can be easily source controlled and tagged along with the rest of the source

User · Answer

There has been a lot of discussion about the database model itself, but we also keep the required data in .SQL files.

For example, in order to be useful your application might need this in the install:

INSERT INTO Currency (CurrencyCode, CurrencyName) 
VALUES ('AUD', 'Australian Dollars');

INSERT INTO Currency (CurrencyCode, CurrencyName) 
VALUES ('USD', 'US Dollars');

We would have a file called currency.sql under subversion. As a manual step in the build process, we compare the previous currency.sql to the latest one and write an upgrade script.

User · Answer

I have used the dbdeploy tool from ThoughtWorks at http   dbdeploy com   It encourages the use of migration scripts  Each release  we consolidated the change scripts into a single file to ease understanding and to allow DBAs to  bless  the changes

User · Answer

While this question has many good answers, most of them don’t include the innovations changes in the market, specifically with commercial tools.

Here is a short list of tools that do database version control, I listed the pros and cons of each (full discloser: I work for DBmaestro)

Red-Gate – has been on the market for many years. It provides version control of database objects using scripts integrated with file-based version control.

DBVS – provides version control of the database objects using scripts integrated with file-based version control.

DBmaestro – Provides an enforcement of the version control processes (check-out / check-in) on the real database objects. So there is no question if the version control repository is in-sync with the database being used by the application.

I would encourage you to read a comprehensive, unbiased review on Database Enforced Change Management solutions by veteran database expert Ben Taylor which he posted on LinkedIn https://www.linkedin.com/pulse/article/20140907002729-287832-solve-database-change-mangement-with-dbmaestro

User · Answer

Requiring the development teams to use an SQL database source control management system isn’t the magic bullet which will prevent issues from happening. On its own, database source control introduces additional overhead as the developers are required to save the changes they’ve made to an object in a separate SQL script, open the source control system client, check in the SQL script file using the client and then apply the changes to the live database.

I can suggest using the SSMS add-in called ApexSQL Source Control. It allows developers to easily map database objects with the source control system via the wizard directly from SSMS. The add-in includes support for TFS, Git, Subversion and other SC systems. It also includes support for source controlling Static data.

After downloading and installing ApexSQL Source Control, simply right-click the database you want to version control and navigate to ApexSQL Source Control sub-menu in SSMS. Click the Link database to source control option, select the source control system and the development model. After that you’ll need to provide the log-in information and the repository string for the source control system you’ve chosen.

You can read this article for more information: http://solutioncenter.apexsql.com/sql-source-control-reduce-database-development-time/

User · Answer

Short version  dump your production database into a git repository for an instant backup solution

User · Answer

I have used RedGate SQL Compare Pro for schema synchronization with script folder  then I commit all my update to version control  It works great

User · Answer

We insist upon change scrips and a master data definition script   These are checked into CVS along with any other source code   The PL SQL  were are an Oracle shop  is also source controlled in CVS   The change scripts are repeatable and can be passed to everyone on the team   Basically  just because it is a database  there is never an excuse not to code it and use a source control system to track the changes

User · Answer

I agree with many of the posting concerning ruby's ActiveRecord migrations - they are an elegant way to manage the database in small incremental files that everyone can share. With that said, I've recently implemented a project using VisualStudio's Database Project, and it's kinda made me a believer. Short story - you create a database project, import all (if any) existing database objects into it (tables/views/triggers/keys/users/etc). That import results in a "Create" script per object. To manage the database you alter the create script and then on deploy VS compares the target database to the state of the database residing in your project and apply the proper alter statements.

It really is a bit of magic and I have to admit, it's one of the better things the VS team has done. I'm really impressed up to this point.

Of course, you can manage the whole database project in the version control system of your choice.

User · Answer

At our business we use database change scripts. When a script is run, it's name is stored in the database and won't run again, unless that row is removed. Scripts are named based on date, time and code branch, so controlled execution is possible.

Lots and lots of testing is done before the scripts are run in the live environment, so "oopsies" only happen, generally speaking, on development databases.

User · Answer

I ve started working on sqlHawk which is aimed at providing  open source  tooling around this problem   It s currently in fairly early stages  but does already support storing and enforcing stored procedures and running scripted updates   I d be grateful for any input from anyone who has the time to look at this tool   Apologies for blatant self promotion  but I hope this is useful to someone

User · Answer

RedGate is great  we generate new snapshots when database changes are made  a tiny binary file  and keep that file in the projects as a resource   Whenever we need to update the database  we use RedGate s toolkit to update the database  as well as being able to create new databases from empty ones   RedGate also makes Data snapshots  while I haven t personally worked with them  they are just as robust

User · Answer

Wow  so many answers  For solid database versioning you need to version control the code that changes your database  Some CMS offer configuration management tools  such as the one in Drupal 8  Here is an overview with practical steps to arrange your workflow and ensure the database configuration is versioned  even in team environments

User · Answer

I ve heard people say you absolutely have to keep your schemas in the database  I m not sure I agree  This really depends on the system you re working with  If your system is relatively small and the data is not terribly important  And the the speed at which you need to bring another development environment online is crucial   then yes   you can benefit from it  However when your schema is useless without the data and the database is extremely large  it becomes virtually impossible to  source control  your database  Sure  you can still keep your DDL code in source control but that s essentially useless  You can t get the data needed without backup restore   In larger database development efforts  I ve found backup and restore as the preferred rollback option  Sure you can keep procs  views  functions etc in source control but keeping table sql is not necessary  Also  if your deployment process is airtight  you ll most likely never have to  rollback  your production environment

User · Answer

YES  I think it is important to version your database  Not the data  but the schema for certain    In Ruby On Rails  this is handled by the framework with  migrations   Any time you alter the db  you make a script that applies the changes and check it into source control    My shop liked that idea so much that we added the functionality to our Java-based build using shell scripts and Ant  We integrated the process into our deployment routine   It would be fairly easy to write scripts to do the same thing in other frameworks that don t support DB versioning out-of-the-box

User · Answer

We have a weekly sql dump into a subversion repo  It s fully automated but it s a REALLY beefy task    You ll want to limit the number of revisions because it really chows disk space after a while

User · Answer

Sadly  I ve seen more than one team developing PL SQL programs  stored procedures in Oracle  - sometimes ten thousands LOC - just by editing the code in TOAD  a database tool   without even saving the source to files  except for deployment   Even if the database is backuped regulary  wouldn t take that for granted  though   the only way to retrieve an old version of a stored procedure is to restore the whole database  which is many GB large  And of course sometimes concurrent changes in one file lead to loss of work  when more than one developer works on the same project

User · Answer

If your Database is SQL Server  we might have just the solution you re looking for  SQL Source Control 1 0 has now been released   http   www red-gate com products SQL Source Control index htm  This integrates into SSMS and provides the glue between your database objects and your VCS  The  scripting out  happens transparently  it uses the SQL Compare engine under the hood   which should make it so straightforward to use that developers won t be discouraged from adopting the process    An alternative Visual Studio solution is ReadyRoll  which is implemented as a sub-type of the SSDT Database Project  This takes a migrations-driven approach  which is more suited to the automation requirements of DevOps teams

User · Answer

Yes  we do it by keeping our SQL as part of our build -- we keep DROP sql  CREATE sql  USERS sql  VALUES sql and version control these  so we can revert back to any tagged version   We also have ant tasks which can recreate the db whenever needed   Plus  the SQL is then tagged along with your source code that goes with it

User · Answer

The most successful scheme I ve ever used on a project has combined backups and differential SQL files   Basically we would take a backup of our db after every release and do an SQL dump so that we could create a blank schema from scratch if we needed to as well   Then anytime you needed to make a change to the DB you would add an alter scrip to the sql directory under version control   We would always prefix a sequence number or date to the file name so the first change would be something like 01 add created on column sql  and the next script would be 02 added customers index   Our CI machine would check for these and run them sequentially on a fresh copy of the db that had been restored from the backup   We also had some scripts in place that devs could use to re-initialize their local db to the current version with a single command

User · Answer

You should never just log in and start entering "ALTER TABLE" commands to change a production database. The project I'm on has database on every customer site, and so every change to the database is made in two places, a dump file that is used to create a new database on a new customer site, and an update file that is run on every update which checks your current database version number against the highest number in the file, and updates your database in place. So for instance, the last couple of updates:

if [ $VERSION \< '8.0.108' ] ; then
  psql -U cosuser $dbName << EOF8.0.108
    BEGIN TRANSACTION;
    --
    -- Remove foreign key that shouldn't have been there.
    -- PCR:35665
    --
    ALTER TABLE     migratorjobitems
    DROP CONSTRAINT migratorjobitems_destcmaid_fkey;
    -- 
    -- Increment the version
    UPDATE          sys_info
    SET             value = '8.0.108'
    WHERE           key = 'DB VERSION';
    END TRANSACTION;
EOF8.0.108
fi

if [ $VERSION \< '8.0.109' ] ; then
  psql -U cosuser $dbName << EOF8.0.109
    BEGIN TRANSACTION;
    --
    -- I missed a couple of cases when I changed the legacy playlist
    -- from reporting showplaylistidnum to playlistidnum
    --
    ALTER TABLE     featureidrequestkdcs
    DROP CONSTRAINT featureidrequestkdcs_cosfeatureid_fkey;
    ALTER TABLE     featureidrequestkdcs
    ADD CONSTRAINT  featureidrequestkdcs_cosfeatureid_fkey
    FOREIGN KEY     (cosfeatureid)
    REFERENCES      playlist(playlistidnum)
    ON DELETE       CASCADE;
    --
    ALTER TABLE     ticket_system_ids
    DROP CONSTRAINT ticket_system_ids_showplaylistidnum_fkey;
    ALTER TABLE     ticket_system_ids
    RENAME          showplaylistidnum
    TO              playlistidnum;
    ALTER TABLE     ticket_system_ids
    ADD CONSTRAINT  ticket_system_ids_playlistidnum_fkey
    FOREIGN KEY     (playlistidnum)
    REFERENCES      playlist(playlistidnum)
    ON DELETE       CASCADE;
    -- 
    -- Increment the version
    UPDATE          sys_info
    SET             value = '8.0.109'
    WHERE           key = 'DB VERSION';
    END TRANSACTION;
EOF8.0.109
fi

I'm sure there is a better way to do this, but it's worked for me so far.

User · Answer

I do by saving create update scripts and a script that generates sampledata

User · Answer

Any database interface code absolutely should go into version control (Stored Procedures, Functions, etc).

For structure and data, it is a judgement call. I personally keep a clean structural template of my databases around, but don't store them in version control, due to the size. But storing it in version control can be very beneficial, even for just having a history.

User · Answer

There has been a lot of discussion about the database model itself, but we also keep the required data in .SQL files.

For example, in order to be useful your application might need this in the install:

INSERT INTO Currency (CurrencyCode, CurrencyName) 
VALUES ('AUD', 'Australian Dollars');

INSERT INTO Currency (CurrencyCode, CurrencyName) 
VALUES ('USD', 'US Dollars');

We would have a file called currency.sql under subversion. As a manual step in the build process, we compare the previous currency.sql to the latest one and write an upgrade script.

User · Answer

A big problem  often overlooked  is that for larger web based systems  it is required to have a transitional period or bucket testing approach to making new releases  This makes it essential to have both rollback and a mechanism for supporting both the old and new schema in the same DB  This requires a scaffolding approach  made populist by the Agile DB folks   In this scenario  lack of process in DB source control can be a total disaster  You need old schema scripts  new schema scripts and a set of intermediate scripts  as well as a tidy up  once the system is fully on the new version  or rolled back    Rather than having scripts to recreate schema from scratch  what is required is a state based approach  where you need scripts purely to move the DB into the state you require  both forward and back  from version to version  Your DB becomes a series of state scripts  which can be easily source controlled and tagged along with the rest of the source

User · Answer

Yes  always  You should be able to recreate your production database structure with a useful set of sample data whenever needed  If you don t  over time minor changes to keep things running get forgotten then one day you get bitten  big time  Its insurance that you might not think you need but the day you do it it worth the price 10 times over

User · Answer

We re in the process of moving all the databases to source control  We re using sqlcompare to script out the database  a profession edition feature  unfortunately  and putting that result into SVN   The success of your implementation will depend a lot on the culture and practices of your organization  People here believe in creating a database per application  There is a common set of databases that are used by most applications as well causing a lot of interdatabase dependencies  some of them are circular   Putting the database schemas into source control has been notoriously difficult because of the interdatabase dependencies that our systems have   Best of luck to you  the sooner you try it out the sooner you ll have your issues sorted out

User · Answer

The new Database projects in Visual Studio provide source control and change scripts.

They have a nice tool that compares databases and can generate a script that converts the schema of one into the other, or updates the data in one to match the other.

The db schema is "shredded" to create many, many small .sql files, one per DDL command that describes the DB.

+tom

Additional info 2008-11-30

I have been using it as a developer for the past year and really like it. It makes it easy to compare my dev work to production and generate a script to use for the release. I don't know if it is missing features that DBAs need for "enterprise-type" projects.

Because the schema is "shredded" into sql files the source control works fine.

One gotcha is that you need to have a different mindset when you use a db project. The tool has a "db project" in VS, which is just the sql, plus an automatically generated local database which has the schema and some other admin data -- but none of your application data, plus your local dev db that you use for app data dev work. You rarely are aware of the automatically generated db, but you have to know its there so you can leave it alone :). This special db is clearly recognizable because it has a Guid in its name,

The VS DB Project does a nice job of integrating db changes that other team members have made into your local project/associated db. but you need to take the extra step to compare the project schema with your local dev db schema and apply the mods. It makes sense, but it seems awkward at first.

DB Projects are a very powerful tool. They not only generate scripts but can apply them immediately. Be sure not to destroy your production db with it. ;)

I really like the VS DB projects and I expect to use this tool for all my db projects going forward.

+tom

User · Answer

Check out LiquiBase for managing database changes using source control

User · Answer

As a rule  we keep all of our object code  stored procedures  views  triggers  functions  etc   in source control because these objects are code  and as about every other answer here agrees  code belongs in some form of version control system   As for CREATE  DROP  ALTER statements  etc   DDL   we developed and use BuildMaster to manage the deployment of these scripts such that they can be run once and only once against a target database  whether they fail or not   The general idea is that developers will upload change scripts into the system and when it comes time for deployment  only the change scripts that haven t been run against the target environment s database will be run  this is managed very similarly to Autocracy s answer   The reason for this separation of script types lies in that once you manipulate a table s structure  add an index  etc   you effectively cannot undo that without writing a brand new script  or restoring the database - as opposed to the object code where you can simply drop a view or stored procedure then recreate it   Some of the benefits can be seen when  for example  you restore your production database into your integration environment  the system automatically knows exactly which scripts haven t been run and will alter the table structure of that newly restored database to be current with regards to development

User · Answer

RedGate software makes some great tools that will help you version your database   Be sure to try to have your devs build their own isolated local databases for dev work rather than rely on a  dev server  which may or may not be down at some time

User · Answer

One of Kira s prime use cases is database upgrades by explicitly specify the schema outside the database as code  It then can manage the database and upgrade it to any version from any version

User · Answer

Your project team can have a DBA to whom every developer would forward their create alter  delete  insert update  for master data  sql statements  DBAs would run those queries and on successfully making the required update would add those statements to a text file or a spreadsheet  Each addition can be labeled as a savepoint  Incase you revert back to a particular savepoint  just do a drop all and run the queries uptil the labelled savepoint  This approach is just a thought    a bit of fine tuning here would work for your development environment

User · Answer

I use SchemaBank to version control all my database schema changes:

from day 1, I import my db schema dump into it
i started to change my schema design using a web browser (because they are SaaS / cloud-based)
when i want to update my db server, i generate the change (SQL) script from it and apply to the db. In Schemabank, they mandate me to commit my work as a version before I can generate an update script. I like this kind of practice so that I can always trace back when I need to.

Our team rule is NEVER touch the db server directly without storing the design work first. But it happens, somebody might be tempted to break the rule, in sake of convenient. We would import the schema dump again into schemabank and let it do the diff and bash someone if a discrepancy is found. Although we could generate the alter scripts from it to make our db and schema design in sync, we just hate that.

By the way, they also let us create branches within the version control tree so that I can maintain one for staging and one for production. And one for coding sandbox.

A pretty neat web-based schema design tool with version control n change management.

User · Answer

We version and source control everything surrounding our databases:

DDL (create and alters)
DML (reference data, codes, etc.)
Data Model changes (using ERwin or ER/Studio)
Database configuration changes (permissions, security objects, general config changes)

We do all this with automated jobs using Change Manager and some custom scripts. We have Change Manager monitoring these changes and notifying when they are done.

User · Answer

We have our Create Alter scripts under source control  As for the database itself  when you have hundreds of tables and a lot of processing data every minutes  it would be CPU and HDD killer to version all the database  That s why backup is still  according to me  the best way to control your data

User · Answer

I absolutely love Rails ActiveRecord migrations. It abstracts the DML to ruby script which can then be easily version'd in your source repository.

However, with a bit of work, you could do the same thing. Any DDL changes (ALTER TABLE, etc.) can be stored in text files. Keep a numbering system (or a date stamp) for the file names, and apply them in sequence.

Rails also has a 'version' table in the DB that keeps track of the last applied migration. You can do the same easily.

User · Answer

We do source control all our dabase created objects  And just to keep developers honest  because you can create objects without them being in Source Control   our dbas periodically look for anything not in source control and if they find anything  they drop it without asking if it is ok

User · Answer

I typically build an SQL script for every change I make, and another to revert those changes, and keep those scripts under version control.

Then we have a means to create a new up-to-date database on demand, and can easily move between revisions. Every time we do a release, we lump the scripts together (takes a bit of manual work, but it's rarely actually hard) so we also have a set of scripts that can convert between versions.

Yes, before you say it, this is very similar to the stuff Rails and others do, but it seems to work pretty well, so I have no problems admitting that I shamelessly lifted the idea :)

User · Answer

Yes     our databases are designed in ERwin and the DDLs for each version are automatically generated   The ERwin files are kept in our source code control system  actually  so are our engineering documents

User · Answer

Yes   Code is code   My rule of thumb is that I need to be able to build and deploy the application from scratch  without looking at a development or production machine

User · Answer

I source control the database schema by scripting out all objects  table definitions  indexes  stored procedures  etc     But  as for the data itself  simply rely on regular backups   This ensures that all structural changes are captured with proper revision history  but doesn t burden the database each time data changes

User · Answer

Must read Get your database under version control  Check the series of posts by K  Scott Allen      When it comes to version control  the database is often a second or even third-class citizen  From what I ve seen  teams that would never think of writing code without version control in a million years-- and rightly so-- can somehow be completely oblivious to the need for version control around the critical databases their applications rely on  I don t know how you can call yourself a software engineer and maintain a straight face when your database isn t under exactly the same rigorous level of source control as the rest of your code  Don t let this happen to you  Get your database under version control

User · Answer

The databases themselves  No  The scripts that create them  including static data inserts  stored procedures and the like  of course  They re text files  they are included in the project and are checked in and out like everything else   Of course in an ideal world your database management tool would do this  but you just have to be disciplined about it

[database] Do you use source control for your database items?

The answer is

Examples related to database

Examples related to version-control

Tags