Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

import database and update data in database daily Expand / Collapse
Author
Message
Posted Saturday, January 24, 2009 8:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:45 AM
Points: 134, Visits: 419
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.
Post #643094
Posted Saturday, January 24, 2009 9:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:34 AM
Points: 2,049, Visits: 3,590
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #643099
Posted Sunday, January 25, 2009 3:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:45 AM
Points: 134, Visits: 419
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

Post #643237
Posted Sunday, January 25, 2009 9:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:34 AM
Points: 2,049, Visits: 3,590
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #643257
Posted Monday, January 26, 2009 10:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:45 AM
Points: 134, Visits: 419
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

Post #643563
Posted Monday, January 26, 2009 11:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:34 AM
Points: 2,049, Visits: 3,590
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #643604
Posted Monday, January 26, 2009 11:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:45 AM
Points: 134, Visits: 419
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
Post #643615
Posted Monday, January 26, 2009 2:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:34 AM
Points: 2,049, Visits: 3,590
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #643708
Posted Monday, January 26, 2009 4:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:45 AM
Points: 134, Visits: 419
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
Post #643797
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse