I must replicate a MSSQL 2005 db (hosting via a hosting that is shared provider) on an integration server for development reasons. MSSQL server provides a number of methods for carrying out data replication, but I don't know that them works with an average hosting that is shared atmosphere.
The concept is always to update the database daily approximately, far better if it is possible incrementally. Any idea what direction to go?
You will find handful of methods for doing the work.
Use SQL Server 2005 copy function, however it would fail if SQL Representative is not destroying or there's any permission restriction - mostly prefer to happen with hosting that is shared.
Use SQL Server backup/restore: the issue is usually you do not have the Database server file system with hosting that is shared.
Replicate database schema on development server, then perform a DTS to transfer data, it can save you the DTS package ( even by hand edit it as needed ) and hang up a scheduled task within the development machine, it might instantly synchronize the database everyday, it might not be incrementally, is dependent in your database design/schema.
option 3 appears to require more work but really you can easily setup and would save you plenty amount of time in a long term.
every other suggestions I must hear too.
It is dependent how the hosting atmosphere is setup
I previously had jobs to HOST: backup SQL DB's on SQL Server, copy to Web/FTP Server Internal: FTP download the backup file, restore on the SQL Server
That wasn't fun, but when it's setup it really works more often than not
When the remote SQL Server is definitely accessible, you are able to setup SQL Replication, using Snapshot Replication (basically schedule backup-and-restore) daily
I've at this time a DB duplicated from server DB to my computer's own SQL Server using Snapshot Replication (Used to do it through the wizard)
Try the SQL Server Posting Wizard 1.2, that is incorporated with Versus 2008. Or download the 1.1 version here: