7

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.

Improve this question
1
  • Same Instance or different?
    – RateControl
    Commented May 4, 2011 at 13:47

4 Answers 4

Reset to default
7

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.

Improve this answer
1
  • Thanks. This is exactly what I want to do with minimum hassle.
    – gfyans
    Commented May 6, 2011 at 8:59
4

I'd have a restore script

And I'd have it restore the latest "real" backup too: this way you test the backup file integrity too (of course it could be corrupt later but it's a nice to have). This new script shouldn't take it's own backup.

We have this in our current shop but restore into non-production environment

I wouldn't use SSIS or replication: you want the whole database so just restore.

Improve this answer
2

If you have Enterprise edition you can use database snapshots which is a very quick process. Otherwise, you can create TSQL scripts to backup and restore your DB and schedule these scripts in the SQL agent. Rough outline of the steps: Create a SQL script to backup your DB. Schedule this backup script to run during a low usage period. Then create another SQL script to restore your DB from the backup created in the previous step. Schedule the restore script to run at the time of day that you want an updated copy of the DB.

Replication is used more for distributed databases and might be an overkill for now.

Improve this answer
2
  • How can he use a database snapshot when the staging application needs to write to the copy without affecting the source?
    – Aaron Bertrand
    Commented Jun 9, 2011 at 23:20
  • @Aaron Bertrand you are right. I missed the part where the staging DB needs to be written to. Snapshots are read only copies. Thanks.
    – StanleyJohns
    Commented Jun 11, 2011 at 3:38
1

If it's on the same machine and instance, I'd go with snapshots. There's mainly no maintenance included, only need take care of the space on the drive you've set it, because if it's a highly used database it will grow pretty fast.

If it's on a different machine any process that simulates log shipping or even plain restore of the last backup file would suffice. I agree with Stan that replication or any SSIS process would be an overkill for your situation. Replication has too many burdens for being used in this simple task and SSIS would just be an over complication.

Improve this answer
3
  • How can he use a database snapshot when the staging application needs to write to the copy without affecting the source?
    – Aaron Bertrand
    Commented Jun 9, 2011 at 23:20
  • @Aaron: I think I didn't read the last part of the question, about writing to this database :-). In this case the snapshot is, of course, invalid and I would go by your solution, backup and restore. Btw, how will log shipping help for this? In LS the databases are either in restoring mode or in standby, read-only... :-).
    – Marian
    Commented Jun 10, 2011 at 6:44
  • You can log ship until you are ready to use the secondary as a staging database, then restore with recovery. When you're done using it, you can simply re-initialize log shipping. I've implemented fully-automated solutions exactly like this in the past.
    – Aaron Bertrand
    Commented Jun 10, 2011 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.