The TechNet documentation for deployment of software updates for SharePoint Server 2007 suggests, among other things, to detach your content databases in order to reduce downtime. This is easy enough to do through Central Admin when you have a few databases and a small number of web applications, but how about in a large farm with many web applications and dozens of content databases with multiple SQL Server Instances? PowerShell to the rescue!
First: Remove/Detach the Databases – Create a CSV file with value headings webapp and url where webapp is friendly name and url is the associated url for the web application. CSV example below:webapp,url
webApp1,http://webapp1.xyz.com
webApp2,http://webapp2.xyz.com
Use below PowerShell script to loop through all of your web applications using above CSV file, enumerate the content databases via STSADM, and throw the content database name and SQL Server Server/Instance name to a new CSV file named by webapp value. An internal loop reads from the new CSV file and detaches the databases (I commented line out below – uncomment when ready to run).
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
#loop through a csv file that contains webapp and url, run enumcontentdbs, export results to csv
Import-Csv .\webapps.csv | ForEach {
$strWA = $_.webapp
$strURL = $_.url
$rawdata=stsadm -o enumcontentdbs -url $strURL
$sitesxml=[XML]$rawdata
$sitesxml.databases.contentdatabase | Select-Object server,name | export-csv .\$strWA.csv -noType
#loop through contentDBs from csv created in above line and delete
Import-Csv .\$strWA.csv | ForEach {
$strServer = $_.Server
$strName = $_.Name
write-output WebApp: $strURL ContentDB: $strName
#WARNING BELOW COMMAND DANGEROUS -- UNCOMMENT WHEN READY -- WILL REMOVE EVERY CONTENT DB ON FARM
#$delDB = stsadm.exe -o deletecontentdb -url $strURL -databasename $strName
}
}
Second: Add the Databases back – After you’ve run all of your updates, you need to add your content databases back. Use below PowerShell script to loop through all of your web applications and read in the values from the CSV files created by running previous script to add the content databases back.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
#loop through webapps from original csv -- same as remove script
Import-Csv .\webapps.csv | ForEach {
$strWA = $_.webapp
$strURL = $_.url
$rawdata=stsadm -o enumcontentdbs -url $strURL
#loop through contentDBs and add based on csv created in the webAppDbs_Remove.ps1
Import-Csv .\$strWA.csv | ForEach {
$strServer = $_.Server
$strName = $_.Name
#write-output WebApp: $strURL ContentDB: $strName
$AddDB = stsadm.exe -o addcontentdb -url $strURL -databasename $strName -databaseserver $strServer -sitewarning 450 -sitemax 500
}
}Add the scipts in appropriately named powershell ps1 files (e.g., webAppDBs_Remove.ps1 and webAppDBs_Add.ps1) on an Application or Web Front End Server where Powershell is installed and call from a .bat file with commands similar to below:
powershell.exe Set-ExecutionPolicy RemoteSigned
REM command to run script from the ps1 file to remove
powershell.exe -noexit .\webAppDBs_Remove.ps1
REM command to run script from the ps1 file to add back
REM powershell.exe -noexit .\webAppDBs_Add.ps1
