Re: Sync production db with test
- From: samalex <samalex@xxxxxxxxx>
- Date: Thu, 18 Jun 2009 11:21:32 -0500
On Thu, 18 Jun 2009 10:26:02 -0500, Carl Ganz <seton.software@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
.
- Follow-Ups:
- Re: Sync production db with test
- From: Carl Ganz
- Re: Sync production db with test
- References:
- Sync production db with test
- From: SetonSoftware
- Re: Sync production db with test
- From: samalex
- Re: Sync production db with test
- From: Carl Ganz
- Re: Sync production db with test
- From: samalex
- Re: Sync production db with test
- From: Carl Ganz
- Sync production db with test
- Prev by Date: Re: Deleting all logs except first for a given day??
- Next by Date: RE: Deleting all logs except first for a given day??
- Previous by thread: Re: Sync production db with test
- Next by thread: Re: Sync production db with test
- Index(es):
Relevant Pages
|
