Programmatically Attach/Detach SQL Server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RP - 26 Sep 2007 19:57 GMT

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.

Reply to this Message

Alberto Poblacion - 26 Sep 2007 20:33 GMT

>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.

Reply to this Message

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...