Nightly importable or attachable copies of production database

Question

We would like to be able to nightly make a copy/backup/snapshot of a production database so that we can import it in the dev environment.

We don't want to log ship to the dev environment because it needs to be something we can reset whenever we like to the last taken copy of the production database.

We need to be able to clear certain logging and/or otherwise useless or heavy tables that would just bloat the copy.

We prefer the attach/detach method as opposed to something like sql server publishing wizard because of how much faster an attach is than an import.

I should mention we only have SQL Server Standard, so some features won't be available.

What's the best way to do this?

Answer 1

MSDN

I'd say use those procedures inside a SQL Agent job (use master.xp_cmdshell to perform the copy).

Answer 2

You might want to put the big huge tables on their own partition and have this partition belong to a different file group. You would backup then backup and restore the main file group.

You might want to also consider doing incremental backups. Say, a full backup every weekend and an incremental every night. I haven't done file group backups, so I don't know if these work well together.

Answer 3

I'm guessing that you are already doing regular backups of your production database? If you aren't, stop reading this reply and go set it up right now.

I'd recommend that you write a script that automatically runs, say once a day, that:

1. Drops your current test database.
2. Restores your current production backup to your test environment.

You can write a simple script to do this and execute it using the isql.exe command line tool.