How to copy a common MSSQL 2005 database for development purposes?
I would like to replicate an MSSQL 2005 db (hosted through a hosting provider) directly on an integration server for development purposes. MSSQL provides quite a few ways to replicate data, but I'm not sure if any of them are compatible with a typical shared hosting environment.
The idea is to update the database once a day or so, much better if it can be done gradually. Any idea how to proceed?
source to share
There are several ways to do this.
-
Use the copy feature of SQL Server 2005, but it will not work unless SQL Agent is destroyed or there are some restrictions on access restriction - this mostly happens with shared hosting.
-
Use SQL Server backup / restore: The problem is usually you don't have access to the file system of the shared hosting database server.
-
Replicate the database schema on the development server, then do DTS to transfer the data, you can save the DTS package (even manually edit it if needed) and set up a scheduled task on the development machine, it will automatically sync the database every day, it may not be gradual depends on your database design / schema.
Option 3 seems to take more work, but is actually easy to set up and will save you a lot of time in the long run.
any other suggestions I would like to hear too.
source to share
It really depends on the setup of the hosting environment
Earlier I had HOST jobs: backup SQL database to SQL Server, copy to Web / FTP server Internal: FTP uploads backup file, restore to SQL Server
It wasn't fun, but once it gets set up, it works most of the time.
If the remote SQL Server is readily available, you can set up SQL replication using snapshot replication (essentially scheduling backups and restores) daily
I have right now a DB replicating from the server DB to my own SQL Server machine using Snapshot Replication (I did this using the wizard)
source to share