Sunday, 2 September 2007

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!

1 comment:

  1. ưu điêm ghế văn phòng làm việc nên chú ý như thế nào để chọn mua sản phẩm phù hợp vàNhững ưu điểm ghế văn phòng làm việc
    Để chọn mua bàn làm việc nào cho phù hợp với văn phòng làm việc nên bạn nên quan tâm tớiChọn mua mẫu ghế văn phòng làm việc như thế nào
    Các tiêu chí chọn mua mãu nội thất văn phòng cho công ty doanh nghiệp bạn nên quan tâm tớitiêu chí chọn ghế văn phòng
    Ghế văn phòng làm việc nào phù hợp cho công tyvăn phong làm việc của bạn nên quan tâm toi cácCần nên chọn mua mẫu ghế văn phòng làm việc nào
    Mẫu ghês văn phong fnào phù hợp với nhu cầu làm việc tại văn phòng của bạn cho nên việcNên chọn mua mẫu ghế văn phòng nào
    Nội thát văn phòng phải phù hợp với cac tiêu chi nào cho văn phòng làm việc nên việcLựa chọn ghế văn phòng hợp với bạn

    ReplyDelete

Please feel free to comment on this post, I want to hear your feedback!