How do I synchronise local SQL Server Express db with my online SQL Server 2005 db?
For various reasons, I have a situation where the data in the SQL Server 2005 database hosted by my ISP is more up to date than the data in the matching SQL Server Express database on my own PC. I now want to synchronise these two databases but don't know how to do it.
Because I am still developing my ASP.NET 3.5 app, which has not yet been opened up to users, I am only used to synchronising in the other direction by first using the Database Publishing Wizard within Visual Web Developer 2008 Express to create a suitable script associated with my SQL Server Express database and then running this script in SQL Server Management Studio Express while connected to my online database. However, I have no idea how to reverse this process (which I guess will become a regular requirement once my app goes live). Assistance would be much appreciated.
David
|
0 |
|
haggis999 
asp.net.sql-datasource
29906 articles. 
0 followers.
12 Replies
1470 Views
I would get a copy of SqlCompare http://www.red-gate.com/products/SQL_Compare/index.htm and SqlDataCompare http://www.red-gate.com/products/SQL_Data_Compare/index.htm
They will pay for themselves almost the 1st time you use them; to synchronise 2 databases will take less than 10 minutes (probably less than 5).
Simon
Sharing Knowledge Saves Valuable Time!
www.sjmdev.com
|
|
0 |
|
ganseki Hi Simon,
Thanks for the suggestion, but at £245 (GBP) each for the standard editions, these products are not cheap. I am developing an app for a non-profit making organisation and funds are very tight. I was hoping that there was a way to do what I want using Microsoft SQL Server Management Studio Express......David
|
|
0 |
|
haggis999 Could you connect to the other database with Visual Web, and attempt the publish from the other direction (reversing your current process)?
But to be honest that thought scares me a little as I suspect it may overwrite changes you've made to your development database. I have heard good things about this free tool http://www.xsqlsoftware.com/LiteEdition.aspx... but I've not used it much so I can't vouch for how good it is.
RedGate do offer a package deal (bundle) for both programs http://www.red-gate.com/products/sql_bundles/index.htm it's only £345 so quite good value compared to buying them both separately, though I appreciate that may still be too much for the budget. But if its an on-going thing then it will pay for itself.
Simon
Sharing Knowledge Saves Valuable Time!
www.sjmdev.com
|
|
0 |
|
ganseki What is the nature of your access to the database at your ISP? For example do you have console access? In which case:
- Export a database creation script from the remote database.
- Use the script to create a new database locally.
- Export say CSV files, table by table from the remote database.
- Copy the files locally.
- Load them using SSIS to the new local database.
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
|
|
0 |
|
TATWORTH ganseki:
Could you connect to the other database with Visual Web, and attempt the publish from the other direction (reversing your current process)?But to be honest that thought scares me a little as I suspect it may overwrite changes you've made to your development database. I have heard good things about this free tool http://www.xsqlsoftware.com/LiteEdition.aspx... but I've not used it much so I can't vouch for how good it is.
Simon,
I might be able to do the reverse process using VWD but it's going to take me a while to learn how to set up the connection. I'll also check out your link. Thanks for the info.David
|
|
0 |
|
haggis999 What is the nature of your access to the database at your ISP? For example do you have console access? In which case:
- Export a database creation script from the remote database.
- Use the script to create a new database locally.
- Export say CSV files, table by table from the remote database.
- Copy the files locally.
- Load them using SSIS to the new local database.
If it is just read a lookup table, you could create a web service accessible only by the fixed IP address of your local PC and bring data back using the web service.
I'm a bit of a newbie so I don't really know what you mean by console access. I can certainly connect to the online database via Microsoft SQL Server Management Studio Express and my ISP enables some admin tasks via a Helm control panel. So far, I've haven't noticed if I have access to SQL Server Integration Services (but I'm a little doubtful about that).
|
|
0 |
|
haggis999 >console access
Are you able to log on to the remote server with local administrator rights? It sounds like you have not got that level of access.
The web service method is probably the only way, unless you can back up the remote database and retrieve the backup. - Does you control panel allow that?
Who is your ISP?
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
|
|
0 |
|
TATWORTH I'm almost certain that I don't have local admin rights for the hosted database (shared Windows hosting by http://csnewmedia.co.uk/).
So far, I haven't spotted any database backup tools in Helm but I'll keep looking....
|
|
0 |
|
haggis999 >So far, I haven't spotted any database backup tools in Helm but I'll keep looking....
If no database backup on the remote service, then you have to consider writing a web service anyway to do a backup (or import data).
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
|
|
0 |
|
TATWORTH If no database backup on the remote service, then you have to consider writing a web service anyway to do a backup (or import data).I'm only about half way through designing my application (having bought my very first ASP.NET textbook just over 3 months ago). One of the things I have yet to consider in any detail is what I do with the data that users will eventually create when the website goes live.
The app is for processing entries to an annual international photographic competition. For the 2008 competition, all entry forms were submitted on paper and entered by my colleagues into an Access 2003 database I wrote for the purpose. This database had a 'front end' and a 'back end', as is normal with Access apps, and the back end was a standard Access Jet database. I converted this back end into a SQL Server Express database and then used this to create the database for my new website (intended to permit online entries for 2009). A few weeks after the closing date for entries the judging will take place, a process that will probably continue to be handled by my Access app.
Plan A is to modify my Access front end to work with the SQL Server Express back end (a non-trivial task) and then synchronise it with the online version on a regular basis right up to the closing date (and do my backups locally). I'm not yet sure if there is a better Plan B.....
Whatever happens, I certainly need to find a reliable and affordable synchronisation tool. How complicated do you think it would be to write my own web service for this purpose, as you have suggested?
David
|
|
0 |
|
haggis999 Could you connect to the other database with Visual Web, and attempt the publish from the other direction (reversing your current process)?Simon,
I am pleased to report that I was able to connect to my online database in the Database Explorer window of VWD and then run the Database Publishing Wizard to create a script. I then opened up Management Studio Express and connected to my local SQL Server Express databases. With the target database highlighted, I then executed this new script. All the new data that was in my online database was then successfully transferred to the SQL Server Express version.I'm still waiting for some feedback from XSQL about their free LiteEdition product. It may be that this will offer some additional benefits; time will tell. In the meantime, I do now have what appears to be a viable solution to my problem. Thanks for suggesting what I should have thought of myself!
David
|
|
0 |
|
haggis999 Just needed a fresh pair of eyes on the problem I guess
Pleased you got it working
Simon
Sharing Knowledge Saves Valuable Time!
www.sjmdev.com
|
|
0 |
|
ganseki 
Similar Artilces: I have an SQL Server 2000 DB that I created on one PC that I would like to move to a PC running SQL Server 2005 Express, is this possible? Will the SQL Server 2000 DB run under SQLS Server 2005 EXPRESS? Thank you for any thoughts, Yes, there are serveral ways to move the database: backup/restore, detach/attach, Copy Database Wizard, etc. For more information, you can refer to:Copy Database Wizard: http://msdn2.microsoft.com/en-us/library/ms190775.aspxBackup/Restore: http://msdn2.microsoft.com/en-us/library/ms190436.aspxDetach/Attach: http://msdn2.microsoft.com/en-us/library/ms190794.a...
Deploy Sql Server Express DB to Sql Server Express
I have developed by webite with Visual Studio 2005 and sql server express. I'm trying to deploy my website to a VPS with sql server express running on it I ftp'd the App_Data folder to the remote via the 'copy web site' Everything seemed okay until I started trying to run more complex database operations using stored procedures and transactions. It appears that the schema is messed up because when defining the parameters for a stored procedure, upon ExecuteNonQuery I get the error that I've assigned too many parameters which I have not (since it runs fine on...
Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!), I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine. But I've had nothing but frustration trying to get it up and running on a webhosting site. I picked hostmysite.com which uses SQL Server 2005. I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database. I tried using SQL Server Management Studio Express. I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf files ...
Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi,Basically i built a webpage with Login and it's working well from my local machine.Right now, i'm using Database Publishing Wizard. the problem is how do i convert.MDF files to .SQL 2005 to get my database publish in the internet? thanks Place your .MDF file in the App_Data directory, change the connection string accordingly and then publish your web site. ----------------------------------------------------------Please click Mark As Answer if this helped in solving your problem. Please click "Mark As Answer" if this hepled in solving your problem. ...
Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!), I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine. But I've had nothing but frustration trying to get it up and running on a webhosting site. I picked hostmysite.com which uses SQL Server 2005. I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database. I tried using SQL Server Management Studio Express. I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf file...
SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well. (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed. I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...
Transferring DB from SQL Express to SQL Server 2005
Hi,I have an asp.net (C#) project running perfectly on 2 machines running SQL Express.I need to transfer it to a machine running SQL Server 2005 (I don't believe it has SQL Express installed). I've setup the database on the new machine by right clicking 'Data Connections', Selecting my machine as the server name and attaching the database file from the other machine in the App_Data folder. My connection string is as follows: Data Source=My Machine Name;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Initial Catalog=ASPNETDB.mdf;Integrated Security=True It...
local SQL Server 2005 Express and Host with SQL Server 2000
I'm creating an app and have SQL Server 2005 Express installed. Most of the hosts now offering SQL Server 2005 are a bit pricey compared to those only offering 2000. Will I have any difficulty uploading my site and running the db on SQL 2000? Thanks in advance. I've been searching these forums but all people having about the same question, all have no replies in the thread. I'd love some feedback on this issue. I'm quite new to asp.net.I've got vs installed with sql 2005 express. but I also installed sql 2000 since my host is still using this one. how can I port the db ? ...
SQL Server Express and SQL Server 2005
can any one tell me what the main differences are in the two editions? also can you use and express 2005 edition database on sql 2005? if not how would you do that? Tuppers Look here for a detailed comparison between the two.Yes, you can use Express databases on SQL Server 2005. Just attach it to an instance of SQL Server 2005.Is this enough information?DonDon Kiely, MCP, MCSDIn the Last Frontier, Interior AlaskaPlease post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem! cheers i was told by my host t...
upgrading from sql server 2005 express edition to sql server 2005 express with advanced services
If i have been using sql server 2005 express for developing my application and i decide to upgrade to sql server 2005 express with advanced services while still working on the same application, what will happen to my application's database. Can i be able to continue with my work with out any major regrets.--------------------------------------------------No Project Too Eazy.-------------------------------------------------- I have answered you in this other thread there is no problem, if there is a problem I would have warned you. Hope this helps.http://forums.asp.net/...
Attach SQL DB to SQL Server 2005 using VB.Net
Is there any way to attach a SQL DB to SQL Server 2005 using VB.Net? If so can you also set security? Can you rephrase your question? Attach SQL DB from which version of SQL server ? Look up books online for sp_attachdb and sp_detachdb.***********************Dinakar NethiLife is short. Enjoy it.*********************** I have a DB that was created in SQL Server 2005. I have a website that displays data from systems all over the world. Each system sends it's data to it's own DB. The customer can then go to a website and see the data at real time. What ...
Easiest way to move a SQL Server Express 2008 DB to SQL Server 2000?
Hello, I created an application in Visual Studio Express using the App_Data folder for my SQL Server Express 2008 database. The machine that I have to port the application to is using SQL Server 2000. What is the easiest way for me to take my SQL Express 2008 DB and get it onto the SQL Server 2000 machine? I don't think that I can use Import/Export as the machines are not on the same network and cannott see one another. Thoughts? Also, are there any security issues with running SQL Server Express 2008 on the SQL Server 2000 machine? Would there be any point? T...
Confliction between SQL Server 2005 and SQL Server 2005 Express Edition
Hi, My objective is using a single DB file (mdf file) + SQL Server Express 2005 to run my program I got two softwares here, Visual Studio 2005 and SQL Server 2005. Firstly, I installed Visual Studio 2005, normally the SQL Server 2005 Express Edition will be installed with this software. After finishing the installation. I opened a new project and try to open a DB file (.mdf) from Visual Studio 2005, it works. The problem is that I can't restore the mdf file from an old database file since it lose a lot of functions that appear in SQL Server 2000 / SQL Server 2005. ...
Moving SQL Server 2005 Database to SQL Server 2005 Express
Hi, I have SQL Server 2005 in my Development machine but the hosting server has only SQL server 2005 Express. I was wondering if I develop the DB in SQL server 2005 would be able to move/deploy it to SQL Server Express in the production server? If so how? Can I just generate the script and then run it on the productions server? Or there is a better/easier way? Thanks in advanceShuaib----------------------- I don't anticipate any problems creating database in SQL Server 2005 and moving it to Express. If you need to generate the script to create an empty shell (no d...
Web resources about - How do I synchronise local SQL Server Express db with my online SQL Server 2005 db? - asp.net.sql-datasource
A-League head Damien de Bohun says no plans to synchronise final rounds in futureAll five games of the final round of the A-League season this weekend will have an impact on the make-up of the top six but Football Federation ...
Residents asked to synchronise toilet flushingResidents asked to synchronise toilet flushing
Choir singers 'synchronise their heartbeats'Choir singers not only harmonise their voices they also synchronise their heartbeats, a study suggests.
HaveClip synchronises clipboards across your network... probably don’t want your clipboard text overwritten every time someone else copies something – but fortunately the program can be set to synchronise ...
Yoga synchronises heart, mind, and body: PM Modi's advise to everyone... this a part of their lives. Sometimes, we notice our mind works on one thing, the body on another, and time brings us in conflict. Yoga synchronises ...
Sleeping in a tent could cure your insomnia: Exposure to the natural light of dawn and dusk helps synchronise ...Researchers at the University of Colorado Boulder found that after a week of exposure to the natural light-dark cycle, people find it easier ...
Pendulum clocks synchronise using sound pulses, scientists discoverScientists may have finally discovered why pendulum clocks synchronise when placed nearby.
App Store - Easy BooksRead reviews, get customer ratings, see screenshots, and learn more about Easy Books on the App Store. Download Easy Books and enjoy it on your ...
Hippocampus - Wikipedia, the free encyclopediaThe hippocampus is located in the medial temporal lobe of the brain . In this lateral view of the human brain, the frontal lobe is at left, the ...
Coordinated Universal Time - Wikipedia, the free encyclopediaCoordinated Universal Time ( UTC ) is the primary time standard by which the world regulates clocks and time. It is one of several closely related ...