SQL Scripting for Manageability

The attached SQL scripts are intended as sample scripts and may require some modification to suit your individual environments; however, serve as a foundation and guidance in SQL scripting under Windows SharePoint Services 3.0.

Retrieve active Content Databases from a WSS 3.0 Configuration Database

  • Returns a list of all active Content Databases in the Windows SharePoint Services Configuration Database.

USE <WSS_3.0_Configuration_Database>
SELECT DISTINCT SiteMap.DatabaseId, Objects.Name
FROM SiteMap
ON SiteMap.DatabaseId=Objects.Id

Get Site Collections in Content Databases not in Configuration Database

  • Returns all Site Collections in a Content Database using the Windows SharePoint Services 3.0 Configuration Database. Add additional Content Databases as required.

SELECT * FROM [<WSS_3.0_Configuration_Database>].dbo.SiteMap WHERE ApplicationID = ‘670181FD-E7AE-4230-A0B0-9261092D7A4C’ AND Id NOT IN (
(SELECT ID FROM[<SQL_Server_Instance>].<WSS_3.0_Content_Database>.dbo.Sites)
UNION (SELECT ID FROM [<SQL_Server_Instance>]. <WSS_3.0_Content_Database>.dbo.Sites)

Get Content Database and Database Server Information

  • Returns Content Database and associated Database Server information.

SELECT SQLServers.Name AS SQLServerName, SQLInstances.Name AS SQLInstanceName, DBs.Name AS DBName
FROM Objects AS DBs
INNER JOIN Objects AS SQLInstances
ON DBs.ParentId = SQLInstances.Id
INNER JOIN Objects AS SQLServers
ON SQLInstances.ParentId = SQLServers.Id
ON SQLInstances.ClassId = Classes.Id
WHERE Classes.Fullname LIKE ‘Microsoft.SharePoint.Administration.SPDatabaseServiceInstance%’

Get Site Collection Count from WSS 3.0 Content Database

  • Returns the Site Collection count from a Windows SharePoint Services 3.0 Content Database.

USE <WSS_3.0_Configuration_Database>
SELECT * FROM Sitemap s
INNER JOIN Objects o
ON s.DatabaseId=o.Id
WHERE o.Name LIKE ‘<WSS_3.0_Content_Database>%’

Get Site Collection Mapping Information

  • Maps a Windows SharePoint Services 3.0 Site Collection to its host Content Database using the Windows SharePoint Services 3.0 Configuration Database.

USE <WSS_3.0_Configuration_Database>
SELECT a.Path, a.Id, b.Name, c.Name
FROM <WSS_3.0_Configuration_Database>..SiteMap AS a INNER JOIN
<WSS_3.0_Configuration_Database>..Objects AS b ON a.DatabaseId = b.Id INNER JOIN
<WSS_3.0_Configuration_Database>..Objects AS c ON b.ParentId=c.Id
WHERE a.ApplicationId = ‘670181FD-E7AE-4230-A0B0-9261092D7A4C’

Compare WSS 2.0 and WSS 3.0 Content Databases [Useful in Gradual Upgrade Mode]

  • Compares Windows SharePoint Services 2.0 and Windows SharePoint Services 3.0 Content Databases returning the variance.

USE [<WSS_3.0_Configuration_Database>]
DECLARE @pairname NVARCHAR(100)
DECLARE @databasename NVARCHAR(100)
SELECT @pairname = ‘<WSS_3.0_Content_Database>’
SELECT @dbid = (SELECT [Id] FROM DBO.objects WHERE [Name] = @pairname)
FROM [<SQL_Server_Instance>].<WSS_2.0_Content_Database>.DBO.Sites t1
(SELECT t2.[Path] FROM DBO.SiteMap t2 WHERE t2.DatabaseId = @dbid
AND t2.[Path] = REPLACE(t1.FullUrl,’

Locate a specific Site Collection using the WSS 3.0 Configuration Database

  • Locates a specific Site Collection in the Windows SharePoint Services 3.0 Configuration Database.

USE <WSS_3.0_Configuration_Database>
JOIN Objects o ON s.DatabaseId = o.Id
AND s.Path LIKE ‘%<Site_Collection_Path>%’

Site Collection and subsite Backup, Restore, and Migration

I received an inquiry this week asking how to migrate an existing Site Collection as a subsite of a new parent Site Collection without having the availability of SMIGRATE in Microsoft Office SharePoint Server 2007/Windows SharePoint Services 3.0. The answer are new STSADM parameters, import and export, which has been made available in STSADM. The import/export feature is based on the new Content Migration APIs; Recycle Bin state and alerts are not included in STSADM -o export – the requirement that the new Site Collection or subsite exists, remains as with previous versions of STSADM when using the restore parameters. A benefit of the export parameter is that security settings will be included if desired; whereas, in SMIGRATE WSSUserUtil was required to capture and import web security. Sample syntax of the new parameters follows:

stsadm -help export
  [stsadm -o export -url URL -f filename]
stsadm -help import
  [stsadm -o import -url URL -includesecurity]

For more information on Content and site migration visit http://technet2.microsoft.com/Office/en-us/library/16a7e571-3531-4a4e-baa7-f348a9f9d1d11033.mspx?mfr=true

Whitepaper: Understanding Workflow in Windows SharePoint Services and the 2007 Microsoft Office System

Of the many new features available in both Microsoft Office SharePoint Server 2007 and Windows SharePoint Services 3.0; the questions I most commonly field surround workflow; deployment, implementing, best practices, etc…  The attached whitepaper serves as an excellent resource to answer many of these questions.

Understanding Workflow in Windows SharePoint Services and the 2007 Microsoft Office System

SharePoint Community Search by Lawrence Liu

Lawrence Liu recently posted his Live Search macro enabling a consolidated search against the SharePoint Community; to add to that capacity I’ve put together a small bookmarklet that will enable you to run this search from your browser without having to navigate away from your current page.


Best Practices: Are your DIPs alive? Alternate Access Mapping may be the answer…

Historically alternate access settings provided a mechanism to identify the different ways in which users access portal sites; in MSIT we’ve taken that practice one step further and leverage alternate access mappings (AAM) as a means to identify problem web front-ends in a NLB cluster. As an example an NLB (network load balancing) cluster may be bound to a VIP (virtual IP address) with a mapping of foo, where the hosts are foo1 and foo2, by leveraging alternate access mappings we can DIP test foo1 and foo2 both independently and through the Microsoft Operations Manager (MOM) Web Sites and Services Management Pack. The basic rules of AAM are that every alternate access setting entry must have a default URL, at which point each can have additional alternate acccess methods for either intranet, extranet, or custom access. Each URL though must differ from all other URLs. These mappings are stored in the configuration database. Microsoft Office SharePoint Server 2007 uses the default URL for any requested URL that is not found in the mapping table.

For more information about Alternate Portal Access Settings in Microsoft Office SharePoint Portal Server 2003 visit http://office.microsoft.com/en-us/assistance/HA011603021033.aspx.