Finding sites in WSS2.0 content DB's

Ever had the problem where you have multiple content databases for a given web application in WSS and need to find which one has the site you’re after?

Probably not, I’d only had the odd one until recently, so I wrote the following script to help out…

This works only where you have single SQL server with content databases, if you have content databases across multiple SQL servers you’ll need to modify this script slightly…

Font is quite small to fit it all on one screen!! - Simply copy-paste into your favourite script editor (I like PrimalScript)...

** Start Script **

'declare
Dim debugmode
' read CommandLine args into vars
' no validation here so they must all be complete
For Each oArg In WScript.Arguments
  sSwitch = LCase(Left(oArg,3))
  sValue = Right(oArg,Len(oArg)-3)
  Select Case sSwitch
    Case "-x:"
        Debugoption = sValue 
    Case "-y:"
        SQLServer = sValue
    Case "-z:"
        ConfigDB = sValue
    Case "-s:"
        SiteSearch = SValue
  End Select
Next
'Debug Option
'error and bomb out if not set
If Debugoption = "debugon" Then
        debugmode = 1
        MsgBox "Debugging activated!", vbOKOnly Or vbExclamation
        MsgBox "SQLServer = " & SQLServer, vbOKOnly Or vbExclamation
        MsgBox "ConfigDB = " & ConfigDB, vbOKOnly Or vbExclamation
        MsgBox "SiteSearch = " & SiteSearch, vbOKOnly Or vbExclamation
Elseif Debugoption = "debugoff" Then
        debugmode = 0
Else
        MsgBox "Debugoption not specified, please set using:" & (Chr(13)) & "'sitebackup.vbs -x:debugon' for ON" & (Chr(13)) & "'sitebackup.vbs -x:debugoff' for OFF" & (Chr(13)) & "this utility will do nothing without the correct command line arguments!"  & (Chr(13)) & (Chr(13)) & "Matt is a lazy coder!", vbOKOnly Or vbCritical, "BIG FAT ERROR"
        WScript.Quit
End if
If SQLServer = "" Then
        'error out
        MsgBox "SQL Server not specified, please set using:" & (Chr(13)) & "'sitebackup.vbs -y:SERVERNAME'" & (Chr(13)) & "This utility will do nothing without the correct command line arguments!"  & (Chr(13)) & (Chr(13)) & "Matt is a lazy coder!", vbOKOnly Or vbCritical, "BIG FAT ERROR"
        WScript.Quit
End If
If ConfigDB = "" Then
        'error
        MsgBox "ConfigDB not specified, please set using:" & (Chr(13)) & "'sitebackup.vbs -z:DATABASENAME'" & (Chr(13)) & "This utility will do nothing without the correct command line arguments!"  & (Chr(13)) & (Chr(13)) & "Matt is a lazy coder!", vbOKOnly Or vbCritical, "BIG FAT ERROR"
        WScript.Quit
End If
If SiteSearch = "" Then
        'error
        MsgBox "Site URL not specified, please set using:" & (Chr(13)) & "'sitebackup.vbs -s:http://server/sites/site'" & (Chr(13)) & "This utility will do nothing without the correct command line arguments!"  & (Chr(13)) & (Chr(13)) & "Matt is a lazy coder!", vbOKOnly Or vbCritical, "BIG FAT ERROR"
        WScript.Quit
End If
filedatestamp = "Y" & year(now()) & "-M" & month(now()) & "-D" & day(now()) & "-HH" & hour(now()) & "-MM" & minute(now())
' SET CONSTS
baseDir = BakDrive & BakDir
SQLConString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & ConfigDB & ";Data Source=" & SQLServer
If Debugmode = 1 Then
        'MsgBox "baseDir = " & baseDir & (Chr(13)), vbOKOnly Or vbExclamation
        MsgBox "sql con string = " & SQLConString & (Chr(13)), vbOKOnly Or vbExclamation
End If
' CONNECT TO DB
Dim WSSCon
Set WSSCon = CreateObject("ADODB.Connection")
WSSCon.Open SQLConString
'create record set
set rsWSSDB = CreateObject("ADODB.recordset")
WSSDBsql="select Name from DATABASES order by Name"
rsWSSDB.open WSSDBsql, SQLConString
'loop through each contant DB
Dim NOval
Dim YESval
NOval = 0
YESval = 0
while NOT rsWSSDB.EOF
        set WSSSiteRS = CreateObject("ADODB.recordset")
        WSSSiteCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & rsWSSDB("Name") & ";Data Source=" & SQLSERVER
        'WSSSitessql="select fullUrl from SITES WHERE FullURL = '" & SiteSearch & "' order by fullUrl"
        'WSSSiteRS.Open WSSSitessql, WSSSiteCon
        'count records
        If Debugmode = 1 Then           
                MsgBox "WSSSiteCon = " & WSSSiteCon & (Chr(13)), vbOKOnly Or vbExclamation
        End If
        set cNumWSS = CreateObject("ADODB.recordset")
        NumWSSsql="select count(*) as TotTeamSites from SITES WHERE FullURL = '" & SiteSearch & "'"
        If Debugmode = 1 Then           
                MsgBox "Couting Query= " & NumWSSsql & (Chr(13)), vbOKOnly Or vbExclamation
        End If
        cNumWSS.open NumWSSsql, WSSSiteCon
        If Debugmode = 1 Then
                MsgBox "number of sites found = " & cNumWSS("TotTeamSites") & (Chr(13)), vbOKOnly Or vbExclamation
        End If
        If cNumWSS("TotTeamSites") = 0 Then
                NOval = (NOval + 1)
        End If
        If cNumWSS("TotTeamSites") >0 Then
                YESval = (YESval + 1)
                MsgBox "I found it!!" & (Chr(13)) & "It's in: " & rsWSSDB("Name") & (Chr(13)), vbOKOnly Or vbExclamation
        End If
rsWSSDB.MoveNext
wend
If YESVal = 0 Then
        MsgBox "I didn't find it!!" & (Chr(13)) & "I checked " & NOVal & " content databases" & (Chr(13)) & "You searched for: " & SiteSearch & (Chr(13)) & "Did you remember the trailing backslash?", vbOKOnly Or vbExclamation
End If
WSSCon.Close
cNumWSS.close 

** End Script **

 

Call the script from a shortcut or command line, like:

 

C:\path\script.vbs -x:debugon -y:MYSERVER -z:MyConfigDB -s:http://server/sites/sitename/

 

You must include the trailing backslash!