hosting a copy of a local database

Question

hello, i wasnt sure if this was the correct forum for posting my question but .

i just wanted to know how does one go about hosting a local copy of a database and keeping it updated (be it instance changes or every 24 hours) once a change it made to the local copy (but not vice versa)

if i could be pointed to a nice place to read about it that would be good thanks

Answer 1

Hi,

This sounds like snapshot replication to me. Have a look at this link:

http://technet.microsoft.com/en-us/library/ms151734.aspx

http://msdn.microsoft.com/en-us/library/ff648057.aspx

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 2

hmm must it be snapshot? cause....i can only use the free version :(

Answer 3

Using Express, hmm, in that case a backup/restore job once every 24 hours I think would be the only way...

The job would need to be with Windows Scheduled Tasks that calls a batch file that has sqlcmd to perform the operation.

Here is link for sqlcmd http://msdn.microsoft.com/en-us/library/ms162773.aspx

backup http://msdn.microsoft.com/en-us/library/ms186865.aspx

restore http://msdn.microsoft.com/en-us/library/ms186858.aspx

windows scheduled tasks http://www.iopus.com/guides/winscheduler.htm

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 4

thansk :) ill read it

but...its not a backup or restore im looking for...

say for example, you have a db on your pc. sales db and after everyday at 5pm when you go home you want your only db online to reflect what happened during your day.

unless if you meant back up your work db and restore your home db to that backup?

Answer 5

Hi, no, I was think more about within a network environment, but in the example you give above, I would actually do what you suggested yourself...Take a backup each day, then restore it at home. Not sure there is another way around it.

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 6

no that was a little example. it has to be a network enviroment.

the real scenario is...people come into an office and pay...( the office's local database is updated )

 id like their account to be updated every X hours

Answer 7

Ok, now I understand. In that case, the ideal solution would be replication (not nessesarily snapshot - probably merge) but where you are using Express, the only 2 ways I can think to do it, is with the backup/restore using sqlcmd or triggers. You could use an after trigger, and then put the code in thre body of the trigger to update the account on the main database. This would not be once an hour, it would be as and when the updates occur.

Here is a link for triggers - http://msdn.microsoft.com/en-us/library/ms189799.aspx

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 8

ouuuu triggers...im pretty good at triggers...but yea that sounds like what i want...

but what i have no idea what to do is to link a local database with a hosted one.

P.is there any specific hosting services you would recommend?

Answer 9

Hi,

Sorry, I'm getting confused again. If everything is network based, why do you need hosting?

You can use linked servers http://msdn.microsoft.com/en-us/library/ms188279.aspx

Rather than traditional hosting, have you though about SQL Azure. That is MSFT hosted "SQL in the cloud" I use it and like it, although there are limitations of course...

http://msdn.microsoft.com/en-us/library/windowsazure/ee336279.aspx

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 10

cause i want the database to be accessed over the internet.

not the local copy...accessed

i want a COPY of the Local DB to be accessed and updated by the local database to make sure that data remains relavant .