SharePoint 2010 Farm configuration backup and Clean up Script

I have to take the backup of the farm while excluding the Web Applicaitons and schedule it each night. Also, part of backup strategy do includes cleaning the old backups.

Have written the script to backup the farm configurations (Intimate farm admin with Email for success and failure) and clean old backup folder. Scripts could be downloaded from below mentioned location
https://sites.google.com/site/mossforsurecontent/content-for-moss-for-sure/backupscript
These Powershell could be scheduled through task scheduler for creating farm configuration backup and delete the old one based on Retention period.

SharePoint 2010 SP1 released.

SharePiont 2010 SP1 Released. Please follow links to get details:
http://support.microsoft.com/kb/2460045
Key areas of improvement:

• Improve support of IE9


• Recycle Bin could now be restored from Site Colleciton or Web that has been deleted.


• RBS and shallow copy features to improve efficiency by moving pointers to database instead of moving databases.


• Folders storage details on SharePoint site.


• Support for MS SQL 2011.


• More Robust search host distribution service that improves error recovery and performance during search crawl.

SharePoint update details could be found at.

http://technet.microsoft.com/en-us/sharepoint/ff800847.aspx

Some Useful Sharepoint 2007 Content Database queries

1- Query to find all form libraries in Sharepoint Farm

SELECT w.Title, w.FullUrl, AllLists.tp_Title
FROM AllLists INNER JOIN
Webs AS w ON AllLists.tp_WebId = w.Id
WHERE (AllLists.tp_ContentTypes LIKE '%Forms/Form%')
ORDER BY w.Title

2- Query to find list with specific content type – Content type is given in where clause
SELECT w.FullUrl, w.Title as Sitename ,A.tp_Title as Listname
FROM AllLists AS A INNER JOIN
Webs AS w ON A.tp_WebId = w.Id
WHERE (A.tp_ContentTypes LIKE '%PowerPoint%')
ORDER BY w.Title

3- Query to find access request detail on each site-

SELECT Title, FullUrl, RequestAccessEmail
FROM Webs
ORDER BY Title

4- Query to give list of alerts on a site – SiteUrl given in where clause

--- List Immediate Alert------

SELECT u.tp_login,u.tp_title,i.UserEmail, i.WebUrl, i.WebTitle, i.ListUrl, i.ListTitle, i.AlertTitle, i.Filter,i.properties
FROM userinfo u , ImmedSubscriptions i
WHERE (i.siteid=u.tp_siteid)and (u.tp_email=i.useremail)and (i.WebUrl LIKE '%Site Url as parameter %')and i.useremail<>' '
order by i.webtitle



--- List Daily/weekly Alert------


SELECT u.tp_login , u.tp_title,(select case notifyfreq when 1 then 'Daily'when 2 then 'Weekly' end ) as NotifyFrequency, s.useremail,s.weburl,s.webtitle , s.listurl,s.listtitle, s.alerttitle ,s.filter,s.properties FROM SchedSubscriptions s , userinfo u
WHERE ( s.useremail=u.tp_email)and (s.siteid=u.tp_siteid) and (s.WebUrl LIKE '%/Site Url as parameter %')
ORDER BY u.tp_login

5- Query to know list/libraries not inheriting permissions from parent site – Site Url given in where clause

Select distinct w.fullurl,w.title,al.tp_title,r.title
from webs w, alllists al ,roleassignment ra, roles r
where al.tp_hasfgp=1 and w.id=al.tp_webid and al.tp_scopeid=ra.scopeid and r.roleid=ra.roleid and w.fullurl like '%Site Url as parameter %'
order by w.fullurl
6- Query to find all workflows in Site collection-

Select distinct(w.fullurl),L.tp_Title from webs w,workflow wf , Alllists L where w.id=wf.webid and w.siteId=wf.siteId and wf.listId=L.tp_Id order by fullurl


7- Query to find all checked out document on sharepoint site-Site url is given in where clause

Select distinct d.dirname, d.leafname, d.version,d.extension, d.checkoutuserid, d.ischeckouttolocal ,u.tp_title from Docs D, USerInfo U where (d.checkoutuserid is not null) and (d.checkoutuserid=u.tp_ID) and d.siteID=u.tp_SIteID and d.iscurrentversion=0 and d.dirname like 'Site Url as parameter ' order by dirname


--To find Checked out document during specific time period--- dates need to be given in where clause
Select distinct d.dirname, d.leafname, d.version,d.extension, d.checkoutuserid, d.checkoutdate ,u.tp_title from Docs D, USerInfo U where (d.checkoutuserid is not null) and (d.checkoutuserid=u.tp_ID) and d.siteID=u.tp_SIteID and d.iscurrentversion=0 order by dirname
d.checkoutdate between to_date ('2009/06/15', 'yyyy/mm/dd')
AND to_date ('2009/06/22', 'yyyy/mm/dd')
8- Query to find user permissions on the list within the site

User Id need to be given as parameter
select w.fullurl, u.tp_login,u.tp_title,R.Title , al.tp_title
from webs w, UserInfo u, RoleAssignment RA, Roles R,Perms P , Alllists al
where w.id=al.tp_webid and al.tp_webid=p.WebID and al.tp_webid=r.webid
and al.tp_scopeid=ra.scopeid and w.id=p.WebID and
w.id=r.webid and r.webid=p.webid and ra.scopeid=P.scopeid
and u.tp_id=ra.principalid and r.roleid=ra.roleid and u.tp_login like 'User Id as a parameter'

9- Query to find permission roles on list in the Sharepoint site

Select distinct d.dirname, d.leafname, d.version,d.extension, d.checkoutuserid, d.ischeckouttolocal ,u.tp_title from Docs D, USerInfo U where (d.checkoutuserid is not null) and (d.checkoutuserid=u.tp_ID) and d.siteID=u.tp_SIteID and d.iscurrentversion=0 and d.dirname like 'Site Url as parameter ' order by dirname

How to make common fields of two form share the column in same Infopath Form Library

1- Create two forms with common fields in design view. Example Form1 with field txta, txtb and Form2 with field txta , txtc.
2- Publish both of them as Site Content Type on the site and store the form templates in a document library. This step will also create the site columns specific to items within the form.
3- Create a form library which needs to share the columns between two forms. Allow management of content types for this form library.
4- Attach both Forms as content type in this form library.
5- Now publish the second form (Form2) to this existing form library as template to the document library. It will wipe out the Form1 specific columns (txtb) from the first content type form.
6- Now add the missing column from existing site columns.
7- Give the proper reference of first content type from existing document library.

Discovering all workflows in shareoint farm.

I got a question from user to identify all workflow in my Sharepoint environment. I tried to identify some out of box option but could not find one. After that I have to put my DB cap on and write query on backend content database of MOSS farm to get list all sites and sub containers which are having active workflow running in them. Here is the query :

select distinct(w.fullurl) as SiteName, L.tp_Title as ListName from webs w,workflow wf , Alllists L where w.id=wf.webid and w.siteId=wf.siteId and wf.listId=L.tp_Id

Another query to get list of all sites and subsites and there approximate data size in sharepoint farm through content database is.

select w.fullurl as URL,d.webId as WebID,sum(d.size/1024) as Size
from docs as d ,webs as w
where d.webId=w.Id
group by d.webId ,w.fullurl
order by w.fullurl

Publishing Site Template in MOSS 2007

Last week I had a hard time tracking an issue which at last found out to be database goof up. We had MOSS 2007 hosted on VM environment. 'Icing on Cake' is that even Database server is on VM with 300 Gigs of Single Content DB. We were installing infrastructure update in this farm when panick struck. MOSS site do not come up at all. At DB side, DBA informed that there were excessive locking going in the background and if they kill that Job locking stops and site come up almost immediately. On detailed investigation it was found that sub procedure proc_DeleteUrl() was locking the db. On further testing it was revealed that Creating Publishing site trigger this action. After working with Microsoft Support it was identified that DB's were not performing well and maintenance of DB's were creating issues. We ran update statistics command on tables to correct this issue. Details of commands are as follows.

UPDATE STATISTICS ALLDocs WITH FULLSCAN
UPDATE STATISTICS AllUserData WITH FULLSCAN
UPDATE STATISTICS WebPartLists WITH FULLSCAN
UPDATE STATISTICS WebParts WITH FULLSCAN
UPDATE STATISTICS AllDocVersions WITH FULLSCAN
UPDATE STATISTICS AllUserDataJunctions WITH FULLSCAN
UPDATE STATISTICS AllDocStreams WITH FULLSCAN
UPDATE STATISTICS BuildDependencies WITH FULLSCAN
UPDATE STATISTICS AllLinks WITH FULLSCAN
UPDATE STATISTICS RoleAssignment WITH FULLSCAN

MOSS 2007 Table Structure

I always thought of to see table structure of MOSS Databases so as to run some basic queries. Although I know very well that touching DB is not safe enough as it make Microsoft Support Untouchable but still sometime you need to take those chances. I have compiled list of table and tablestructure for all important databases(ConfigDB,ContentDB,SharedServicesDB,SharedSerivicesSearchDB) in MOSS 2007.http://sites.google.com/site/mossforsurecontent/content-for-moss-for-sure/moss-for-sure-content/MOSSDatabaseTableStructure.xls?attredirects=0. More details of Configuration and Content Databases is provided at http://msdn.microsoft.com/en-us/library/ms998452.aspx