SharePoint Tips

SharePoint Portal Server Data Backup and Restore Utility Understanding the files generated by utility "SharePoint Portal Server Data Backup and Restore" is a utility that comes with SharePoint Portal Server 2003 used to backup all SharePoint sites. This includes the Portal and Windows SharePoint Sites. The .SPB backup files created by the utility are really just backup files that you could have created using Enterprise Manager. The utility also creates a XML file as well that lists the three databases: *_PROF means PROFILE *_SITE means SITE *_SERV means Services NOTE: There is a bug when using "Full-text search and index component" and SQL 2005 as your backend. The SharePoint Backup and Restore utility errors out because the index and the datafile both try to restore to the same location. This KB article tells you how to enable (and you can see how to disable) the "Full-text search and index component" if you are having this problem. http://support.microsoft.com/kb/837367. If you are trying to restore a backup and must have this feature enabled, then you can get a hotfix by contacting Microsoft (no direct download link) as described in the KB article: http://support.microsoft.com/kb/919175/. I have not verified it, but I think if you use MS SQL to restore the backup files directly and then Use the Create / Restore Portal in the SharePoint Central Administration web page to add the databases to the configuration database, then this should work fine. NOTE: I was able to use Enterprise Manager to restore files. However, this does not add a Portal. I tried restoring from the Create Portal page, but it didn't work. I think if I had the configuration database backed up also, this would have worked. The configuration database is NOT backed by the utility. Enterprise Manager or similar tool must be used. Where are the log files? C:\Program Files\SharePoint Portal Server\Logs Look for a file similar to SPS_00003364_SPSBACKUP.LOG NOTE: This is the location of many other logs such as the Portal Creation log which is something like: SPS_SiteCreation_<site name>.Log Version of SharePoint Must match In order for the utility to succeed, make sure you have the EXACT same version of SharePoint Portal Server 2003 and Windows SharePoint Services. This includes Service Packs and Hotfixes. If an restore fails typically you don't need to backup and restore again, you just need to restore once you have made the version the same. If version are different, you may get a message like:

Portal creation failed Microsoft.SharePoint.SPException: The database schema is too old to perform this operation in this SharePoint cluster. Please upgrade the database and try again. ---> System.Runtime.InteropServices.COMException (0x81070571): The database schema is too old to perform this operation in this SharePoint cluster. Please upgrade the database and try again.

Restoring a Database Using MS SQL Server You can use MS Enterprise Manger for SQL Server 2000 or MS SQL Server Management Studio to restore the 3 .SPB files. 1. Restore .SPB files (they are really just standard MS SQL backup files) into the database of your choice. NOTE: The configuration database can and should be recreated when moving to a new machine and thus does not need to be backed manually (SharePoint Portal Server Data Backup and Restore doesn't back it up either) and does not need to be restored either. 2. Go to the SharePoint Central Administration web page and choose "Create a portal site" under the "Portal Site and Virtual Server Configuration" section. 3. Click the Restore radio button, and fill in the names of the databases you restored. 4. If this is a new server that you restored to, you will need to modify some of the data in the database. This of course is not support by Microsoft. I have not had any issues doing this, but I cannot be held responsible if it does cause any errors as any database changes to SharePoint is not supported by Microsoft (or me for that matter). 5. At this point, the SharePoint portal should come up without errors. You should be able to create a new Windows SharePoint Services site without errors. If you have Site Definitions, themes, etc defined specifically for your purposes, you will most likely need to install and configure them before existing WSS sites will come up without error. 6. If you are using SharePoint Portal Server to keep a listing of your sites through its Sites page, you will need to update each reference to the since they are stored in the database and still point to the old server. You can always type them in the browser directly, but it is probably best to correct this to avoid accidentally hitting the production or old server. If you have a few sites, you can change this by hand, if you have a lot of sites I recommend a SQL update statement. The easiest way I have found to do this is to do a global search in replace in all three databases (not config, just Profile, Services, Site). I use something like SearchAndReplaceAllTAbles 'http://sharepoint.myco.com:80', 'http://sharepointnew.myco.com:8080' SearchAndReplaceAllTAbles 'http://sharepoint.myco.com', 'http://sharepointnew.myco.com:8080' SearchAndReplaceAllTAbles 'http://sharepoint', 'http://sharepointnew.myco.com:8080'

SearchAndReplaceAllTAbles 'sharepoint.myco.com:80', 'sharepointnew.myco.com:8080' SearchAndReplaceAllTAbles 'sharepoint.myco.com', 'sharepointnew.myco.com:8080'

SearchAndReplaceAllTAbles 'sharepoint', 'sharepointnew.myco.com:8080'

Here is the source code for this SearchAndReplaceAllTables stored procedure: http://justgeeks.blogspot.com/2007/01/sql-2005-search-and-replace-on-data.html I recommend looking at what will be replaced first, I recommend using SearchAllTables first. Here is the source: http://justgeeks.blogspot.com/2006/10/search-ms-sql-server-for-any-text.html The url is not always fully qualified, some have ports, http, sts, etc so you may need to be strategic in the order and what you replace. The above should get most of it though. Please remember, this is a very big change. I highly recommend you look at what is being replaced and test, test, test the first time you do it to make sure nothing broke. When you are done, searches and site directory should be accurate again. You may find some images on particular sites still point old server and / or don't load. You may need to change them manually.

Shrinking MS SQL database

Here is an easy way to shrink the log file of the a MS SQL 2000 or 2005 database.

The simplest way seems to be to use Enterprise Manager if your using SQL 2000 or SQL Server Management Studio if your using SQL 2005 to use the UI. The quickest is probably to copy and paste the SQL though. You can decide.

Using the UI
SQL Server Studio Management
1. Change Recovery Mode to Simple by getting properties on Database Options. Then choose Simple for Recovery model.
2. It can be found by right clicking on the database and choosing tasks. Choose Shrink and then Files. Be sure to select the File type as 'Log'
3. Change Recovery Mode to Full by getting properties on Database Options. Then choose Full for Recovery model.


Using SQL
USE MyDatabase;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ('MyDatabase_Log', 1);
GO

-- Reset the database recovery model.
ALTER DATABASE MyDatabase
SET RECOVERY FULL;
GO

NOTE: MyDatabase_Log is by default correct. However, if a database has been renamed by default the Logical file names don't get updated. So, likely if the MyDatabase was renamed to MyDatabaseOld the Logical file name (the first parameter to DBCC SHRINKFILE) would still be MyDatabase. The name can be verified usign MS SQL Server Management Studio for SQL 2005 or Enterprise Manager for SQL 2000.

NOTE: This script can be executed from MS SQL Server Management Studio even if the database is on a SQL 2000 instance.

NOTE: If the Logical File name has an & in it you will need to put single quotes around the value as noted above. Otherwise, the single quotes are optional.

NOTE: If the log file doesn’t shrink, you may need to put the database in single user mode (under Properties | Options). Then run the DBCC SHRINKFILE command. 

If all else fails and you get desperate, you can detach your database (you may need to put it in single user mode first especially if you are out of disk space), then manually go to the file system and manually delete the log file. Then attach the database again; a new log file will be created.

WARNING: With any of this, you will lose the log of transactions since you have deleted the transaction log.

You may also want to check out my entry on how to truncate the transaction log if the above isn’t working for you.

ISAPI Extensions and Filters

What are they? Basically, they are ways to extending Internet Information Services (IIS). They take two different approaches. An ISAPI extension is much like a cgi or .net aspx page, but implemented at a much more low level using something like c or c++. It basically requires that you implement a few methods. The extension is accessed only when a url explicitly requests it. (Actually, in IIS 6 or greater it can be accessed the same way as a filter also. by using HSE_REQ_EXEC_URL.) It would typically reside in the /scripts directory. So an example would be: http://myth/articles/scripts/validate.dll?123456789012543 An ISAPI filter is much like writing an HttpModule for .net, except that it is called for every hit to the web site / or server if configured as such. It basically hooks into all requests to the web site or server and is called prior to .net framework call. Dev Tips: Testing Changes The fastest way to test your changes is to configure the filter IIS to point to your Debug version of the DLL. The best way to build when configured this way seems to be to keep the Services (from Administive Tools on the Start menu) open. When you want to build and test change do the following: 1. Stop the World Wide Web Publishing service. 2. Build (in Visual Studio, etc). 3. Start the World Wide Web Publishing service. 4. Attach to inetinfo.exe if you want to debug 5. Set breakpoint where desired. 6. Hit Url in IE. What methods can I implement? If you look in CHttpFilter class, you will see: virtual DWORD HttpFilterProc(PHTTP_FILTER_CONTEXT pfc, DWORD dwNotificationType, LPVOID pvNotification); virtual BOOL GetFilterVersion(PHTTP_FILTER_VERSION pVer); virtual DWORD OnReadRawData(CHttpFilterContext* pfc, PHTTP_FILTER_RAW_DATA pRawData); virtual DWORD OnPreprocHeaders(CHttpFilterContext* pfc, PHTTP_FILTER_PREPROC_HEADERS pHeaders); virtual DWORD OnAuthentication(CHttpFilterContext* pfc, PHTTP_FILTER_AUTHENT pAuthent); virtual DWORD OnUrlMap(CHttpFilterContext* pfc, PHTTP_FILTER_URL_MAP pUrlMap); virtual DWORD OnSendRawData(CHttpFilterContext* pfc, PHTTP_FILTER_RAW_DATA pRawData); virtual DWORD OnLog(CHttpFilterContext* pfc, PHTTP_FILTER_LOG pLog); virtual DWORD OnEndOfNetSession(CHttpFilterContext* pfc); virtual DWORD OnEndOfRequest(CHttpFilterContext* pfc); virtual DWORD OnAuthComplete(CHttpFilterContext* pfc, PHTTP_FILTER_AUTH_COMPLETE_INFO pAuthComplInfo); virtual DWORD OnSendResponse(CHttpFilterContext*, PHTTP_FILTER_SEND_RESPONSE); virtual DWORD OnAccessDenied(CHttpFilterContext*, PHTTP_FILTER_ACCESS_DENIED); Where do I find out what the events that I can use and what they do? http://msdn2.microsoft.com/en-us/library/ms524855.aspx Where do I find a list of the IIS Server variables that I can use and what they are? http://msdn2.microsoft.com/en-us/library/ms524602.aspx Where can I get a simple example? http://www.codeproject.com/isapi/isapiredirector.asp

Converting String to / from Date using Custom Formatting

In Java it is easy to convert a date as text / string to a Date object. String to Date object Date dateObj = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").parse("05/18/05 18:15:10"); Date object to String String dateStr = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(dateObj);

Exporting Business Rules from HP OpenView Service Desk to Excel

Using the HP OpenView Service Desk client open the Administrator Console. Then navigate to the Business Logic node. Expand that portion of the tree to show the Database Rules and UI Rules nodes. Click on which ever set of rules you would like to export. Go to the File menu, change Report Style to Table or better yet a Bordered Table. Then choose Print Preview from the same File menu. Click or highlight some of the text in the preview. Then do a control-A and then control-C to select all text and then copy. Go to MS Excel, open a new worksheet, and paste. You will need to make it pretty, but all the data is there. I recommend setting the column height to something like 12.75 if you like the default height that you usually get from a new Excel spreadsheet.