Copy tables to another server

Question

Post reply

Copy tables to another server

Ahmad Ghanem

Old Hand

Points: 351
More actions
April 16, 2006 at 2:31 pm

#112995

This procedure is useful to put it in scheduled job, then you'll get a fresh data table without making backup and restore.
-------------------------------------------------------------
CREATE PROCEDURE [CopyToNewDatabase]
  @Dname varchar (256),     --The name of source database
  @Sname varchar (256)      --The name of source server
as
  declare
  @Sql   varchar (256),
  @c cursor,
  @Name varchar(256)


  SET @C = CURSOR FAST_FORWARD FOR
  select [name] from sysobjects where xtype = 'u'
OPEN @C
FETCH NEXT FROM @C INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
  IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = 'temp1')
   DROP TABLE temp1
  set @sql = 'select * into temp1'+' '+'from '+@Sname+'.'+@Dname+'.'+'dbo.'+@name
  execute(@sql)
  set @sql = 'truncate table '+ @name
  --print @sql
  execute (@Sql)
  set @sql = 'alter table '+ @name +' '+ 'disable trigger all'
  execute (@sql)
  set @sql = 'insert into '+ @name + ' select * from temp1'
  --print @sql
  execute (@Sql)
  set @sql = 'alter table '+ @name +' '+ 'enable trigger all'
  execute (@Sql)
  --drop table temp1

  print @Name


FETCH NEXT FROM @C INTO @Name
end
--EXEC CopyToNewDatabase '',''

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Nicely done... You should publish this in the "scripts" section of this forum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

Interesting idea. Should work well for small datbases. I'd worry about larger ones.

Ahmad Ghanem Old Hand Points: 351 More actions · Answer 3

Mr Steve

I've tried this script on database with size about 1.5 GB , and it took about 14 minutes.

noeld SSC Guru Points: 96590 More actions · Answer 4

Don't like the idea for several reasons.

Constraints (all kinds) are lost, indexes (all kinds) are lost and the network will be really taxed if the DB grows at "decent" sizes. By the way, a 1.5GB DB is a toy size DB nowdays (no offence)

It is interesting though for small footprint DBs

Cheers,

* Noel