1

I have two sql servers installed on my computer (SQL2008 EXPRESS) and also SQL2008 that comes with the specific software that we are using. I need to make a service that runs all the time and at a specific time updates the non existing records in the SQL2008 EXPRESS from SQL2008.. can you suggest a way of doing this? Currently the best thing I got is making a local copy in excel file, but that will result 365 excel files per year which I dont think is a good idea :)

p.s. sorry if my english is bad :)

zhv89
  • 9
  • 5
  • 3
    Considering you are using Express, it's possible to set up replication with SQL Express as a subscriber: http://technet.microsoft.com/en-US/library/ms165654(v=sql.90).aspx. That would avoid you having to hand-code a services yourself, and instead rely on internal SQL Server functionality specifically meant for pushing data between different databases. – SchmitzIT Aug 27 '14 at 11:00
  • I will take a look at the link you suggested, but most likely I will need to do it by code... – zhv89 Aug 27 '14 at 11:27

2 Answers2

0

You don't have to hand-craft your own software for that. There are 3rd party tools like OpenDbDiff or RedGate dbdiff to do that. These tools generate the differential sql that you can apply on your target database.

Raghu
  • 699
  • 7
  • 14
  • Yes I know there are lots of free tools out there, but for this project it is specific that it has to be hand made.. so that leaves me no choice.. – zhv89 Aug 27 '14 at 11:25
0

I'm confused when you mention Excel. What would Excel have anything to do with moving data from one SQL database to another?

The short answer is, if you need a C# service, then write a C# service that copies the data directly from one database to the other. The problem that you are trying to solve is not very clear.

Having said all that, and with my limited understanding of the problem, it sounds like what you need is a SQL job that is scheduled to run once a day that copies the data from one server to the other. Since it sounds like they are on separate instances, you'll just need to set up a linked server on either the source or destination database and either push or pull the data into the correct table(s).

EDIT:

Ok, so if a windows service is a requirement, that is perfectly acceptable. But, like I mentioned, you should forget about Excel. You wouldn't want to go from SQL->Excel->SQL if you have no other reason for the data to exist in Excel.

Here is some information on creating a windows service: Easiest language for creating a Windows service

Here is a simple tutorial on accessing SQL in C#: http://www.codeproject.com/Articles/4416/Beginners-guide-to-accessing-SQL-Server-through-C

If you want a more formal solution (read: data access layer), I'd point you toward Entity Framework. The complexity of the project will probably be the driving factor on whether you just want to do SQL statements in your code vs. going with a full blown DAL.

Community
  • 1
  • 1
Wyatt Earp
  • 1,783
  • 13
  • 23
  • Well, I said excel because that is the only backup I am able to create for the moment :) and i was thinking about export to excel and after that import into the other server but that is too much of a work.. I will try to explain in better.. I have both of the servers on the same PC Server1 - .\Data , Server2 .\MirrorData, now my problem is that I am not really good at database programming and I don't know which method to use to copy (or update if records exist) information from the Data server to the Mirror server. All i know is that it has to be a service.. market requirement – zhv89 Aug 27 '14 at 12:28
  • Thanks for the links, I think I am getting the idea but I don't know how to transfer data from one table at the .\Data server to another table in the .\MirrorData table should I fill a DataTable first and then transfer it or should I use other method? – zhv89 Aug 27 '14 at 14:06
  • @zhv89 Basically, you just need some type of in-memory objects to hold the data. If you use EF, all of those objects will be created according to the framework. If you are using SqlCommands then you'll manually serialize/deserialize into your own objects, which can be as detailed as you like. – Wyatt Earp Aug 27 '14 at 18:00