What is the best way to copy the tables of a database...

Question

Post reply

What is the best way to copy the tables of a database...

Ken Tot

Old Hand

Points: 375
More actions
May 17, 2015 at 8:33 pm

#318230

that will keep the indexes,constraints etc.? I'll be using SQL 2008 or Visual Studio 2008. The basic SSIS package will not work since our databases are being updated from time to time. (e.g. new columns/tables meaning we need to redo the SSIS package everytime there are changes). Other techniques are highly recommended.

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: 1000539 More actions · Answer 1

How often do you want to synchronize the DDL? Are you also looking to synchronize the data? If so, how often?

Can the changes be "destructive" as in total replacement of each table or not?

--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)
Intro to Tally Tables and Functions

Ken Tot Old Hand Points: 375 More actions · Answer 2

Jeff Moden (5/17/2015)

How often do you want to synchronize the DDL? Are you also looking to synchronize the data? If so, how often?

Can the changes be "destructive" as in total replacement of each table or not?

Hello Jeff,

Please refer here.

http://qa.sqlservercentral.com/Forums/Topic1684972-391-1.aspx

We've changed our mind already. We will do it SQL 2008 to 2008. We need to sync/mirror/replicate or whatever you call it every night. The DB1 (source) updates everyday (working hrs. 8am-5pm). I'm working in the bank industry.

Thanks,

Mr. Tapon

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

From what you're describing here, just a backup and restore will work. But if you're only looking to move the structures and the DDL for the database, you might consider getting a tool such as SQL Compare[/url] (disclosure, I work for the company).

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I agree with Grant. You should be doing nightly backups anyway. Just do a restore of one system's backup to the other system.

--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)
Intro to Tally Tables and Functions