Deleting a SharePoint Portal Site with SQL Server Tools

By: Wes Stokes | Comments | Related: > SharePoint Administration

Problem

On very rare occasions, it is possible to receive an error when trying to delete a particular portal site with the SharePoint Central Administration application. The error will read something similar to "STS error receiving information from configuration database." As a result, you will not be able to remove the portal site or its corresponding databases through the SharePoint Central Administration application. To circumvent this error, one can delete the portal site using SQL Server Query Analyzer or SQL Server Management Studio. However, to do so is unsupported by Microsoft and EXTREMELY DANGEROUS TO SHAREPOINT IF DONE IMPROPERLY. This document recounts the successful procedures followed in a past development environment incident and outlines what should be done if it ever becomes necessary to delete a portal site with the SQL Server tools.

Solution

As a matter of background, in our development environment, SharePoint Portal Server v2.0 (SPS) is installed in what is referred to as a "Small Farm" consisting of a web server (SHRPNTSVR1) and a SQL Server backend (SQLSERVER1). When SPS is installed, a database is created on the SQL Server backend called SPS01_Config_DB. When an actual portal site is created, (3) databases on the SQL Server are additionally created. In this case, they are called Magic1_Site, Magic1_Prof, and Magic1_Serv. These (3) databases house information for a particular portal site on the SharePoint web server; in our case the MagIC development site, while the SPS01_Config_DB database contains "farm-wide" information (i.e. - what sites use which databases, virtual servers, etc.).

Relevant Information:

Portal Site Name: MagIC
URL: http://SHRPNTSVR1/
Corresponding Databases: Magic1_Site, Magic1_Prof, and Magic1_Serv
IIS Virtual Server Name: Default Web Site

Our group needed to delete the MagIC development site on SHRPNTSVR1 to make way for the restoration of a more updated version of the site, but we were unable to do so in the SharePoint Central Administration application. I decided to employ SQL Server 2000 Query Analyzer to delete the portal site by directly updating the configuration tables in the SPS01_Config_DB. A diagram of the (4) tables involved is shown below.

eletingaPortalSitewithSQLServerQueryAnalyzer1

1) First I determined what needed to be deleted by going to the databases table.

USE SPS01_Config_db 
GO  
SELECT * 
FROM dbo.Databases
GO

eletingaPortalSitewithSQLServerQueryAnalyzer2

From the results shown above, I learned the DatabaseIDs:

147B5955-2806-47CD-BD9E-096D12FDC449 = MagIC1_SITE
F495821B-6474-4A6A-BE9D-60121EFC6745 = MagIC1_SERV (ServiceDatabaseId)
0D04CD3E-B3DD-4C89-BC9D-BE6097F90B7D = MagIC1_PROF (UserProfileDatabaseId)

And the VirtualServerID:

AE85EF61-F8AA-48E7-979F-1FEC7FB04B42 = Default Web Site

2) Taking this knowledge to the PortalSites table, I knew which record to delete and checked for it before deletion.

SELECT * 
FROM dbo.PortalSites 
WHERE ServiceDatabaseId = 'F495821B-6474-4A6A-BE9D-60121EFC6745'
AND UserProfileDatabaseId = '0D04CD3E-B3DD-4C89-BC9D-BE6097F90B7D'
GO

eletingaPortalSitewithSQLServerQueryAnalyzer3

I then deleted the record in the PortalSites Table.

DELETE FROM dbo.PortalSites 
WHERE ServiceDatabaseId = 'F495821B-6474-4A6A-BE9D-60121EFC6745'
AND UserProfileDatabaseId = '0D04CD3E-B3DD-4C89-BC9D-BE6097F90B7D'
GO

3) Next, I went to the Sites table. I needed to determine which records needed to be deleted, knowing that the DatabaseID for the MagIC_SITE database was 147B5955-2806-47CD-BD9E-096D12FDC449.

SELECT *    
FROM dbo.Sites
WHERE DatabaseId LIKE '147B5955-2806-47CD-BD9E-096D12FDC449%'
GO

eletingaPortalSitewithSQLServerQueryAnalyzer4

I deleted all (9) of the sites with the corresponding DatabaseID.

DELETE FROM dbo.Sites 
WHERE DatabaseId LIKE '147B5955-2806-47CD-BD9E-096D12FDC449%'
GO

4) Updating the Databases table was my next issue. As seen in section 1, I knew the VirtualServerID for the default web site was AE85EF61-F8AA-48E7-979F-1FEC7FB04B42. From this, I could determine which records to delete in the Databases table. But before I deleted anything, I ensured that no other databases would be affected by this action.

SELECT *
FROM dbo.Databases
WHERE VirtualServerId = ‘AE85EF61-F8AA-48E7-979F-1FEC7FB04B42'
GO

The Name column in the results shows the (3) databases from section 1 that I wanted to disconnect from.

eletingaPortalSitewithSQLServerQueryAnalyzer5

Recognizing the proper databases and seeing that no others would be harmed, I deleted the (3) records in the Databases table.

DELETE FROM dbo.Databases
WHERE VirtualServerId = ‘AE85EF61-F8AA-48E7-979F-1FEC7FB04B42'
GO

5) Lastly, I went to the VirtualServers table and determined which records needed to be deleted.

SELECT *
FROM dbo.VirtualServers
WHERE [Name] LIKE 'default web site%'
GO

eletingaPortalSitewithSQLServerQueryAnalyzer6

There was (1) record where the VirtualServerID (AE85EF61-F8AA-48E7-979F-1FEC7FB04B42) and Virtual Server Name (Default Web Site) matched my information from Section 1 of this document. I deleted this lone record.

DELETE FROM dbo.VirtualServers
WHERE [Name] =  'default web site'
GO

Next Steps

The actions described here in sections 1-5 successfully removed the portal site from the SharePoint Server without affecting any other Portal Site on the server. This is NOT the preferred method of deleting a portal site and should only be used as an alternative to using the SharePoint Portal Server Central Administration application.