Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


import database and update data in database daily


import database and update data in database daily

Author
Message
Shaun2012
Shaun2012
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 423
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.
David Benoit
David Benoit
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2396 Visits: 3650
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
Shaun2012
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 423
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
David Benoit
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2396 Visits: 3650
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
Shaun2012
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 423
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
David Benoit
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2396 Visits: 3650
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
Shaun2012
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 423
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
David Benoit
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2396 Visits: 3650
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
Shaun2012
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 423
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search