Use C # Detach and Attach Databases

First, use the SQL statement for the Detach and Attach databases:

use mastergosp_detach_db 'TestDB'go                                                                                                                use mastergosp_attach_db '1','C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB.mdf','C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf'go

Using C # For Detach and Attach databases, I think of two methods

1. Run the preceding statement using SqlCommand. The Code is as follows:
Using (SqlConnectionsc = newSqlConnection (txtConntionString. Text ))
{
Using (SqlCommandcmd = newSqlCommand (@ "use master;
Exec sp_detach_db 'testdb' ", SC ))
{
If (SC. State = ConnectionState. Closed)
SC. Open ();

Cmd. CommandType = CommandType. Text;
Cmd. ExecuteNonQuery ();
}
}

Using (SqlConnectionsc = newSqlConnection (txtConntionString. Text ))
{
Using (SqlCommandcmd = newSqlCommand (@ "use master;
Exec sp_attach_db 'testdb'
, 'C: \ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ DATA \ TestDB. mdf'
, 'C: \ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ DATA \ TestDB_log.ldf '", SC ))
{
If (SC. State = ConnectionState. Closed)
SC. Open ();

Cmd. CommandType = CommandType. Text;
Cmd. ExecuteNonQuery ();
}
}

Note that the go statement in the SQL statement is replaced with a semicolon here. The SQL statement can be executed normally without exec, and exec must be added here.
Because SqlConnection is used, a valid connection string must be specified. You can use the database TestDB as the connection string in Detach, but TestDB is not used in Attach because TestDB is not created yet, this should be noted when writing code. Of course, if the connection string uses the master database, the use master can save

2. Use the Attach and Detach functions provided by SQL Server.
Here, several Dll files provided by SQL Server are used.
Add reference first:
Microsoft. SqlServer. ConnectionInfo
Microsoft. SqlServer. Management. Sdk. Sfc
Microsoft. SqlServer. Smo
Namespace addition:
Using Microsoft. SqlServer. Management. Smo;
Using Microsoft. SqlServer. Management. Common;
Using System. Collections. Specialized;

The Code is as follows:
ServerConnection SC = new ServerConnection ("ServerNameHere"); // You can specify username and password here
Server server = newServer (SC );
Server. DetachDatabase ("TestDB", false );

ServerConnection sc = new ServerConnection("ServerNameHere");//You can specify username and password hereServer server = new Server(sc);
StringCollection files = new StringCollection();files.Add(@"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB.mdf");files.Add(@"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf"); server .AttachDatabase("TestDB", files, AttachOptions.None);

After execution, you can call the Disconnect method of the Server to close the connection.

There are some options for detach and attach databases, which can be specified as needed.

Update:

Check the help document of SQL Server to learn that sp_attach_db may be deprecated in the future. We recommend that you use create database, for example:

USE master;GOsp_detach_db Archive;GO-- Get the SQL Server data pathDECLARE @data_path nvarchar(256);SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)                  FROM master.sys.master_files                  WHERE database_id = 1 AND file_id = 1);-- Execute CREATE DATABASE FOR ATTACH statementEXEC ('CREATE DATABASE Archive      ON (FILENAME = '''+ @data_path + 'archdat1.mdf'')      FOR ATTACH');GO

 

However, currently, sp_attach_db in SQL Server 2008 can still be used normally.