Bernhard Krenz

Ab und zu steht man vor der Herausforderung eine SQL-Datenbank wiederherzustellen oder sonstwie zurück zu setzen. Dann wird man schnell mit dem Problem konfrontiert, dass offene Verbindung zur Datenbank diese Aufgabe verhindern.

Nun kann man entweder mit den üblichen Tools, wie dem SQL-Management-Studio, die Verbindungen trennen, oder man bastelt sich dazu ein kleines Skript. Da ich selber hauptsächlich skriptbasiert arbeite, habe ich dafür folgenden Algorithmus entwickelt:

USE master

DECLARE @databaseName VARCHAR(15)
SET @databaseName = 'name_of_your_database'

DECLARE @kill_id INT
DECLARE @disconnectQuery NVARCHAR(255)
DECLARE killprocess_cursor CURSOR FOR SELECT a.spid FROM sysprocesses a JOIN sysdatabases b ON a.dbid = b.dbid WHERE b.name = @databaseName
OPEN killprocess_cursor

FETCH NEXT FROM killprocess_cursor INTO @kill_id
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @disconnectQuery = 'KILL '+ CONVERT(VARCHAR, @kill_id)
	RAISERROR ('EXECUTING: %s', 1, 1, @disconnectQuery) WITH NOWAIT
	EXEC (@disconnectQuery)
	FETCH NEXT FROM killprocess_cursor INTO @kill_id
END	

CLOSE killprocess_cursor
DEALLOCATE killprocess_cursor

Das Skript durchläuft mit einem Cursor sämtliche offenen Prozesse der festgelegten Datenbank und schließt diese mit Hilfe des „Kill“-Befehls und der zugehörigen Prozess-Id.

Bei der Architektur einer Datenbank gilt es nach einer Testphase oder nach Änderungen oftmals diese komplett neu aus den beteiligten Skripten zu erzeugen. Um zu viel manuelle Arbeit zu vermeiden erzeuge ich mir dazu ein weiteres Skript, welches neben der Installation der Datenbank auch alle beteiligten Skripte in fest definierter Reihenfolge ausführt.

Das Ausführen der Skripte aus aus einer Datei erfolgt über den Befehl xp_cmdshell. Dieser macht nichts weiter, als einen Befehl an die Windows-Kommandozeile weiter zu erreichen. Der Befehl ist im Standardfall allerdings sicherheitsbeschränkt, weshalb die Konfiguration vorher geändert werden muss.

Folgende Schritte sind dazu notwendig:

1. Löschen der Datenbank falls diese existiert
2. Datenbank neu erstellen
3. Berechtigung konfigurieren, um Skripte aus Dateien auszuführen
4. Konstanten deklarieren und initialisieren
5. Skript-Dateien in eine temporäre Tabelle einfügen
6. Diese Tabelle in einer Schleife durchlaufen und die Skripte aus den Dateien ausführen

Sehen wir uns einige der Schritte im Detail an:

Schritt 3:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.
RECONFIGURE

GO

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.
RECONFIGURE

Mit diesem Schnipsel werden die erweiterten Berechtigungen für den xp_cmdshell-Befehl gesetzt.

(continue reading…)