I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally.
We don't need to transform data, just copy from DatabaseA to DatabaseB (but never from DatabaseB to DatabaseA).
Would you set this up under the Replication services or an SSIS job? What are the advantages and disadvantages of each?
The reason I am doing is so I can have a staging application which read and writes to a staging database, but then every night any new data from the live database is pulled in to the staging database.
The live database is updated daily with new events and we want to ensure that the staging environment resembles the live environment as much as possible.