Inside: STSADM MERGECONTENTDBS

For those of you that run in to this, it is MUCH FASTER and way more SQL friendly to use MERGECONTENTDBS instead of the BACKUP and RESTORE option WHEN MOVING FROM THE SAME DB SERVER AND SAME WEB APPLICATION.  Why?  Think of what a BACKUP and RESTORE do.  First, the BACKUP writes all the contents of the site to a file, that could be rather big.  Then, the RESTORE puts it back in, which blows up the transaction log as well.  So what does MERGECONTENTDBS do that is so much better?  ANSWER: Skips the whole file process…big improvement in speed and efficiency

Let’s assume the following scenario: 

Database moving from: DB1

Database moving to: DB2

Site GUID:  A63289FB-5404-4B17-BB49-4378EEF2CDB7

 

Steps in the SQL Profiler world…not exact but you get the point

–Lock the site in DB1

–Same as STSADM -o setsitelock -url site -lock noaccess

exec proc_SetSiteFlags ‘A63289FB-5404-4B17-BB49-4378EEF2CDB7′,131072,131072

 

–Run a bunch of INSERT INTO DB1.[dbo].[Table] (Col1,Col2,…) SELECT Col1, Col2,… FROM DB2.dbo.Table

Sites

ComMd

Deps

AllDocs

AllDocStreams

AllDocVersions

ContentTypes

EventReceivers

Features

ImmedSubscriptions

AllLinks

NavNodes

ScheduledWorkItems

SchedSubscriptions

Webs

Groups

GroupMembership

Roles

RoleAssignment

Workflow

WorkflowAssociation

Perms

RecycleBin

SiteVersions

NameValuePair

NameValuePair_Albanian_CI_AS

.

.

.

NameValuePair_Vietnamese_CI_AS

BuildDependencies

AllUserData

UserInfo

WebParts

Categories

AllLists

ContentTypeUsage

WebCat

WebMembers

WebPartLists

Personalization

 

exec proc_SetSiteFlags ‘A63289FB-5404-4B17-BB49-4378EEF2CDB7′,0,131072

exec proc_DeleteSite ‘A63289FB-5404-4B17-BB49-4378EEF2CDB7′

 

–Config DB

exec dbo.proc_dropSiteMap @Id=’A63289FB-5404-4B17-BB49-4378EEF2CDB7′

 

exec sp_executesql N’UPDATE [DB2].[dbo].[Sites] SET BitFlags = @Flag WHERE Id = @SiteId’,N’@SiteId uniqueidentifier,@Flag int’,@SiteId=’A63289FB-5404-4B17-BB49-4378EEF2CDB7′,@Flag=0

 

–Config DB

declare @p8 int

set @p8=1

exec dbo.proc_putSiteMap @ApplicationId=’A32D6D58-99F5-437F-BE32-3233D242BF91′,@DatabaseId=’DF61C0F9-1F4C-49B2-B1A9-99276320359E’,@SiteId=’A63289FB-5404-4B17-BB49-4378EEF2CDB7′,@Path=N’/sites/tester’,@Pairing=0,@RedirectUrl=NULL,@HostHeaderIsSiteName=0,@CurrentDatabaseSiteCount=@p8 output

select @p8

 

That is it…I know, not the prettiest thing, but SQL people get it and now hopefully so does everyone 

 

BUG: ACCESS DENIED. Adding/Removing Users/Groups

Ever get the error “Access Denied” while trying to add/remove a user/group from a SharePoint Site Collection? If you are like me and realize that the user you are logged in with has Site Admin priveleges, you are probably scratching your head like I was. If you are like me, those scratches left a mark on your bald head too! Enter bug in SharePoint.

If you have a Site Collection marked as “readonly” and it happens to be the last Site in the SharePoint table Sites, it will basically disable you from editing EVERY user/group in SharePoint that is within the same Content Database.

So how could someone figure this out? A lot of SQL skills help, and I gots em…

To replicate this problem, do the following:

1. Create a new Web Application and a new Content Database in Central Administration (WEB APP=http://test, Content Database=SP_TEST_CONTENT)
2. Create as many site collections as you want:
   1. “/” root Blank
   2. “/sites/T1″ Blank
   3. “/sites/T2″ Blank
3. Open up SQL Management Studio (SSMS) and run a query like this: SELECT  s.id,s.BitFlags,w.FullUrl FROM SP_TEST_CONTENT..Sites s JOIN SP_TEST_CONTENT..Webs w ON s.Id = w.SiteId
4. Take note to the one that is last, if it is “”, it is root.  Now, go to a command line and do the following: “stsadm -o setsitelock -url http://test/<Full URL from above> -lock readonly”
5. Go to any of the site collections that ARE NOT marked as read-only and try to add a user/group…ACCESS DENIED

So what is the deal?  SQL skills take over…  I opened up SQL Profiler and watched what was going on.  The one line that got me was calling “proc_SecAddUserToSiteGroup”.  Upon looking at this SPROC with “sp_helptext proc_SecAddUserToSiteGroup” I saw another call to “fn_CanUserEditMembershipOfGroup”.  After doing the same thing on this call, found a line like the following:

SELECT @SiteFlags = BitFlags FROM Sites

All in all valid SQL statement, however, one big gaping hole…uh….WHERE CLAUSE!!!!!!!!

After this call, it looks for IF @SiteFlags & 131072 = 131072 RETURN 0.  DOH…that pretty much mean ACCESS DENIED!!!!

Microsoft, please fix this bug…