Migrating databases from development to production

If you work for a small company, then your development, test and production databases might reside on the same server. However, most organizations realize the risks associated with such a setup. If your server were to become unusable due to a disk failure or power outage, you'd lose not only the business from your current customers, but also the valuable development time. If you use a production database for development and developers make a mistake, your online customers would have to wait until your production database was restored from a backup. The bottom line is that no serious development should be done on the production server. The application should be developed on one server, tested in a production-like environment on another server (sometimes referred to as Quality Assurance server), and then deployed to the production server.

The initial migration of the database is easy; you can just backup the database on the development server and restore it on the production server. But what happens if you have subsequent releases or enhancements to the already deployed application? Well, backup and restore is no longer an option--you'd lose all the data collected in production. DTS could be helpful only if your database structure is the same on all servers. In the majority of cases your database structure will change from one release to the next; therefore, you need some type of a mechanism to compare databases on two servers and come up with a plan to synchronize them. In this article, I offer you a script that compares tables, indexes and constraints on two servers and generates a comprehensive report of differences.

As you might guess, the trick is in examining the system tables and/or the information schema views. The script itself isn't terribly complicated, but if your situation calls for comparing database structures, it will give you a good head start for developing your own solution. Once you know the differences between the database structures, you can come up with scripts to make appropriate changes to the production database and transfer/transform additional data from the development environment.

The following script compares development and production databases on my test server. Replace the database names if you have to compare two databases on the same server. If you have separate servers, you'll have to set up the remote server as a linked server and modify this script to query the remote database. See inline comments for details.

SET NOCOUNT ON 
DECLARE  @table_name VARCHAR(100), 
  @index_name VARCHAR(100), 
  @sql VARCHAR(4000)

-- temp tables to keep intermediate values:
CREATE TABLE #dev_columns (
table_name VARCHAR(100) NULL,
column_id INT NULL, 
column_name VARCHAR(100) NULL, 
data_type VARCHAR(100) NULL, 
length INT, 
nullable BIT)

CREATE TABLE #production_columns (
table_name VARCHAR(100) NULL,
column_id INT NULL, 
column_name VARCHAR(100) NULL, 
data_type VARCHAR(100) NULL, 
length INT, 
nullable BIT)

CREATE TABLE #dev_indexes (
 table_name VARCHAR(100), 
 index_name VARCHAR(100), 
 column_name VARCHAR(100), 
 ordinal_position_of_column INT, 
 isclustered BIT)

CREATE TABLE #production_indexes (
 table_name VARCHAR(100), 
 index_name VARCHAR(100), 
 column_name VARCHAR(100), 
 ordinal_position_of_column INT, 
 isclustered BIT)

CREATE TABLE #dev_constraints (
 table_name VARCHAR(100), 
 constraint_type VARCHAR(25), 
 constraint_name VARCHAR(100), 
 column_name VARCHAR(100), 
 constraint_value VARCHAR(255))

CREATE TABLE #production_constraints (
 table_name VARCHAR(100), 
 constraint_type VARCHAR(25), 
 constraint_name VARCHAR(100), 
 column_name VARCHAR(100), 
 constraint_value VARCHAR(255))

-- get all columns, indexes, and constraints from first db:
USE Development
DECLARE table_cursor CURSOR FOR 
SELECT name 
FROM Development.dbo.sysobjects 
WHERE type = 'u'
AND name <> 'dtproperties'
ORDER BY 1

OPEN table_cursor 
FETCH NEXT FROM table_cursor 
INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
     SELECT @sql = 'INSERT #dev_columns (
  table_name ,
  column_id , 
  column_name , 
  data_type , 
  length , 
  nullable ) 

 SELECT  ''' + @table_name + ''' ,
  colid, 
  a.name, 
  b.name, 
  a.length, 
  a.isnullable
  FROM syscolumns a 
                INNER JOIN systypes b 
                ON a.xtype = b.xtype 
  and a.xusertype = b.xusertype
  WHERE 
            a.id = OBJECT_ID(''' + @table_name + ''')'

 EXEC(@sql)

 SELECT @sql = ' '

 SELECT @sql = 'INSERT #dev_indexes 
  SELECT DISTINCT b.name AS table_name, 
  a.name AS index_name, 
  d.name AS column_name,
  c.keyno AS ordinal_position_of_column,
  ''CLUSTERED'' = CASE 
          WHEN a.indid = 1 THEN 1 
          ELSE 0
       END 
  FROM sysindexes a INNER JOIN sysobjects b 
                                  ON a.id = b.id
  AND b.type = ''u'' 
                and b.name <> ''dtproperties''
  INNER JOIN sysindexkeys c 
                ON a.indid = c.indid AND c.id = b.id
  INNER JOIN syscolumns d 
                ON d.colid = c.colid 
                AND d.id = b.id
  WHERE a.indid <> 0 
                AND a.indid <> 255 
                AND a.name NOT LIKE ''_wa%''
  AND b.name = ''' + @table_name + '''
  ORDER BY 1, 2, 4'
 EXEC(@sql)

 SELECT @sql = ' '

 SELECT @sql = 'INSERT #dev_constraints
  SELECT  b.name AS table_name, 
  constraint_type =  
                        CASE 
   WHEN a.type = ''c'' 
                        THEN ''CHECK''
   WHEN a.type = ''d'' 
                        THEN ''DEFAULT''
   END,
       a.name AS constraint_name, 
       COL_NAME(b.id, a.info) 
                     AS column_name, 
       c.text AS constraint_value
         FROM sysobjects a 
                INNER JOIN sysobjects b 
  ON b.id = a.parent_obj 
  LEFT JOIN syscomments c 
                ON c.id = a.id
  WHERE a.type IN (''c'', ''d'')
  AND b.name = ''' + @table_name +''''

 EXEC(@sql)

 FETCH NEXT FROM table_cursor 
        INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor

-- get all columns, indexes, and constraints from second db:
USE Production
/*******************************************
* if using a remote server then remove 
* "USE production" statement and use 
* four-part identifier for querying 
* the tables, as in following
* 
* SELECT name 
* FROM 
* linked_server.remote_database.dbo.sysobjects 
****************************************
*/
DECLARE table_cursor CURSOR FOR 
SELECT name FROM Production.dbo.sysobjects 
WHERE type = 'u'
AND name <> 'dtproperties'
ORDER BY 1

OPEN table_cursor 
FETCH NEXT FROM table_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @sql = 'INSERT #production_columns (
  table_name ,
  column_id , 
  column_name , 
  data_type , 
  length , 
  nullable ) 

     SELECT  ''' + @table_name + ''' ,
  colid, 
  a.name, 
  b.name, 
  a.length, 
  a.isnullable
            FROM syscolumns a INNER JOIN systypes b 
                ON a.xtype = b.xtype 
  and a.xusertype = b.xusertype
 WHERE a.id = OBJECT_ID(''' + @table_name + ''')'

 EXEC(@sql)

 SELECT @sql = ' '

 SELECT @sql = 'INSERT #production_indexes 
  SELECT 
                DISTINCT 
                 b.name AS table_name, 
   a.name AS index_name, 
   d.name AS column_name,
   c.keyno AS ordinal_position_of_column,
   'CLUSTERED' =  
                        CASE 
         WHEN a.indid = 1 THEN 1 
         ELSE 0
      END 
  FROM sysindexes a 
                INNER JOIN sysobjects b 
                ON a.id = b.id
  AND b.type = ''u''
  INNER JOIN sysindexkeys c 
                ON a.indid = c.indid AND c.id = b.id
  INNER JOIN syscolumns d 
                ON d.colid = c.colid AND d.id = b.id
  WHERE a.indid <> 0 
                AND a.indid <> 255 
                AND a.name NOT LIKE ''_wa%''
  AND b.name = ''' + @table_name + '''
  ORDER BY 1, 2, 4'

 EXEC(@sql)

SELECT @sql = ' '

SELECT @sql = 'INSERT #production_constraints
 SELECT  b.name AS table_name, 
  constraint_type =  
                        CASE 
   WHEN a.type = ''c'' 
                        THEN ''CHECK''
   WHEN a.type = ''d'' 
                        THEN ''DEFAULT''
   END,
  a.name AS constraint_name, 
  COL_NAME(b.id, a.info) 
                AS column_name, 
  c.text AS constraint_value
 FROM sysobjects a INNER JOIN sysobjects b 
 ON b.id = a.parent_obj 
 LEFT JOIN syscomments c ON c.id = a.id
 WHERE a.type IN (''c'', ''d'')
 AND 
        b.name = ''' + @table_name +''''

EXEC(@sql)

FETCH NEXT FROM table_cursor INTO @table_name

END
CLOSE table_cursor
DEALLOCATE table_cursor

-- now compare the values:
SELECT 'the following tables are in development, but not in production database:'
SELECT DISTINCT table_name FROM #dev_columns 
    WHERE table_name NOT IN 
    (SELECT table_name FROM #production_columns)

SELECT 'the following tables are in production, but not in development database:'
SELECT DISTINCT table_name 
FROM #production_columns 
WHERE table_name 
NOT IN (SELECT table_name FROM #dev_columns)

SELECT 'the following columns are in development, but not in production database:'
SELECT a.* FROM #dev_columns a 
WHERE a.column_name 
NOT IN (SELECT column_name 
     FROM #production_columns b 
     WHERE a.table_name = b.table_name)

SELECT 'the following columns are in production, but not in development database:'
SELECT a.* FROM #production_columns a 
WHERE a.column_name NOT IN 
 (SELECT column_name FROM #dev_columns b 
  WHERE a.table_name = b.table_name)

SELECT 'the following columns have varying data types, length or nullability:'
SELECT  
DISTINCT 
a.table_name,
 a.column_name,
 a.data_type AS dev_dt, 
 a.length AS dev_length, 
 a.nullable AS dev_nullability,
 b.data_type AS production_dt, 
 b.length AS production_length, 
 b.nullable AS production_nullability
FROM #dev_columns a 
INNER JOIN #production_columns b ON 
a.column_name = b.column_name
AND
a.table_name = b.table_name
AND
(a.data_type <> b.data_type
OR
a.length <> b.length
OR 
a.nullable <> b.nullable)
ORDER BY 1

DROP TABLE #dev_columns, #production_columns

SELECT 'the following tables have indexes in dev, but not 
production:'
SELECT DISTINCT table_name 
FROM #dev_indexes 
WHERE table_name 
NOT IN (SELECT table_name 
        FROM #production_indexes)

SELECT 'the following tables have indexes in production, but 
not dev:'
SELECT DISTINCT table_name 
FROM #production_indexes 
WHERE table_name 
NOT IN (SELECT table_name FROM #dev_indexes)

SELECT 'the following indexes appear in dev, but not in 
production:'
SELECT * FROM #dev_indexes a 
WHERE index_name NOT IN 
 (SELECT index_name 
          FROM #production_indexes b 
           WHERE a.table_name = b.table_name)

SELECT 'the following indexes appear in production, but not in development:'
SELECT * FROM #production_indexes a 
WHERE index_name NOT IN 
 (SELECT index_name FROM #dev_indexes b 
         WHERE a.table_name =    
         b.table_name)

SELECT 'the ordinal position of columns is different in 
following indexes:'
SELECT  
a.table_name,
a.index_name, 
a.ordinal_position_of_column as 'dev_position', 
b.ordinal_position_of_column as 'production_position'
FROM #dev_indexes a 
INNER JOIN #production_indexes b ON 
a.table_name = b.table_name 
AND 
a.index_name = b.index_name
AND 
a.column_name = b.column_name 
AND 
a.ordinal_position_of_column <> 
b.ordinal_position_of_column

SELECT 'the following indexes are clustered in development 
but not in production, or vice versa:'
SELECT  
a.table_name, 
  a.index_name, 
 a.isclustered AS isclustered_in_dev,  
 b.isclustered AS isclustered_in_production
FROM #dev_indexes a 
INNER JOIN #production_indexes b ON 
a.table_name = b.table_name 
AND 
a.index_name = b.index_name
AND 
a.isclustered <> b.isclustered

DROP TABLE #dev_indexes, #production_indexes

SELECT 'the following constraints appear in development, 
but not in production:'
SELECT * FROM #dev_constraints WHERE constraint_name 
         NOT IN 
 (SELECT DISTINCT constraint_name 
         FROM #production_constraints)

SELECT 'the following constraints appear in production but not in development:'
SELECT * FROM #production_constraints 
         WHERE constraint_name NOT IN 
 (SELECT DISTINCT constraint_name 
         FROM #dev_constraints)

DROP TABLE #dev_constraints, #production_constraints 

Reader Feedback

Mike C. writes: I have found that calling the OBJECT_ID function across linked servers does not work however you attempt to prefix it. But you can amend the two lines of the format:

WHERE a.id = OBJECT_ID(''' + @table_name + ''')'

...to be like:

WHERE a.id = (
    SELECT id
    FROM server2.db_name.dbo.sysobjects
    WHERE name = ''' + @table_name + '''
)'

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.