September 28, 2004 at 8:40 am
I am new to SQL overall and I don't know how to write a script to take a table from one server and move it to another for safe keeping incase I need to move it back.
I have figured how to back it up to another table name to be safe like this:
SELECT * INTO aaa_curtisbackup_tablename FROM tablename
(backup table is the 'aaa_curtisbackup_tablename') (live table is 'tablename')
This allows a duplicate table on the same database, same server.
HOW CAN I DO A SIMILAR TSQL COMMAND BUT MOVE IT TO ANOTHER SERVER AND DATABASE???
September 28, 2004 at 9:04 am
I think the best way to do this is to create a DTS job. This will allow you to run the package everyday or as frequent as you might need and the data will be always in synch with the live table. To create the DTS from Enterprise Manager, just go to the database where you have the table, expand the database, right click on Tables, select All tasks, choose Export and follow the DTS wizard. Make sure to select the option Delete when mapping the table to get rid of all previous records otherwise SQL append records by default (See BOL for more details) Good luck!!
September 28, 2004 at 12:19 pm
Thank you, but where do I check or uncheck the option for SQL to append in DTS - I did this and saved the job in DTS, but never saw that option you mentioned, thanks again!!!
September 28, 2004 at 4:00 pm
In the wizard, you'll see the options for appending, deleting, or creating the destination table on the "Column Mappings and Transformations" screen. You get there by clicking on the elipse (...) in the "Transform" column of the screen where you choose which tables to transfer. When you save the package, the delete will be in a Execute SQL task.
Greg
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply