Restoring Sharepoint content database from backup

A small script to quickly restore a Sharepoint 2007 content database from backup:

USE master
GO

ALTER DATABASE SITE_WSS_Content
SET SINGLE_USER WITH
ROLLBACK AFTER 5
GO

RESTORE DATABASE SITE_WSS_Content
FROM DISK = 'path_to_backup\SITE_WSS_Content_backup_201201122000.bak'
WITH REPLACE
GO

ALTER DATABASE SITE_WSS_Content
SET MULTI_USER
GO

Listing subcategories for a category in Commerce Server 2007 using a SQL query

As promised, here is the follow-up to the last post. This query gets us the names of all subcategories of a particular category in Commerce Server 2007.

DECLARE @categoryOid AS INT
SET @categoryOid = 937 ;

WITH    Cats2 ( oid, child_oid, hierarchy_level )
          AS ( SELECT   oid ,
                        child_oid ,
                        1 AS hierarchy_level
               FROM     dbo.ConsumerShop_CatalogHierarchy
               WHERE    oid = @categoryOid
                        AND CatalogName = 'ConsumerStaging'
               UNION ALL
               SELECT   h.oid ,
                        h.child_oid ,
                        c.hierarchy_level + 1 AS hierarchy_level
               FROM     dbo.ConsumerShop_CatalogHierarchy h
                        JOIN Cats2 c ON h.oid = c.child_oid
                                        AND h.CatalogName = 'ConsumerStaging'
             )
    SELECT  CategoryName
    FROM    dbo.ConsumerShop_CatalogProducts
    WHERE   oid IN ( SELECT DISTINCT ( oid ) 
					 FROM   Cats2 )

Listing categories for a product in Commerce Server 2007 using a SQL query

Commerce Server API is not exactly known for being lightning-fast, so sometimes you want to skip it altogether and just use plain old SQL to get the data.

This time I needed to find all categories (at a particular level) that a product belongs to. This was solved by the script below, which is using a recursive CTE to walk upwards through the tree.

Just set the @productId and @hierarchyLevel below and voilá!

DECLARE @hierarchyLevel INT
DECLARE @productId VARCHAR(10)
SET @hierarchyLevel = 3
SET @productId = '25853' ;

WITH Cats (oid, child_oid, hierarchy_level) AS
	(
		SELECT h.oid, h.child_oid, 1 AS hierarchy_level
		FROM dbo.ConsumerShop_CatalogHierarchy h 
		JOIN dbo.ConsumerShop_CatalogProducts p ON h.child_oid = p.oid
		WHERE p.ProductID = @productId
		AND h.CatalogName = 'ConsumerStaging'

		UNION ALL 

		SELECT h.oid, h.child_oid, c.hierarchy_level + 1 AS hierarchy_level
		FROM dbo.ConsumerShop_CatalogHierarchy h
		JOIN Cats c ON c.oid = h.child_oid
		WHERE h.CatalogName = 'ConsumerStaging'
	)
    SELECT  CategoryName
    FROM    Cats
            JOIN dbo.ConsumerShop_CatalogProducts p ON Cats.oid = p.oid
    WHERE   hierarchy_level = @hierarchyLevel
            AND BaseCatalogName = 'ConsumerStaging'

In my next post I will explain how to accomplish the opposite, i.e. find all subcategories of a category.

Script for extracting SQL Server users, roles and role assignments

A nice little script to generate a script for extracting all users. roles and role memberships in a SQL Server database

select 'USE ' + db_name()
union all
select 'GO'

union all

SELECT 'CREATE USER [' + name + '] for login [' + name + ']'
 from sys.database_principals
 where Type = 'U'
  and name <> 'dbo'
union all
select 'GO'

union all

SELECT 'CREATE ROLE [' + name + ']'
FROM sys.database_principals
where type='R'
and is_fixed_role = 0
union all
select 'GO'

union all

SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + ''''
 from sys.database_principals users
  inner join sys.database_role_members link
   on link.member_principal_id = users.principal_id
  inner join sys.database_principals roles
   on roles.principal_id = link.role_principal_id
union all
select 'GO'