Attaching and detaching databases

Attaching and detaching databases can also be done programmatically using SMO. The SMO server object provides methods that allow you to perform this task quite simply.

Detaching databases

Before you detach a database, you must first check for a few conditions that might prevent the database from being detached. For example, if the database is currently being replicated or if the database has some existing snapshots, the database cannot be detached. Once these conditions are cleared, you can use the DetachDatabase() method to detach the database. The following is an example snippet:

Import-Module SQLPS -DisableNameChecking

#current server name
$sourcename = "ROGUE"
$sourceserver = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sourcename

$dbname = "Chinook"
$sourceserver.DetachDatabase($dbname, $true, $true)

Once this script has executed, you can confirm in SSMS whether the database has indeed been detached from the instance. Attaching the database...