Copy Database locally 3

[Keyth]

I see there are plenty of posts about how to copy database, I have worked with db files quite a lot.

without detaching the database and copying files etc, is there not a way it can be done with SQL query?

I have an app which I would like to adapt so that at startup it prompts the user to run a test copy instead of the live copy. To do this I would like to create a replica of the database with '_test' on the end of the database name. It would be very nice to be able to do this in a query without knowing the file names.

I am coding an exe using vb.net 2010 and running SQL Svr 2005 Workgroup edition.

Would I need to create the database and then For each Table in the source database, copy it to the new database?

Can anybody offer any advice on Database Copy methods please?

Thanks in advance to all

 

[simian336]

Easyist way is to make a back up and restore it with a new name.

Simi

 

[ousoonerjoe]

Restore the latest backup of your production database to a new database name. You can declare the new name and Data/Log file locations during the restore action. This is probably the simplest method. It will create all the triggers, functions, tables, defaults, etc., whereas doing a table Import/Export will not migrate all these items.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[Keyth]

Hmmm. Thanks for your replies.

To specify the restore filename requires the location of the original doesnt it? how can this be done without knowing file locations? Or is there another way?

 

[gmmastros]

You can get the location of the data files for a particular database by running the following query:

Select * from sysfiles

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Keyth]

OK thanks for all your help guys(n gals) I'll have a go with selecting filenames and backup/restore.

Regards,
Keith