Attach or Detach a SQL Server Database Using SMO - August 2009

The tip-of-the-month demonstrates attaching or detaching a database to/from SQL Server using SMO. The operative code is contained in VB.Net class module cConnectForAttachSQLDatabase. A VB.Net Windows application demonstrates the process.

The Demo


Enter the name of the SQL Server Instance to be used then click 'Connect' to test the connection.
In this example the connection succeeded.


If the connection fails an error message is issued.


Locate the database to be attached then click 'Attach'.
In this example database 'AttachSQLDatabase.mdf' is attached.
This sample database is optionally distributed with the source code. See below.


The Attach process first verifies that the database is not already attached. If it is an error message is displayed.


The folder is compressed.


The database is compressed.


Click 'Detach' to detach the database selected in the dropdown box.
A status message indicates success.

cConnectForAttachSQLDatabase Methods and Properties

Method Operands Description Sample
Attach sFileName As String Attach sFileName. Use ParseGetJustName to get sDBName. Return True if successful. Raise an error if it fails. If Attach("c:\Data\MyDB.mdf") Then
Attach sDBName As String, sFileName As String Attach sFileName as sDBName. Return True if successful. Raise an error if it fails. If Attach("MyDB", "c:\Data\MyDB.mdf") Then
The Attach process verifies that sFileName exists. If not an error is raised. Next it verifies that the databases folder is not compressed and that it is not read only. If the folder is compressed an error is raised. If it is read only it is made read/write. The database file and associated log file, if any, is made read/write if necessary. If the database name already exists an error is raised.
Detach sDBName As String Detach sDBName. Return True if successful. Raise an error if it fails. If Detach("MyDB") Then
DoConnect sServer As String Open a connection to SQL Server instance sServer. Public properties oConn and oSQLServerSMO are set. Return True if successful. Raise an error if it fails. If DoConnect("Dell1705") Then
DoConnectClose   Close oConn if it is Open. Set oSQLServerSMO to Nothing. Return True if successful. If DoConnectClose Then
ExistsDatabase sDBName As String Return True if sDBName exists in any instance of SQL Server If ExistsDatabase("MyDB") Then
ExistsDatabase sDBName As String, oSQLServerSMO As Server Return True if sDBName exists in SQL Server instance oSQLServerSMO If ExistsDatabase("MyDB", "Dell1705") Then
IsFolderCompressed sFolderPath As String Return True if sFolderPath is compressed If IsFolderCompressed("c:\Data\") Then
IsFolderReadOnly sFolderPath As String Return True if sFolderPath is read only. If IsFolderReadOnly("c:\Data") Then
ParseGetJustName sDatabase As String Use the fully qualified sDatabase to return just the file name. Dim sDBName = ParseGetJustName("c:\Data\MyDB.mdf")
Returns "MyDB"
SetFileReadWrite sFileName As String Sets sFileName to read and write. SetFileReadWrite("c:\Data\MyDB.mdf")
Property Description
oConn As ServerConnection Opened in DoConnect.
oSQLServerSMO As Server Opened in DoConnect.
oDB As Database Not set. Can be used to iterate the databases in oSQLServerSMO.
sErrDescription As String Contains the error message text if DoConnect fails.

Other Modules Used in the Demo

Module - Click link for More Info Comment
BGStatusBar.vb Used to provide status and error information.
cFilenameParse.vb Used to get the file name from the fully qualified folder/filename.
modLocationLocationLocation.vb Used to save and get the location of the form. Center the form.
Setup the Window menu.
modRegRead.vb Read and Write to the registry.
Save selected values between sessions.

Download VB.Net Code

Download - VS2005 VB.Net Zipped File  Size 
Attach SQL Database with Sample Database AttachSQLDatabase.zip 682k
Attach SQL Database without Sample Database AttachSQLDatabaseNoDB.zip 59k

Previous Tip   Tip Index   Next Tip

Home

Updated 2011/04/16