Posted on:
Categories: SQL
Description:
Issue Due to a disk configuration issue we had to remount ~200 databases to our Staging SQL Server. This proved to be a very tedious task using the SQL Server Management Studio. I have read that Microsoft has added significant PowerShell improvements to SQL over the years but I have yet to play with it. This proved to be a perfect time. Interfacing with SQL proved to be very easy thanks to PowerShell. Using the following two lines of code you will be able to get to the SQL Server object. From here the possibilities are endless. Follow the link below for the Class Documentation. http//msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx #Load the SQL Assembly [System.Reflection.Assembly]LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null #Connect to the local SQL Server Instance, change the (local) parameter if needed $server = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)") Solution http//gallery.technet.microsoft.com/scriptcenter/Attach-all-the-databases-ace9ed34 #------------------------------------------------------------------------------------------------------ # Name Attach-AllDatabasesInDir # Description This script will attach all the databases in a directory to the local SQL Instance # Usage Run the function with the -DatabaseDir parameter # By Ivan Josipovic, softlanding.ca #------------------------------------------------------------------------------------------------------ #Do not modify below here function Attach-AllDatabasesInDir param( [string]$DatabaseDir = $(throw "-DatabaseDir `"C\ExampleDir`" is required.") ) #Load the SQL Assembly [System.Reflection.Assembly]LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null #Connect to the local SQL Server Instance, change the (local) parameter if needed $server = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)") if ($server -eq $null) Write-host -foreground red "Unable to connect to the SQL Servers" return -1 $items = get-childitem $DatabaseDir *.mdf foreach ($item in $items) [bool]$ErrorExists = $false $Item.name try $DBName = $server.DetachedDatabaseInfo($($item.fullname)).rows[0].value catch Write-host -foregroundcolor red "File was not able to be read. It is most likely already mounted or in use by another application" $ErrorExists = $true if ($ErrorExists -eq $false) foreach ($db in $server.databases) if ($db.name.Equals($DBName)) write-host -foreground red "This database already exists on the server" $ErrorExists = $true if ($ErrorExists -eq $false) $DbLocation = new-object System.Collections.Specialized.StringCollection $DbLocation.Add($item.fullname) $attach = $server.AttachDatabase($DBName, $DbLocation) return #------------------------------------------------------------------------------------------------------ Attach-AllDatabasesInDir -DatabaseDir "D\Data\SQLDat\MOSSData"