How to snapshot a SQL Server Database without taking it offline

Question

We occasionally want to copy our production database with data and have it populate a corresponding database in our test environment so that we can reproduce production problems.

We currently wait for a maintenance window and do an Detach/Attach.

We're using SQL Server 2008 and both test and production databases are on the same server.

What is the best way to copy a snapshot that would 1) not require the production database to go offline and 2) be capable of being automated via a build script?

Answer 1

Make a backup. You should do anyway... and you can do the restore overriding file and database name and file positions.

both test and production databases are on the same server.

Bad idea. I ahve seen SQL scripts grind a SQL Server to a halt and I would not like to happen that on a production system. I suggest using your backup server for testing.