Re: Sync production db with test

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
.

Relevant Pages

  • run stored proc from excel with a parameter
    ... declare @sql nvarchar ... [Database Name] ... [Custodian Name] ... [Near Dups File Count] ...
    (microsoft.public.excel.programming)
  • Re: CDatabase CRecordset - How to get the number of rows?
    ... What is the value of dataStr in the CRecordset::Open call. ... > // Declare a long to hold the row count for the fast method ... > // Declare a long to hold the row count for the slow method ... >>> I'm now testing my DLL with the real database. ...
    (microsoft.public.vc.mfc)
  • Re: Update databases
    ... Compares if all tables in one database have analog in second ... declare @sqlStr varchar ... exec ('declare @Name sysname select @Name=name from ... -- ##CompareStr - will be used to pass comparing strings into dynamic script ...
    (microsoft.public.sqlserver.programming)
  • Re: great script, but...
    ... DECLARE @x nvarchar; ... I am having 'n' number of tables within a database. ... open boris ... Can't we avoid using cursors? ...
    (microsoft.public.sqlserver.programming)
  • Looking for data dictionary/definition integrity checking utility
    ... This script searches for a value in the database. ... declare @tbl varchar ... -- char and numeric ... open CRR ...
    (microsoft.public.sqlserver.programming)