Untitled

# Configuration
$serverName = "YourServerName" # SQL Server name
$databaseFolder = "C:\Path\To\DatabaseFiles" # Folder containing database files
$sqlUsername = "YourSQLUsername" # SQL Server username
$sqlPassword = "YourSQLPassword" # SQL Server password

# Create SQL credentials object
$securePassword = ConvertTo-SecureString -String $sqlPassword -AsPlainText -Force
$sqlCredential = New-Object System.Management.Automation.PSCredential($sqlUsername, $securePassword)

# Identify MDF and LDF files
$mdfFiles = Get-ChildItem -Path $databaseFolder -Filter *.mdf
$ldfFiles = Get-ChildItem -Path $databaseFolder -Filter *_log.ldf

if ($mdfFiles.Count -eq 0) {
    Write-Output "No MDF files found in folder: $databaseFolder"
    exit
}

if ($ldfFiles.Count -eq 0) {
    Write-Output "No LDF files found in folder: $databaseFolder"
    exit
}

# Map MDF files to LDF files
$databases = @{}
foreach ($mdf in $mdfFiles) {
    $dbName = [System.IO.Path]::GetFileNameWithoutExtension($mdf.Name)
    $ldf = $ldfFiles | Where-Object { $_.Name -like "$dbName`_log.ldf" }
    if ($ldf) {
        $databases[$dbName] = @{
            MDF = $mdf.FullName
            LDF = $ldf.FullName
        }
    } else {
        Write-Output "No LDF file found for database: $dbName"
    }
}

# Execute T-SQL commands
foreach ($dbName in $databases.Keys) {
    $mdfPath = $databases[$dbName].MDF
    $ldfPath = $databases[$dbName].LDF

    $sqlCommand = @"
CREATE DATABASE [$dbName] ON 
(FILENAME = N'$mdfPath'),
(FILENAME = N'$ldfPath')
FOR ATTACH;
"@

    Write-Output "Executing command for database: $dbName"
    Write-Output $sqlCommand

    try {
        Invoke-Sqlcmd -ServerInstance $serverName -Credential $sqlCredential -Query $sqlCommand
        Write-Output "Database $dbName attached successfully."
    } catch {
        Write-Output "Error attaching database $dbName: $_"
    }
}

Write-Output "Database attachment process completed."