import database and update data in database daily

Question

Post reply

import database and update data in database daily

Shaun2012

Hall of Fame

Points: 3214
More actions
January 24, 2009 at 8:44 pm

#204531

Hi ,
i want to transfer a database from one server to another server using SQL 2008.
And then i want to write a script which i can update the database daily automatically .
Thanks
Sumit.

Viewing 9 posts - 1 through 8 (of 8 total)

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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

You can use a couple of methods including detach / copy / attach or backup / restore. Both are very effective.

As for the automatic update process, what are you trying to do?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 2

Hi David,

Thanks for the response.

As for the automatic update process, what are you trying to do?

Every Night, the database is updated using archive files.

So i want to write a script which will update the database and create a batch file through which it automatically update the database every early morning.

Thanks

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Hard to say exactly what you would need but it sounds like you could consider either BULK INSERT or SSIS to get the data from file to the database. Then you would execute your insert / update depending on what you would want to do. This is typically done using "staging" tables where you would load data from files, scrub, and then finally execute any other processing that you would need to do against that data.

Hope this helps. If you provide some additional information we might be able to provide you with further details Regardless, looking into BULK INSERT should get you on your way.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 4

Hi david,

i had findout today exactly what we wanted.

we have 2 servers in 2008.

Server 1- Main

Server 2 - Local Server.

So now i want to create a script in which when main server database is udpated then our local server database should be updated as well.

For Ex Server 1 - Datbase: Northwind and 1 table have 500 records and 100 records were added in the database .

So now i want the 100 records to be added in our local Serve as well.

I hope this will help you to sort it .

thanks

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 5

The easiest method to make this consistent would be to utilize replication. If this can be an update that occurs once per day then look at snapshot. If it requires immediate update then you could use transactional replication. I will state that if you are going to be bulk-loading a load of data the transactional replication will develop a bit of latency but it can keep up pretty well. The snapshot replication solution will push ALL the data across daily. So, you will have to figure out which one will work best in accordance with the requirements before you. Read up on both in Books On Line.

Have fun. Sounds like a good project.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 6

Hi David,

Thanks for the help.

But i have a question that i had never done this before. Can you tell me the steps if so.(snapshot replication and copy database)

Also the database i want to copy in local server have to be copied some particular tables , there are around 80 tables , in which 20 tables need to be moved in the local server

And then i can use snapshot replication.

I was trying to copy the database but it was allowing me copy the full database not letting me select particular tables from that database

thanks

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 7

Yes. If you are going to get granular and define only certain tables then you would need to use transactional replication. The are quite a few considerations and you should really read BOL to get an understanding of all this prior to starting. Once you get that and have a plan mapped out feel free to reply and or create a new post with questions, etc.

Additionally, if this is a bulk-load type process that is updating your primary server, you could actually run the same process against the other server as well. Not sure if that is possible in your scenario.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 8

Hi David,

Thanks for replying so quick, it had ease my work and i will be able to do it. thanks a lot for the help

Take Care

Sumit