Programmatically Attach/Detach SQL Server

[RP]

I am using SQL Server 2005 with C# 2005. I want to programmatically
Detach a database, then copy its files from the data folder to another
location and then Attach the database back. Can it be done using C# ?
If yes, please illustrate.

 

[Alberto Poblacion]

I am using SQL Server 2005 with C# 2005. I want to programmatically
Detach a database, then copy its files from the data folder to another
location and then Attach the database back. Can it be done using C# ?

Yes, you can do that by openning a SqlConnection to another database
(such as "master"), and sending a call to the system stored procedure
sp_detach_db. You can do this by means of the method ExecuteNonQuery() of a
SqlCommand object. Note that the detach will fail if the database is in use.
After copying the database files (System.IO.File.Copy(...)), you
reattach the database with a call to sp_attach_db, or better yet, CREATE
DATABASE database_name FOR ATTACH.