Migrating databases from development to production
A script that compares tables, indexes and constraints on two servers and generates a comprehensive report of differences.
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.
Start the conversation
0 comments