A Bag of Databases

by sharepointbitme

A while ago a co-worker of mine had a little problem with attaching a LOT of databases to a customers web application. He needed to do it several times and wanted a way to easily get any errors produced as each database attached. I put together a Powershell script to do the job and it made the tedious job a lot easier.

Now I don’t think it is likely you will have exactly that same need, but it turns out that several parts of the script could be useful for a lot of things so let’s talk about some of the useful ideas.

Getting a list of items from a CSV file

To make life easier I set up the script so that it could pull the names of the databases from a common separated value (CSV) file. In this case there was only one value I wanted to put from the file, so there weren’t a lot of commas involved and the format of the file was simple, here is an example:

ContentDB  <-- The first line of the file contains column names.
WSS_Content_DB1
WSS_Content_DB2

Of course my co-worker was dealing with a couple of hundred files so the list was longer, but you get the idea. So here is how we used this file.

If you put the 3 lines above in a text file named InputDBs.txt in a folder on the C drive named MountData you can use it like this:

$SQLInstance = "SQLSERVER"
$WebApp = "http://DOMAIN.com"
$InputCSV = "C:MountDataInputDBs.txt"
$DBNames = Import-csv $inputCSV

## Mount each DB in InputCSV
ForEach ($item in $DBNames){
Mount-SPContentDatabase –Name $item.ContentDB –WebApplication $WebApp -DatabaseServer $SQLInstance -Outvariable mountoutput -errorvariable mounterrors

The first two variables are obvious, we are setting the SQLINSTANCE to a variable so we can easily change it to use another instance in the future and the $WebApp is the Web application we are connecting the database to. $InputCSV is just the path to our file that contains all of the database names. Once we have that file we can read items out of it with

Import-csv $inputCSV

and pump them into our other variable

$DBNames

that we use in the next line

ForEach ($item in $DBNames){

Each line (except the first, which contains the column name) of the file is an “item” so if there are 2 database names the process would run twice. The first item would result with this line:

Mount-SPContentDatabase –Name $item.ContentDB –WebApplication $WebApp -DatabaseServer $SQLInstance -Outvariable mountoutput -errorvariable mounterrors

Would run with these values

Mount-SPContentDatabase –Name WSS_Content_DB1 –WebApplication http://DOMAIN.com -DatabaseServer SQLSERVER -Outvariable mountoutput -errorvariable mounterrors

The second pass would have these values:

Mount-SPContentDatabase –Name WSS_Content_DB2 –WebApplication http://DOMAIN.com -DatabaseServer SQLSERVER -Outvariable mountoutput -errorvariable mounterrors

Because the first “item” in the ContentDB column was WSS_Content_DB1 and the second was WSS_Content_DB2. Are you seeing the possibilities yet? For example we could eliminate the $Webapp variable and add a WebApp in our CSV file to attach each database to a different web app like this:

ContentDB,WebApp  <-- The first line of the file contains column names.
WSS_Content_DB1,http://webapp.company.com
WSS_Content_DB2,http://webapp2.company.com

Mount-SPContentDatabase –Name $item.ContentDB –WebApplication $item.WebApp -DatabaseServer $SQLInstance -Outvariable mountoutput -errorvariable mounterrors

The same process can be used for lots of things, so get out there and find them.

Advertisements