A Primer on Attaching Databases with PowerShell Method AttachDatabase

Attaching a SQL Server database using PowerShell can be straightforward. Here's an effective and easy approach using the Invoke-Sqlcmd Cmdlet, by encapsulating your SQL statement in a variable, and then pass it to the Invoke-Sqlcmd cmdlet. For example:

$sql_command = "

    USE [master]

    GO

    CREATE DATABASE [TestDB_20240115_173550] ON 

    ( FILENAME = N'K:\\SQL2022AG01\\DATA\\TestDB_20240115_173550.mdf' ),

    ( FILENAME = N'L:\\SQL2022AG01\\LOG\\TestDB_log_20240115_173550.ldf' )

    FOR ATTACH

    GO

"



Invoke-Sqlcmd -ServerInstance SQLVM01\\SQL2022AG01 -Query $sql_command

The above command assumes that the PowerShell module for the SQL Server is installed and imported into your PowerShell session.  For any DBA using PowerShell to manage SQL Server instances, I think this should be automatically loaded, which you can do by copying the SQL Server module files in one of the directories listed in $env:PSModulePath. 

To determine the locations where PowerShell modules can be stored, simply enter $env:PSModulePath in the PowerShell console and press Enter. This will display the paths:

PS C:\Users\Dummy> $env:PSModulePath

C:\Users\dummy\Documents\WindowsPowerShell\Modules;

C:\Program Files\WindowsPowerShell\Modules;

C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules

On my system, these three directories are where I can place PowerShell module files. The choice of directory depends on whether I want the module accessible only to my user profile or to all users on the computer. It also depends on my permissions to create files and folders in the Program Files and System32 directories.

You can verify if the SQL Server module is loaded into your current session with the Get-Module command. 

PS C:\Users\Dummy> Get-Module -Name sqlserver | Format-List

Name              : SQLServer
Path              : C:\Users\dummy\Documents\WindowsPowerShell\Modules\SQLServer\21.1.18256\SqlServer.psm1

Description       : This module allows SQL Server developers, administrators and business intelligence professionals to automate database development and server administration, as well as both 
                    multidimensional and tabular cube processing.
                    
                    For Release Notes, expand the Package Details section on this page.
                    
                    For automation with SSRS & Power BI Report Server, use the ReportingServicesTools module available at https://www.powershellgallery.com/packages/ReportingServicesTools
ModuleType        : Script
Version           : 21.1.18256
NestedModules     : {Microsoft.SqlServer.Management.PSSnapins, Microsoft.SqlServer.Management.PSProvider, Microsoft.AnalysisServices.PowerShell.Cmdlets, 
                    Microsoft.SqlServer.Assessment.Cmdlets...}
ExportedFunctions : {Invoke-SqlNotebook, SQLSERVER:}
ExportedCmdlets   : {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp, Add-SqlAzureAuthenticationContext...}
ExportedVariables : 
ExportedAliases   : {Decode-SqlName, Encode-SqlName}

BTW,  in a layman's terms, a PowerShell module is basically your regular folder containing files like script files, DLL/assemblies files etc, as highlighted SQLServer value in the above example.

If it returns nothing that means you currently don't have the SQLServer module in your session. Generally, though if the module folder is in one of the folders listed in $env:PSModulePath then it should get automatically loaded into your session when you reference any of the commands from that module for the first time. PowerShell won't load a module into your session until it is required.

You can also manually load a module into your session using the Import-Module cmdlet:

Import-Module -Name sqlserver

If not then you can install it from the PSGallery, provided that PSGallery is configured as a repository:

Find-Module -Name SQLServer

Install-Module -Name SQLServer

Of course, I already have a previous version of this module installed, which I can upgrade to the latest version by adding -Force to the Install-Module command. This is also essentially how you would typically upgrade an existing module.

---

That being said, beyond SQL statements, PowerShell offers native cmdlets for database management. To attach a database, use the AttachDatabase method. Here's an example:

# Connect to the sql server instance

$sql_instance = Get-SqlInstance -ServerInstance SQLVM01\SQL2022AG01

# Attach the AttachDatabase method

$sql_instance.AttachDatabase('TestDB_20240115_173550', 'K:\SQL2022AG01\DATA\TestDB_20240115_173550.mdf')

The value for first parameter, "TestDB_20240115_173550", is the name for the attached database. The second value is the file path for the primary data file. SQL Server will look up the location of other files (secondary data files, transaction logs etc.) from the primary data file and will look for them in their original locations. 

Below is more information on the AttachDatabase method, along with the parameters you can pass to it. Notice the Definition field, each line in it starting with "void...." is called method/function overload  (https://en.wikipedia.org/wiki/Function_overloading)  , each has different list of parameters that SQL Server will automatically determine based on how you execute the method:

Get-Member -InputObject $sql_instance -MemberType Methods `

           -Name AttachDatabase | Format-List

TypeName   : Microsoft.SqlServer.Management.Smo.Server
Name       : AttachDatabase
MemberType : Method
Definition : void AttachDatabase(string name, System.Collections.Specialized.StringCollection files, string owner), 

             void AttachDatabase(string name, System.Collections.Specialized.StringCollection files), 

             void AttachDatabase(string name, System.Collections.Specialized.StringCollection files, 
                Microsoft.SqlServer.Management.Smo.AttachOptions attachOptions), 

             void AttachDatabase(string name, System.Collections.Specialized.StringCollection files, string owner, 
                    Microsoft.SqlServer.Management.Smo.AttachOptions attachOptions)

Lets look at the first function overload:

Void AttachDatabase(string name, System.Collections.Specialized.StringCollection files, string owner)

It accepts 3 parameters:

1. Name: This would be the name of the database when it's attached
2. Files: This would be an array with the database file names i.e. MDFs, NDFs, Transaction log files, FileStream directories etc. Only the location of the primary data file is required, rest are optional. If other files are omitted, SQL Server will read their location from the primary data file and then will try to look for them in their original location. If any of the files is missing, it will throw an error, and believe me, those error messages are not very informative!
   
3. Owner: The name of the database owner. If you omit this value then the login of the current user will be the owner of the attached database. I generally use "sa" as the database owner.

Below is the second overload for the method. In this one you are not passing the value for Owner parameter.

 

void AttachDatabase(string name, System.Collections.Specialized.StringCollection files) 

Advanced Options: 

The method also supports AttachOptions for more control. Here is list of options you can pass to it:

• EnableBroker
• ErrorBrokerConversations
• NewBroker
• None
• RebuildLog

For detailed information, refer to the Microsoft documentation:

https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.attachoptions

https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.server.attachdatabase

In conclusion, PowerShell offers both straightforward and advanced methods for attaching SQL databases. Whether you prefer direct SQL commands or native PowerShell cmdlets, the process is efficient and well-integrated into the SQL Server environment. By understanding the nuances of these methods and ensuring the necessary modules are installed and loaded, you can seamlessly manage your SQL Server databases.