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