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



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?


Solution:1

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'<EVENT_INSTANCE><EventType>DROP_TABLE</EventType><PostTime>2009-04-22T11:03:11.880</PostTime><SPID>57</SPID><ServerName>YSG</ServerName><LoginName>ysg\yordgeor</LoginName><UserName>dbo</UserName><DatabaseName>DBGA_DEV</DatabaseName><SchemaName>en</SchemaName><ObjectName>tb_BL_Products</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/><CommandText>drop TABLE [en].[tb_BL_Products] --<Version> some</Version>&#x0D;  </CommandText></TSQLCommand></EVENT_INSTANCE>', 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'<EVENT_INSTANCE><EventType>CREATE_TABLE</EventType><PostTime>2009-04-22T11:03:18.620</PostTime><SPID>57</SPID><ServerName>YSG</ServerName><LoginName>ysg\yordgeor</LoginName><UserName>dbo</UserName><DatabaseName>DBGA_DEV</DatabaseName><SchemaName>en</SchemaName><ObjectName>tb_BL_Products</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/><CommandText>CREATE TABLE [en].[tb_BL_Products](&#x0D;      [ProducId] [int] NULL,&#x0D;      [ProductName] [nchar](10) NULL,&#x0D;      [ProductDescription] [varchar](5000) NULL&#x0D;  ) ON [PRIMARY]&#x0D;  /*&#x0D;  <Version> 2.2.2 </Version>&#x0D;  &#x0D;  */&#x0D;  </CommandText></TSQLCommand></EVENT_INSTANCE>', 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'<EVENT_INSTANCE><EventType>DROP_TABLE</EventType><PostTime>2009-04-22T11:25:12.620</PostTime><SPID>57</SPID><ServerName>YSG</ServerName><LoginName>ysg\yordgeor</LoginName><UserName>dbo</UserName><DatabaseName>DBGA_DEV</DatabaseName><SchemaName>en</SchemaName><ObjectName>tb_BL_Products</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/><CommandText>drop TABLE [en].[tb_BL_Products] &#x0D;  </CommandText></TSQLCommand></EVENT_INSTANCE>', 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'<EVENT_INSTANCE><EventType>CREATE_TABLE</EventType><PostTime>2009-04-22T11:25:19.053</PostTime><SPID>57</SPID><ServerName>YSG</ServerName><LoginName>ysg\yordgeor</LoginName><UserName>dbo</UserName><DatabaseName>DBGA_DEV</DatabaseName><SchemaName>en</SchemaName><ObjectName>tb_BL_Products</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/><CommandText>CREATE TABLE [en].[tb_BL_Products](&#x0D;      [ProducId] [int] NULL,&#x0D;      [ProductName] [nchar](10) NULL,&#x0D;      [ProductDescription] [varchar](5000) NULL&#x0D;  ) ON [PRIMARY]&#x0D;  /*&#x0D;  <Version> 2.2.2 </Version>&#x0D;  &#x0D;  */&#x0D;  </CommandText></TSQLCommand></EVENT_INSTANCE>', 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 <> '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 <> 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 <> 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 > '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= '&lt;'  declare @closetag varchar(4)   set @closetag= '&gt;'  declare @newDataTxt varchar(max)   set @newDataTxt= cast(@data as varchar(max))  set @newDataTxt = REPLACE ( REPLACE(@newDataTxt , @opentag , '<') , @closetag , '>')  -- 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  


Solution:2

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


Solution:3

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).


Solution:4

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?


Solution:5

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.


Solution:6

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


Solution:7

My team versions our database schema as C# classes with the rest of our code. We have a homegrown C# program (<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.


Solution:8

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.


Solution:9

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.


Solution:10

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


Solution:11

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.


Solution:12

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!


Solution:13

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!


Solution:14

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!


Solution:15

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:


Solution:16

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.


Solution:17

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.


Solution:18

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.


Solution:19

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.


Solution:20

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.


Solution:21

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.


Solution:22

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.


Solution:23

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.


Solution:24

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.


Solution:25

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.


Solution:26

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


Solution:27

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.


Solution:28

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »