Full backup of Moss with Cleanup of older backups

I was facing an issue recently with one of my moss servers that was filling up my backup location disk space with the full backups of MOSS. Apparently you can use stsadm to create a full backup to a UNC path, but there isn’t an option to cleanup those backups. Shame on you Microsoft [:D]. The only corrext way to cleanup the backup is to manually edit the spbrtoc.xml file on your backup location and remove the backup files manually after that.

Being an Administrator for all sorts of systems, I really love to automate such tasks and my make life a lot easier.

 So here is what you need to do to have full MOSS backups and have a cleanup of older backups.

On the server where you want to run the backup (any one of your moss servers) do the following:

1. Download and install Powershell (if not installed already [H])

2. start powershell in a command prompt: type powershell.exe

3. Execute the command: Set-ExecutionPolicy Unrestricted. This will enable you to run unsigned ps1 scripts. unless you are a guru in powershell and know how to sign your scripts with a certificate and all.

4. Create a new text file with notepad, paste the following adn save the file to a location like C:\backup\cleanbackups.ps1:

# Clean-up (old) backup files created by WSS v3 (STSADM.EXE)
# Created by Marco
# Tested with PowerShell RTM v1.0

# Location of spbrtoc.xml
$spbrtoc = “\\server1\backup\Sharepoint\spbrtoc.xml”

# Days of backup that will be remaining after backup cleanup.
$days = 14

# Import the Sharepoint backup report xml file
$sp = gc $spbrtoc

# Find the old backups in spbrtoc.xml
$old = $sp.SPBackupRestoreHistory.SPHistoryObject | ? { $_.SPStartTime -lt ((get-date).adddays(-$days)) }
if ($old -eq $Null) { write-host “No reports of backups older than $days days found in spbrtoc.xml.`nspbrtoc.xml isn’t changed and no files are removed.`n” ; break}

# Delete the old backups from the Sharepoint backup report xml file
$old | % { $sp.SPBackupRestoreHistory.RemoveChild($_) }

# Delete the physical folders in which the old backups were located
$old | % { Remove-Item $_.SPBackupDirectory -Recurse }

# Save the new Sharepoint backup report xml file
Write-host “Backup(s) entries older than $days days are removed from spbrtoc.xml and harddisc.”

 I found this script on the follwing link: http://blog.wauwwie.nl/Lists/Categories/Category.aspx?Name=MOSS%202007%20%2F%20WSS%20v.3

5. Now create a batch file on your moss system with the following commands:

stadm -o backup -directory \\server1\backup -backupmethod full -quiet
powershell.exe C:\backup\cleanbackups.ps1

Save the file and schedule it.

And there you have it.

TweakSP from CodePlex

Just wandering around my favorite blogs, when I came across an article describing this new tool at codeplex called TweakSP. I gave it a spin om my demo environment. Although the project is still in a start phase, the idea seams promising. You can download a wsp file that, when deployed, will add an extra tab to your Central Admin site.


Hopefully, they will add a lot more functionality as the project progresses.

It is definitely worth keeping an eye on this project. I know I will [H]


Rename Template "Custom" tab in SharePoint 2007

I was recently wondering if you could rename  that “Custom” tab when creating a new site that holds your custom site templates. Well, it turns out that you can. After searching a while on the net, i came across a post with the following information:

In SharePoint 2007, any custom templates added through the stsadm command stdadm -o addtemplate appear under the “Custom” tab.

This may not appear sufficiently corporate for some people. One easy fix is to simply modify the locale file at C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\Resources\core.en-US.resx. Just find

  <!–TemplatePickerControl –>
<data name=”TemplatePickerControl_Custom”>

and modify <value> to anything you like.

Pulling site collections details via direct SQL query by Josef Nielsen

This article is a direct grab from the site http://www.sharepointblogs.com/echef/archive/2007/10/09/pulling-site-collections-via-direct-sql-query.aspx

I certainly don’t recommend making changes to your content DBs directly in SQL, but there’s no harm to be had pulling data from the DB’s.  I found that this was the easiest way to poll data about all farm content DBs and Web Apps listed by Site Collection.  Hope you find this useful!

/* SiteReport.sql

written by Josef Nielsen

September 2007 


NOTE: You must create a linked server if you use multiple SQL server to house you content DBs




DECLARE @ts1 varchar(1000), @ConfigDB VARCHAR(128) 


– Set your Config DB Name here if it is different

SET @ConfigDB = ‘SharePoint_Config’ 


– This creates a temp table to hold the list of content DBs referenced by the Config DB



      DBname VARCHAR(128),

      DBInstance VARCHAR(128),

      DBServer VARCHAR(128)



– Populate the temp table with content DBs

SET @ts1 = ‘INSERT INTO #TempDbList

                  SELECT [DbName].[Name] AS ”DatabaseName”,

                        [Instance].[Name] AS ”DatabaseInstance”,

                        [Server].[Name] AS ”DatabaseServer”

                  FROM ‘+‘['+@ConfigDB+']‘+‘.[dbo].[Objects] AS [DbName]

                        LEFT JOIN ‘+‘['+@ConfigDB+']‘+‘.[dbo].[Objects] AS [Instance]

                              ON [DbName].[ParentId] = [Instance].[ID]

                        LEFT JOIN ‘+‘['+@ConfigDB+']‘+‘.[dbo].[Objects] AS [Server]

                              ON [Instance].[ParentId] = [Server].[Id]

                  WHERE [DbName].[Properties] LIKE ”%SPContentDatabase%”

                        AND [DbName].[Properties] NOT LIKE ”%WebApplication%”’

EXEC (@ts1) 




–This creates a temp table to hold the end results of the Site Collection lists from all Content DBs

CREATE TABLE [#TempSiteList](

    FullURL VARCHAR(128),

    WebApp VARCHAR(128),

    DBServer VARCHAR(128),

    DBName VARCHAR(128),

    Megs BIGINT,

    Quotamax VARCHAR(128),

    Quotawarning VARCHAR(128),

    Userquota VARCHAR(128),

    Siteowner VARCHAR(128),

    OwnerEmail VARCHAR(128),

    Sitecreationdate VARCHAR(128),

    Lastcontentchange VARCHAR(128)



– Create a cursor to walk through each content DB



            SELECT [DBServer], [DBInstance], [DBName]

             FROM [#TempDbList] 

OPEN DB_Cursor




FETCH NEXT FROM DB_cursor INTO @vDBServer, @vDBInstance, @vDBName



      DECLARE @DBv1 VARCHAR(2000) 


– Add a backslash for DBServers that are not default instances

      DECLARE @slash VARCHAR(128)

      IF @vDBInstance =

            SET @slash =


            SET @slash = ‘\’ 


– Script to insert Site Collection details to the temp site summery table

      SET @DBv1 = ‘INSERT INTO [#TempSiteList]

            SELECT [Webs].[FullUrl],

            [ConfigObjects].[Name] AS ”WebApp”,

            (SELECT ”’ + @vDBServer+@slash+@vDBInstance + ”’) AS ”SQL Server”,

            (SELECT ”’ + @vDBName + ”’) AS ”Content DB Name”,

            (([Sites].[diskused])/1024)/1024 AS ”Megs”,

            (([Sites].[diskquota])/1024)/1024 AS ”Quota max”,

            (([Sites].[diskwarning])/1024)/1024 AS ”Quota warning”,

            (([Sites].[userquota])/1024)/1024 AS ”User Quota”,

            [User].[tp_login] AS ”Site Owner”,

            [User].[tp_email] AS ”Owner E-mail”,

            CAST([Sites].[timecreated] AS char(30)) AS ”Site Creation Date”,

            CAST([Sites].[lastcontentchange] AS char(30)) AS ”Last Content Change”


            ['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].[dbo].[sites] AS [Sites] WITH (NOLOCK)

            LEFT JOIN ['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].[dbo].[webs] AS [Webs] WITH (NOLOCK) ON [Webs].[siteID] = [Sites].[Id]

            LEFT JOIN ['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].[dbo].[userinfo] AS [User]  WITH (NOLOCK) ON [User].[tp_SiteID] = [Sites].[Id]

            LEFT JOIN ‘+‘['+@ConfigDB+']‘+‘.[dbo].[SiteMap] AS [ConfigSiteMap]  WITH (NOLOCK) ON [ConfigSiteMap].[Id] = [Sites].[Id]

            LEFT JOIN ‘+‘['+@ConfigDB+']‘+‘.[dbo].[Objects] AS [ConfigObjects]  WITH (NOLOCK) ON [ConfigSiteMap].[ApplicationID] = [ConfigObjects].[Id]


            [Webs].[ParentWebId] IS NULL

            –AND [User].[tp_SiteID] = [Sites].[Id]

            AND [User].[tp_SiteAdmin] = 1

            AND [User].[tp_id] = 1′

      EXEC (@DBv1)


FETCH NEXT FROM DB_cursor INTO @vDBServer, @vDBInstance, @vDBName 




CLOSE DB_cursor 





– Cursor is closed and released, ad now we select the results of the scan

SELECT * FROM #TempSiteList ORDER BY [WebApp], [FullURL] GO 


– Clean up to get rid of those temp tables

DROP TABLE [#TempDbList]

DROP TABLE [#TempSiteList] 

SharePoint Manager 2007

You definitely need this tool if you are even serious about using SharePoint.

You can find it @ http://www.codeplex.com/spm

The SharePoint Manager 2007 is a SharePoint object model explorer. It enables you to browse every site on the local farm and view every property. It also enables you to change the properties (at your own risk). This is a very powerfull tool for developers that like to know what the SharePoint holds of secrets.

As a Sharepoint Administrator I am using this tool for getting a lot of information out of the farm that is not quite visible in Central Admin.

One of the coolest things I did with it was setting a Site collection to read only by just setting a simple dropdown box to the value true and wham the site is readonly! Try it out yourself.

It is slow on loading howevr, probably due to the fact that it needs to collect all that information first. So be a little patient when loading. Also it is a single executable file that does not require any additionale software installed on your MOSS / WSS servers

SharePoint Sushi

No, you cannot eat it and it does not come from the sea!

When browsing through the projects on Codeplex, I stumbled upon this nice tool called SharePoint Sushi. I gave it a spin and it looks quite promising.

You can find it @ http://www.codeplex.com/sushi

SUSHI is a powerful, user-friendly SharePoint application enabling you to accomplish common SharePoint administrative and development tasks.
What does the name SUSHI stand for?
SUSHI = SharePoint Utility with a Smart, Helpful Interface

SUSHI Features

  • Copy View Copy a view from one SharePoint list to another.
  • Security Reports View all sites and lists a user has access to.
  • Import Documents Upload entire file structures from a file share into a document library.
  • Meta Data Get detailed information about metadata in a list. Move metadata from one column to another for all items in a list. And more.
  • Backup Backup a site.
  • Restore Restore site from backup.
  • Profile Images Import Upload Profile Images and Apply them to User Profiles.
  • Delete Old Documents Delete documents older than a given date. This feature can also be used to copy all files and folders of a document library to a file share.
  • Bulk Site Creation Create a large number of subsites based on a site template.
  • Bulk Webpart Customization If you have lots of sites with the same webpart(s) and want to make changes to all of them, this tool can help you customize all the webparts at once.
  • Help & Settings We said SUSHI was helpful, here is the proof.
  • Send a Test Email

SUSHI was built and is maintained by Joseph Fluckiger, Senior SharePoint Consultant at Catapult Systems

Rename your SharePoint_AdminContent_guid database with stsadm mergecontentdbs operation


 just a quick post to give you the commands to rename your Database on your SQL box of the Central Administration site. This is the only database that you cannot give a name yourself when setting up your MOSS environment.

Thanks to Service Pack 1 for WSS 3.0 you can now use the stsadm command mergecontentdbs instead of doing this operation on with a backup restore operation on the SQL box itself.

Here are the commands I used:

stsadm -o addcontentdb -url http://moss:1000 -databasename SharePoint_AdminContent_moss
stsadm -o enumsites -url http://moss:1000 > output.txt
stsadm -o mergecontentdbs -url http://moss:1000 -operation 3 -sourcedatabasename SharePoint_AdminContent_f58aa288-57e9-49ff-b953-9b4d53d50bfc -destinationdatabasename SharePoint_AdminContent_moss -filename output.txt
stsadm -o deletecontentdb -url http://moss -databasename SharePoint_AdminContent_f58aa288-57e9-49ff-b953-9b4d53d50bfc

check with stsadm -o enumcontentdbs -url http://moss:1000