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.
Thanks,
Greg.
Best Answer
I would just backup/restore. This accomplishes the same thing, validates your recovery story, and will easily handle schema/object changes as well - without really putting any undue strain on the existing database (you're already backing it up, right?). If it were on a different server (or if you ever split them up), I might suggest log shipping as opposed to mirroring or replication, that way you can fiddle with how long between log backups, how long to delay restores, what kind of maintenance window to use, etc. Of course even on a different server a very simple backup/restore will work fine.