CHunky Universe of Vigourous Astonishing SHarepoint :)

Tag Archives: database

SQL Server delete backup history

Exactly what I needed today.

use msdb
sp_delete_backuphistory '2013-01-30'

See more details on sp_delete_backuphistory stored procedure on msdn

Run web.config-dependant code in PowerShell

PowerShell is a great tool. It helps in SharePoint administration and tasks automation. Today I needed to provision a webpart on many similar pages. This third-party webpart’s constructor instantiates a dataaccess service and uses a connectionstring which is stored in the web.config file. So the webpart creation failed until I found a way to load the configuration into powershell.

First you can create a simple file powershell.exe.config, put it into $pshome (C:\Windows\System32\WindowsPowerShell\v1.0). In that file you can specify the connectionstring in the same way like in the web.config (or app.config). But it is not secure: the connectionstring can be changed in the future, and you really don’t want to copy your connectionstrings around. So there is a better way: Check out Ohad Israeli’s blog post about how a configuration file can be bound dynamically in a .net assembly: Binding to a custom App.Config file.

In PowerShell you can do the same, all we need is to modify the syntax: StackOverflow: Powershell Calling .NET Assembly that uses App.config:

[System.AppDomain]::CurrentDomain.SetData("APP_CONFIG_FILE", $config_path)

In SharePoint the web.config file resides in:


So include this in the script which invokes web.config dependant code:

$config_path = "C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config"
[System.AppDomain]::CurrentDomain.SetData("APP_CONFIG_FILE", $config_path)

The ability to bind the configuration file dynamically can be even helpful in another part of the SharePoint world – timerjobs. The timer jobs are executed by owstimer.exe. This service has an own configuration file: {hive}\BIN\OWSTIMER.EXE.CONFIG If the timer job configuration shares connectionstrings or appSettings with web.config, it should be theoretically possible to bind the web.config to owstimer. Leave a comment below if you find this information useful.

Settings in Sharepoint_config database instead of appSettings

An alternative to store key-value paired properties in appSettings in web.config we can use Sharepoint_config database by creating our own SPPersistedObject.  First we have to create a class which inherits from SPPersistedObject and decorate its properties with [Persisted]:

namespace Takana.SharePoint
    public class Settings: SPPersistedObject
        public string DbConString;
        public LinkCheckerPersistedSettings() { }
        public LinkCheckerPersistedSettings(string name, SPPersistedObject parent) : base(name, parent) { }

Then we must create such an object. Let’s do it in Powershell:

if(-not(Get-PSSnapin | 
	Where { $_.Name -eq "Microsoft.SharePoint.PowerShell"})) { 
	Add-PSSnapin Microsoft.SharePoint.PowerShell }

$app = get-spwebapplication http://takana
$configName = "takana.sharepoint.settings"
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$settings = $farm.GetObject($configName, $app.Id, [takana.sharepoint.settings])
if ($settings -ne $null) { $settings.Delete() }             
#Create a new persisted settings object
$settings = New-Object Takana.Sharepoint.settings ($configName, $app)
$settings.DbConString = "Some Connection String"

To get a member by name we must use reflection:

public static string FindSettings(string key, SPWebApplication app)
  var s = string.Empty;
    var farm = Microsoft.SharePoint.Administration.SPFarm.Local;
    var settings = farm.GetObject("Takana.Sharepoint.Settings", app.Id, typeof(Settings)) as Settings;
      if (settings != null)
        var type = typeof(Takana.SharePoint.Settings);
        var f = type.GetField(key);
        s = f.GetValue(settings) as string;
  catch (ArgumentException ex)
    throw new ArgumentException("Settings could not be found."), ex);
  return s;

To prevent errors, we must catch ArgumentException. Pay attention to the constructor of ArgumentException, we provide the original Exception as an InnerException argument and enable a better tracking.

Shrink sharepoint database

A Sharepoint Database can become big and have unused spaces. To shrink database go to CA-> Health Analyzer:


See if there is a list item about unused space in db under the Availability. Click on Repair Automatically in the opened Modal Dialog:


MongoDB shell, learn directly in browser

Are you also curious about NoSQL databases. Well, MongoDB is one of the most known ones. MongoDB has a javascript syntax (json), I like it. Try out the interactive shell at the mongodb site.


Quickstart on Ubuntu

To test it on Ubuntu, just install an existing apt-package:

sudo apt-get install mongodb

Create the default folder and change permissions:

sudo mkdir -p /data/db/
sudo chown `id -u` /data/db

Then jump directly into mongo db shell:


Here are some useful and self explaining commands:

show dbs
show collections
show users
use <db-name>

Återskapa koppling till login i DB efter restore

Om du har kört restore på en databas, så kan det hända att det inte går att ändra rättigheter, och den login du använt inte kan se databasen. SQL Server spottar ut felmeddelandet med kod 15023:

The database is not accessible. (Object Explorer)

Om det är WebService som i sin tur anropar databasen, kan det komma ett sådant felmeddelande:

The underlying provider failed on Open.

Lösningen är att uppdatera kopplingen. Hämtat från denna blog.

EXEC sp_change_users_login 'Auto_Fix', 'your_login', NULL, 'YoUrPaSsWoRd'

Eller bara:

sp_change_users_login 'auto_fix', 'your_login'

Det här är hämtat från forumet:

När du har kört det kommer det stå i Messages:

The row for user 'your_login' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
Daniel Chronlund Cloud Tech Blog

News, tips and thoughts for Microsoft cloud fans

Вула Чăвашла

VulaCV - Чăвашла вулаттаракан сайт

Discovering SharePoint

And going crazy doing it

Bram de Jager - Architect, Speaker, Author

Microsoft 365, SharePoint and Azure

SharePoint Dragons

Nikander & Margriet on SharePoint

Mai Omar Desouki

PFE @ Microsoft

Cameron Dwyer

Office 365, SharePoint, Azure, OnePlace Solutions & Life's Other Little Wonders


Me and My doings!

Share SharePoint Points !

By Mohit Vashishtha

Jimmy Janlén "Den Scrummande Konsulten"

Erfarenheter, synpunkter och raljerande om Scrum från Jimmy Janlén

Aryan Nava

DevOps, Cloud and Blockchain Consultant


SharePoint for everyone


Ryan Dennis is a SharePoint Solution Architect with a passion for SharePoint and PowerShell

SharePoint 2020

The Vision for a Future of Clarity

Aharoni in Unicode

Treacle tarts for great justice

... And All That JS

JavaScript, Web Apps and SharePoint


Mostly what I know and share about...