Re: Sync production db with test

On Jun 18, 12:21 pm, samalex <sama...@xxxxxxxxx> wrote:
On Thu, 18 Jun 2009 10:26:02 -0500, Carl Ganz <seton.softw...@xxxxxxxxxxx>  
wrote:





On Jun 18, 9:35 am, samalex <sama...@xxxxxxxxx> wrote:
On Wed, 17 Jun 2009 15:13:49 -0500, Carl Ganz  
<seton.softw...@xxxxxxxxxxx>  
wrote:

On Jun 17, 11:57 am, samalex <sama...@xxxxxxxxx> wrote:
On Wed, 17 Jun 2009 10:22:09 -0500, SetonSoftware  

<seton.softw...@xxxxxxxxxxx> wrote:
I'd like to set up a process so that every week our Production data
copies to our Test database. I only want the data to come over. The
table structures, indices, stored procs, UDFs, etc. currently  
existing
in the Test database should remain as they are.

What is the best/recommended/least painful approach to doing this?

Thanks

Carl

Hi Carl,

I actually have an SSIS package that does this between our Prod and  
Test  
systems, but it only does it for a few tables and not all.  If you  
have  
a  
TON of tables to sync-up than this could take some time to update  
plus  
it  
wouldn't be dynamic, so any schema changes on either table would  
require  
updating the SSIS package.

Another option is to write a procedure to loop through  
information_schema.Tables, for example using this as the base:
SELECT  *
FROM    information_schema.Tables
where   Table_Type = 'BASE TABLE'

and for each record truncate the table in the Test Database and  
Insert  
Into from Prod Database.  The only catcher will be for tables with  
an  
Identity field TSQL will force you to set Identity_Insert to ON and  
also  
have all the column names in the Insert statement.  It wouldn't be  
too  
difficult to piece together a procedure to create this list from  
information_schema.Columns and put it together using dynamic SQL,  
but  
the  
code will be tricky.

Another option is to just do a backup/restore from Production to  
Test,  
but  
this will overwrite any test objects yet to be deployed to Prod.  Is  
 
this  
the reason you're not doing a backup/restore from Prod to Test?

The script I described above is something I've wanted to put  
together  
for  
some time now, so if I have time later this week I'll do so and add  
it  
to  
this thread.

Take care --

Sam

Sam

I figured this would be the needed approach. I've now got the script
to do this largely worked up  - setting SET_IDENTITY, disabling the
constraints, deleting the existing data, and doing the INSERT..SELECT.
It largely seems to be working and I'll post this when I'm done.
Probably tomorrow.

Thanks

Carl

Hi Carl,

This morning I finished-up that script and I figured I'd post it incase  
 
others can use it.  It's pretty basic and it could surely be modified  
to  
add functionality, but all and all as long as the schemas are the same  
between the two databases this should do the trick:

------------[ Start Code ]------------
-- Set Variables
Declare @strTableSchema Nvarchar(4000)
Declare @strTableName Nvarchar(50)
Declare @strSourceDB nvarchar(50)
Declare @strSQL nvarchar(4000)
Declare @bitIdentity bit

-- Set Source Database
Set @strSourceDB = 'SamsTestDB'

-- Get table Listing into Temp Table
select  name,
                (Select count(*)
                 from Information_Schema.Columns
                 where COLUMNPROPERTY(object_id(TABLE_NAME),  
COLUMN_NAME, 'IsIdentity')  
= 1 and
                TABLE_NAME = name ) as BitIdentity
Into    #tmpTables
from    sysobjects where type = 'U'

-- Loop through tables
While (Select count(*) from #tmpTables) > 0
        Begin  
                -- Get first table name
                Select Top 1 @strTableName = name, @bitIdentity =  
BitIdentity from  
#tmpTables

                -- Reset variables
                set @strTableSchema = ''
                Set @strSQL =
                        'Truncate Table #TableName#; #IdentityOn#  
Insert into #TableName#  
(#TableSchema#) Select * from #SourceDB#..#TableName#; #IdentityOff#'

                -- get Table Schema
                select
                        @strTableSchema  =  @strTableSchema + '[' +  
Column_NAME + '],'
                        from Information_Schema.Columns
                        WHERE   TABLE_NAME = @strTableName
                        ORDER BY Ordinal_Position

                -- Drop leading comma from @strTableSchema
                SET @strTableSchema =  
Left(@strTableSchema,Len(@strTableSchema)-1)  
--@TableSchema + '_EOF'

                -- If Identity than account for this in @strTableSchema
                If @bitIdentity = 1
                        Begin
                                set @strSQL =  
replace(@strSQL,'#IdentityOn#','Set Identity_Insert  
#TableName# On;')
                                set @strSQL =  
replace(@strSQL,'#IdentityOff#','Set Identity_Insert  
#TableName# Off;')
                        End
                Else
                        Begin
                                set @strSQL =  
replace(@strSQL,'#IdentityOn#','')
                                set @strSQL =  
replace(@strSQL,'#IdentityOff#','')
                        End

                -- Set other variables
                set @strSQL =  
replace(@strSQL,'#TableName#',@strTableName)
                Set @strSQL =  
replace(@strSQL,'#TableSchema#',@strTableSchema)
                Set @strSQL = replace(@strSQL,'#SourceDB#',@strSourceDB)

                -- Execute string
                exec(@strSQL)

                -- Delete this table from #tmpTables since it's already  
processed
                Delete from #tmpTables where name = @strTableName

        End

        -- Cleanup
        drop table #tmpTables

------------[ End Code ]------------

Set the SourceDB variable to the Production database and run this from  
the  
Test database to copy all data from Prod to Test.  It also takes into  
account tables with Identity fields.

I may further modify this script to exclude tables that don't have the  
same schemas between Source and Destination since we often have new  
tables  
or modified tables in Test that haven't been rolled out to Prod.  Also  
logging could be added to tell the user what tables were updated and  
how  
many rows... but this was quick and dirty :)

Take care --

Sam- Hide quoted text -

- Show quoted text -

Sam

You may have a problem with TRUNCATE as table with referential
integrity constraints can't be truncated. I'm disabling all the
constraints first (then re-enabling them when done). Your code
reminded me that I can use TRUNCATE since I'm doing this so I'll
modify mine.

Thanks

Carl

Hi Carl,

Ahh, we've gotten away from using foreign keys or other referential  
integrity in our current system, so this slipped my mind as being a  
consideration.  Your method of disabling and re-enabling the constraints  
should do the trick though, so awesome suggestion there.

Sam- Hide quoted text -

- Show quoted text -

Sam

Here's the final and fully commented version. I was having problems
executing SET IDENTITY_INSERT ON, the SELECT...INSERT, and SET
IDENTITY_INSERT OFF as separate statements. When I batched them
together in one semi-colon delimited command it works fine.

Thanks

Carl


ALTER PROCEDURE [dbo].[spc_CopyProd2Test]

AS

/*
DROP TABLE SyncLog
SELECT * FROM SyncLog
TRUNCATE TABLE SyncLog

This stored procedure pulls the contents of a database on one server
into the table structures of
a database on another server. You can change the source
server.database by doing a global replace of
this string:

[GBM-TEST].[Contracts]

with the appropriate servername.databasename

The commands it issues for each table are at follows:

ALTER TABLE [dbo].MyTable NOCHECK CONSTRAINT PK_MyConstraint

TRUNCATE TABLE dbo.[MyTable]

SET IDENTITY_INSERT dbo.[MyTable] ON (if applicable)

INSERT INTO dbo.[MyTable] ([MyColumn1],[MyColumn2]) SELECT [MyColumn1],
[MyColumn2] FROM [MyServer].[MyDataBase].dbo.[MyColumn1]

SET IDENTITY_INSERT dbo.[MyTable] OFF (if applicable)

ALTER TABLE [dbo].MyTable CHECK CONSTRAINT PK_MyConstraint

This procedure will remove all constraints, SET IDENTITY_INSERT ON for
all tables which have identity
columns, delete the contents of the table, INSERT..SELECT the data
from the source table to the target,
SET IDENTITY_INSERT OFF as appropriate, and re-enable the constraints.
*/

DECLARE @SQL varchar(max)
DECLARE @SQLTemplate varchar(max)
DECLARE @Columns varchar(max)
DECLARE @SQLError varchar(max)
DECLARE @TableSchemaOrig varchar(100)
DECLARE @TableSchema varchar(100)
DECLARE @TableNameOrig varchar(100)
DECLARE @TableName varchar(100)
DECLARE @ColumnName varchar(100)
DECLARE @Cnt int
DECLARE @x int

--Create a table to store error messages
IF NOT EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id
('SyncLog'))
BEGIN
CREATE TABLE SyncLog
(
ErrorNumber int,
Message varchar(max),
SQL varchar(max),
ErrorDate datetime
)
END

INSERT INTO SyncLog
(ErrorNumber, Message, SQL, ErrorDate)
VALUES
(Null, 'Migration begun', Null, GETDATE())

--This will hold the SQL statements that are created
CREATE TABLE #SQLtemp
(
ID int identity(1,1),
SQL varchar(max)
)

--This will hold the structures of the tables
CREATE TABLE #Tabletemp
(
ID int identity(1,1),
TABLE_SCHEMA varchar(100),
TABLE_NAME varchar(100),
COLUMN_NAME varchar(100)
)

--Create SQL to turn off all the constraints
INSERT INTO #SQLtemp
SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].' + OBJECT_NAME
(parent_object_id) +
' NOCHECK CONSTRAINT ' + OBJECT_NAME(OBJECT_ID)
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

--Create SQL to truncate the tables
INSERT INTO #SQLtemp
SELECT 'TRUNCATE TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'SyncLog'
ORDER BY TABLE_NAME

--Create SQL to insert data for those table and columns which exist in
both the source and target databases
INSERT INTO #Tabletemp
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM [GBM-TEST].[Contracts].INFORMATION_SCHEMA.columns
WHERE TABLE_NAME IN (SELECT TABLE_NAME
FROM [GBM-TEST].
[Contracts].INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE')
AND TABLE_NAME + COLUMN_NAME IN (SELECT TABLE_NAME + COLUMN_NAME
FROM INFORMATION_SCHEMA.columns)
AND TABLE_NAME <> 'SyncLog'
ORDER BY TABLE_NAME, ORDINAL_POSITION

SET @Cnt = @@ROWCOUNT
SET @x = 1
SET @TableSchemaOrig = ''
SET @TableNameOrig = ''
SET @Columns = ''

--This is the SQL template for the INSERT...SELECT. The strings
delimited by percent signs will be
--replaced with actual values
SET @SQLTemplate = 'INSERT INTO %Table% (%Columns%) SELECT %Columns%
FROM [GBM-TEST].[Contracts].%Table%'
SET @SQL = @SQLTemplate

--For each table/column entry in #Tabletemp
WHILE @x <= @Cnt
BEGIN
--Pull the information for a table/column entry
SELECT @TableSchema = TABLE_SCHEMA,
@TableName = TABLE_NAME,
@ColumnName = COLUMN_NAME
FROM #Tabletemp
WHERE ID = @x

IF @TableSchemaOrig = ''
SET @TableSchemaOrig = @TableSchema

IF @TableNameOrig = ''
SET @TableNameOrig = @TableName

--Build the column list for the table
IF @TableNameOrig = @TableName
SET @Columns = @Columns + '[' + @ColumnName + '],'

--When all the table's column have been retrieved create SET
IDENTITY_INSERT ON/OFF
--statements for those that have identity columns
IF @TableNameOrig <> @TableName
BEGIN
--The SET IDENTITY_INSERT <tablename> ON/OFF is appended
to the INSERT..SELECT SQL
--as executng them individually was causing problems.
Therefore, the commands are
--executed as a semi-colon separated batch like this:
--SET IDENTITY_INSERT dbo.[MyTable] ON (if applicable);
--INSERT INTO dbo.[MyTable] ([MyColumn1],[MyColumn2])
SELECT [MyColumn1],[MyColumn2] FROM [MyServer].[MyDataBase].dbo.
[MyColumn1];
--SET IDENTITY_INSERT dbo.[MyTable] OFF (if applicable)
IF EXISTS (select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @TableNameOrig
and COLUMNPROPERTY(object_id(TABLE_NAME),
COLUMN_NAME, 'IsIdentity') = 1)
BEGIN
SET @SQL = 'SET IDENTITY_INSERT ' + @TableSchema + '.
[' + @TableNameOrig + '] ON;' + @SQL
END

--Remove the trailing comma
SET @Columns = SUBSTRING(@Columns, 1, LEN(@Columns) - 1)

--Replace the placeholders with the table name and comma-
delimited field list
SET @SQL = REPLACE(@SQL, '%Table%', @TableSchema + '.[' +
@TableNameOrig + ']')
SET @SQL = REPLACE(@SQL, '%Columns%', @Columns)

IF EXISTS (select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @TableNameOrig
and COLUMNPROPERTY(object_id(TABLE_NAME),
COLUMN_NAME, 'IsIdentity') = 1)
BEGIN
SET @SQL = @SQL + ';SET IDENTITY_INSERT ' +
@TableSchema + '.[' + @TableNameOrig + '] OFF'
END

INSERT INTO #SQLtemp (SQL) VALUES (@SQL)

SET @SQL = @SQLTemplate
SET @Columns = ''
SET @TableNameOrig = @TableName

END
ELSE
SET @x = @x + 1

END

DROP TABLE #Tabletemp

--Create the SQL to re-enable the constraints
INSERT INTO #SQLtemp
SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].' + OBJECT_NAME
(parent_object_id) +
' CHECK CONSTRAINT ' + OBJECT_NAME(OBJECT_ID)
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

--Now that the SQL statements needed for the migration are ready and
in the required order,
--execute each one
SELECT @Cnt = MAX(ID) FROM #SQLtemp

SET @x = 1

WHILE @x <= @Cnt
BEGIN

SELECT @SQL = SQL
FROM #SQLtemp
WHERE ID = @x

BEGIN TRY
EXEC(@SQL)

INSERT INTO SyncLog
(ErrorNumber, Message, SQL, ErrorDate)
VALUES
(Null, 'OK', @SQL, GETDATE())
END TRY
BEGIN CATCH
SET @SQLError = @@ERROR

--If an error was found, write it to the SyncLog table.
--One of the most common errors will be from trying to insert
a value from a larger column
--into that of a smaller column. This will happen if you
reduced the size of a column in
--your target to less than that of your source. In other
cases, the data type may have changed
--and this will throw an error as well.
IF @SQLError <> 0
INSERT INTO SyncLog
(ErrorNumber, Message, SQL, ErrorDate)
VALUES
(@SQLError, Error_Message(), @SQL, GETDATE())
END CATCH

SET @x = @x + 1

END

INSERT INTO SyncLog
(ErrorNumber, Message, SQL, ErrorDate)
VALUES
(Null, 'Migration completed', Null, GETDATE())

--SELECT * FROM #SQLtemp ORDER BY ID

DROP TABLE #SQLtemp
.

Relevant Pages