Easiest way to make copy SQL Server DB to test DB?

Question

What is the easiest way to take a SQL Server database and make a test copy of it?

I have looked through some existing topics but not sure if there is an easier way. I have database publisher. I want to be able to keep both database on the same server, potentially.

Update: I used the Microsoft SQL Server Publishing Wizard to script to file, create a new database, add "use db", and execute the script. This seemed to work okay. The attach/detach is not recommended because it leaves links back to the original location if it is a full text search database or if the log is missing.

Answer 1

Detatch the database (means taking it offline), copy the mdf file, and then re-attach both the original and the copy.

Answer 2

I would just create the database, then use the "Import Data" task in SS Mgmt Studio to copy the data over. Or you could back up the production database and restore it into the test database.

Maybe not the absolute easiest ways, but pretty low-drama. You can also script the data to a file and play that back into a new database -- that takes awhile, but it's handy for things like version control, and it's human (well, "developer")-readable.

Answer 3

I always just back it up then restore to a different name/file set; How to: Restore a Database to a New Location and Name (Transact-SQL) or you can creater an empty db & use the restore "wizard" enabling OVERWRITE and changing the restore file paths.