Copy Dynamic List of Tables to Another Server

Question

Copy Dynamic List of Tables to Another Server

homebrew01

SSC Guru

Points: 55548
More actions
December 6, 2012 at 9:01 am

#285960

I have a need to copy all tables created in the last 60 days from ServerA.DatabaseA to ServerB.DatabaseA (cloud server). I also need to delete all the tables on Server B older than 60 days.
The cloud server is one the domain, but I cannot get a linked server to work. Maybe this is better suited to SSIS anyway, but I'm a SSIS "newbie" so not quite sure how to go about it.
If it was on the same server, I think I could do it with T-SQL code by looking up the create date for tables.
Any ideas of a good way to do this ?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

DDL structure and the data both, or just the DDL?

hierarchy of the tables due to FK's need to be in the mix there somewhere as well, if we are Creating and Dropping tables.

i need to grab a could server to test this stuff, i know i could do it via TSQL, but there might be nuances for connecting to a cloud server are new to me.

how often is this going to be performed?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

ok this seems to have everything for creating a linked server to an Azure Cloud; what kind of cloud server are you using?

http://blogs.msdn.com/b/sqlcat/archive/2011/03/08/linked-servers-to-sql-azure.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

We have an account with ATT so it's at their location somewhere and we pay by storage & bandwidth used. I don't know a lot about it. Not sure if it's technically a "cloud" server. However it is connected to our domain, so my local Windows account has full permission.

I need to drop the tables at the destination server, then recreate new tables based on create date from the source server and populate the data (source to destination). These are simple temp tables with no FKs or other complications. I want it to happen once per day.