Sitecore Text Searcher - The Ultimate SQL Script

"Where can I edit this text in Sitecore?"

"Can you find out where this content can be changed in Sitecore? 

"I tried changing this content in Sitecore here, but it's not reflecting on the front-end after I publish." 

I hear that a lot.  

When maintaining multiple Sitecore solutions - knowing every template, rendering, and how the data ties in to what you see on the front-end - is nearly impossible.   Prior to Helix, developers were free to develop using various patterns. Some are built in MVC, others are WebForms.  Some components rely on datasource items, others rely on data from the items template. Regardless of architecture, we need to be able to help Content Authors change content if they can't find what they're looking to change.

There are typically a few options to obtain the information for the Content Author's request:

     - If you have access to the solution - inspect the page elements, grab a common class or id, do a Entire Solution search, find some files and follow the code to determine how it populates the component.

     - Start with the page item in Sitecore and determine whether the content is located on the item's template, or in within a data source item.

Those options are both fine - and eventually effective - but there is a faster way - IF YOU HAVE ACCESS TO SQL (which is normally the case if you have a recent DB locally or access to the DB server).

Simply start a new query in SQL MS on the Sitecore DB you want to search in (Web or Master),  modify the @SEARCHTERM variable (include the percent symbols eg. '%content goes here%'), and execute this handy script:

---


DECLARE @SITECOREROOTID NVARCHAR(MAX), @FULLPATHSEARCH NVARCHAR(MAX), @SEARCHTERM NVARCHAR(MAX)
SET @SITECOREROOTID = '11111111-1111-1111-1111-111111111111';
SET @FULLPATHSEARCH = 'sitecore/content/%';
SET @SEARCHTERM = '%search term%';

WITH FullPathItems (ID, NAME, ITEMPATH, TEMPLATEID)
     AS (SELECT ID,
                NAME,
                CAST(NAME AS NVARCHAR(MAX)) AS itempath,
                TEMPLATEID
         FROM   [dbo].ITEMS
         WHERE  ID = @SITECOREROOTID
         UNION ALL
         SELECT i.ID,
                i.NAME,
                CAST(( ITEMPATH + '/' + i.NAME ) AS NVARCHAR(MAX)) AS itempath,
                i.TEMPLATEID
         FROM   [dbo].ITEMS i
                INNER JOIN FullPathItems a
                        ON i.PARENTID = a.ID)
                        SELECT fieldsX.ITEMID,
       fpi.NAME AS ITEMNAME,
       fieldsX.VALUE,
       fpi.TEMPLATEID AS TEMPLATEID,
       templatesX.NAME AS TEMPLATENAME,
       ITEMPATH,
       fieldsX.FIELDID,
       itemsX.NAME AS FIELDNAME,
       fieldsX.LANGUAGE  AS ITEMLANGUAGE 
       FROM   [dbo].[FIELDS] fieldsX
       INNER JOIN FullPathItems fpi
               ON fpi.ID = fieldsX.ITEMID
       INNER JOIN [dbo].ITEMS itemsX
               ON itemsX.ID = fieldsX.FIELDID
       INNER JOIN [dbo].ITEMS templatesX
               ON templatesX.ID = fpi.TEMPLATEID
WHERE fieldsX.VALUE LIKE @SEARCHTERM 
AND fpi.ITEMPATH LIKE @FULLPATHSEARCH

/*INCLUDE  THE FOLLOWING LINE TO INCLUDE ITEM ID CONDITION */
-- AND fieldsX.ItemId = 'B3F15B8C-BAE5-40F4-A139-BA7B3EC6E1ED'

/*INCLUDE THE FOLLOWING LINE TO INCLUDE ITEM NAME CONDITION */
-- AND fpi.Name LIKE '%My Item%'

/* INCLUDE THE FOLLOWING LINE TO INCLUDE TEMPLATE NAME CONDITION */
--AND templatesX.Name = 'My Template'

/* INCLUDE THE FOLLOWING LINE TO ADD LANGUAGE CONDITION */
-- AND fieldsX.Language = 'en-US'

ORDER  BY fpi.ITEMPATH

---

The result includes the following columns:

Sometimes, you'll be presented with several instances of a specific keyword, in which case you can uncomment various lines near the bottom of the script to drill down and filter the results even further.

I find myself using this script on a daily basis and hope that sharing this script helps others track down content quickly and effectively, too.

Read the full post →

Sitecore Website Backup Powershell Script

Depending on the environment, infrastructure, and budget - each Sitecore project has their own backup and deployment process.  More recent solutions often include a file backup step during an automated build process - while others take on a more manual process.

Whatever your specific scenario, my rule of thumb for changing anything is: ALWAYS BACKUP BEFORE MAKING CHANGES.

With solutions using less complex deploy processes - I used to select, copy, and paste specific files and directories to a separate location in case something went completely awry.  To save time - and maintain peace of mind before making file changes to a staging or production environment - I now utilize a handy backup script using Powershell that backs up all necessary files (Robocopy) by simply running it.

To use it:

1. Modify and save the Settings.xml file with your own source and destination paths.
   
   
2. Double-click on RunBackup.bat file.  Once you accept the prompt to run the script as an administrator, you'll see the output of each file being transferred to the destination folder.
   
   
3. Watch it run!
   

The script explicitly excludes the following Sitecore specific paths that often aren't changed from build to build - and don't need to take up more space than needed.  To add/remove an excluded entries,  open and modify the _sitecorebackupscript.ps1 file's '$exclude' variable.

"_DEV", "App_Data", "Content", "temp", "upload", "sitecore", "sitecore modules" 

Settings.xml - Configuration settings

 <?xml version="1.0" encoding="UTF-8"?>  
 <settings>  
      <!-- Date format for Destination folder -->  
      <DateFormat>MM-dd-yyyy hhmmss</DateFormat>  
      <!-- Source Website directory to backup (exclude trailing back-slash) -->  
      <SourceDirectory>C:\inetpub\wwwroot\Sitecore8\Website</SourceDirectory>  
      <!-- Destination directory where backup made (exclude trailing back-slash) -->  
      <BackupDirectory>C:\inetpub\wwwroot-backup\Sitecore8</BackupDirectory>  
 </settings>  

_sitecorebackupscript.ps1: The Powershell script

 <# Sitecore Backup Script - Gabriel Streza (sitecoregabe.com)   
 **************************************************************************  
 Powershell script that backups your Sitecore website while excluding unessesary files.  
   
 Modify Settings.xml to control Source and Directory paths.   
   
 **************************************************************************  
 #>  
   
 # Directory definitions  
 $scriptpath = $MyInvocation.MyCommand.Path  
 $directory = Split-Path $scriptpath  
 [xml]$ConfigFile = Get-Content "$directory\Settings.xml"  
   
 # Script Variable Defintions  
 $DateFormat = Get-Date -format $ConfigFile.Settings.DateFormat  
 $SourceDirectory = $ConfigFile.Settings.SourceDirectory  
 $BackupDirectory = $ConfigFile.Settings.BackupDirectory  
 $dest = $BackupDirectory + '_' + $DateFormat  
   
 # Folders to exclude  
 $exclude = "_DEV", "App_Data", "Content", "temp", "upload", "sitecore", "sitecore modules"  
   
 # Run as Administrator  
 if (!([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) { Start-Process powershell.exe "-NoProfile -ExecutionPolicy Bypass -File `"$PSCommandPath`"" -Verb RunAs; exit }  
   
 # File copy  
 robocopy $SourceDirectory $dest /s /xj /xd $exclude  
   
 # Completed  
 Write-Host -NoNewLine 'DONE! Press any key to continue...';  
 $null = $Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown');  

RunBackup.bat: Execute the Powershell script

 Powershell.exe -executionpolicy remotesigned -File "_sitecorebackupscript.ps1"  

You can download, modify, and utilize this by heading to this GitHub repo and grabbing a copy:
https://github.com/strezag/Sitecore-Website-Backup-Script/


Happy backup...ing!

Read the full post →