Steuerung+C und Steuerung+V sind zwei Windows-Tastenkombinationen, die wir täglich vielfach verwenden, um eine Kopie einer bestehenden Datei zu erstellen. Doch was im Dateisystem so leicht von der Tastatur geht, scheint im Rahmen eines nächtlichen Lade- und Aufbereitungsprozesses ein wenig komplexer. So möchten wir am Beispiel einer als Blaupause dienenden Datenbank eine Möglichkeit erläutern, diese mittels eines Skriptes in beliebiger Anzahl zu vervielfältigen.

So ist beispielsweise ein sehr restriktives Szenario denkbar, bei dem die Entwicklung in einer Hauptdatenbank geschieht und deren Inhalte dann nachts in verschiedene Kinderdatenbanken für jede Region (Nord, Süd, Ost, West) transportiert werden. Die Einschränkung der Inhalte steuern wir per Prozedur oder im Verfahren des Abzugs aus den Vorsystemen. Und wie werden die strukturellen Änderungen an der Hauptdatenbank in die einzelnen Satelliten publiziert? Dies kann mit folgender Logik umgesetzt werden.

Das Ziel der Automatisierung ist die nächtliche Kopie einer Hauptdatenbank in vier verschiedene Satelliten für die Regionen Nord, Süd, West und Ost. Hierfür werden die *.mdf Datei der Blaupause, sowie beliebig viele zugehörige Log Dateien (*.log) im Dateisystem kopiert, mit einem neuen Namen versehen und als Unterbau für den neu erstellten Klon zur Verfügung gestellt. Soweit die Theorie. In der Praxis kann dies am Beispiel unserer Chair wie folgt aussehen.

Für den Zugriff auf die SQL Server Dateien der zu kopierenden Datenbank, muss zunächst deren Speicherort im Dateisystem ermittelt werden. Dies geschieht in der Prozedur per Auslesen der SQL Server Registry Inhalte:

Speicherort der Datenbank:

EXEC master.dbo.xp_instance_regread N'H-
KEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData',
@DirData  output, 'no_output'

Speicherort der Logfiles:

EXEC master.dbo.xp_instance_regread N'H-
KEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @DirLog output, 'no_output'

Hierbei werden jeweils in die beiden nvarchar Variablen @DirData und @DirLog die in der Registry hinterlegten Speicherorte für Datendatei und Logfile der Hauptdatenbank ausgelesen.

Da nun die Pfade zu den Dateien bekannt sind, muss vor dem eigentlichen Kopierprozess die Quelldatenbank offline gesetzt werden. Wäre sie noch aktiv, kann im Dateisystem nicht auf die Dateien zugegriffen werden. Daher müssen wir in unserem Kopierskript an das Offlinesetzen der Datenbank denken. Sie wird nach erfolgreichem Abschluss der Prozedur wieder automatisch online gesetzt.

select @vSourceDBStatus = state_desc from sys.databases where name =
@vSourceDB
    begin
        if @vSourceDBStatus <> 'OFFLINE'
            set @vSQL = N'alter database ' + @vSourceDB + ' set offline with rollback immediate';
            exec (@vSQL)
    end

Nun soll für jede Region der Chair eine Kopie der Quelldatenbank erstellt werden.
Hierfür benötigen wir Zugriff auf die ‚xp_cmdshell‘ – Funktionalität des SQL Servers. Ist diese deaktiviert, kann sie mit Hilfe des Kommandos

EXEC sp_configure'xp_cmdshell', 1
RECONFIGURE

aktiviert werden. Zunächst lesen wir in einen Cursor ‘cur_region’ alle vorhanden Regionen der Dimensionstabelle.

declare cur_region cursor for (select distinct region_de from T_DIM_05_02_Region)

Da nun die vier geographischen Separatoren in der Cursorvariable enthalten sind, können wir diese in einer Schleife abrufen und pro Ausprägung eine Kopie von Daten und Logfile(s) erstellen.

OPEN cur_region
    FETCH NEXT FROM cur_region INTO @vRegionID
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Zusammenfügen des neuen Datenbanknamens
        SET @vNewDB = replace(@vSourceDB, '_Master', '') + '_' + @vRegionID

        -- Prüfen, ob dieser neue Name derzeit schon existiert.
        -- Falls ja, löschen der existenten Datenbank
        IF EXISTS(SELECT * FROM sys.databases WHERE name = @vNewDB)
        BEGIN
            SET @vSQL = 'DROP DATABASE ' + @vNewDB
            EXEC (@vSQL)
        END

        -- Auslesen des existenten Dateinamens
        DECLARE cur_dbfile CURSOR FOR
            (
                SELECT
                    physical_name
                FROM
                    sys.master_files
                WHERE
                    database_id = DB_ID(N'' + @vSourceDB + '') AND type_desc = 'ROWS'
            )


        OPEN cur_dbfile
            FETCH NEXT FROM cur_dbfile INTO @vSourceDBFileName
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    -- Zusammenfügen des neuen Dateinamens
                    SET @vNewDBFileName = REPLACE(@vSourceDBFileName, '.mdf', '_' +
                    @vRegionID + '.mdf')
                    SET @vDBFileName = @vNewDBFileName
                    -- Kopieren der Datei
                    SET @XPcommand = 'copy ' + @vSourceDBFileName + ' ' + @vNewDBFileName
                    exec master..xp_cmdshell @XPcommand
                    FETCH NEXT FROM dbfile_cursor INTO @vSourceDBFileName
                END

        CLOSE cur_dbfile
        DEALLOCATE cur_dbfile
        FETCH NEXT FROM cur_region INTO @vRegionID
    END
CLOSE cur_region
DEALLOCATE cur_region

Analog hierzu kann der Name und Speicherot von einem oder mehreren Logfiles der Quelldatenbank kopiert und mit dem Kürzel der Region versehen werden.
Nach der erfolgten Kopie der gewünschten Dateien, können die neuen Datenbanken im SQL Server hinzugefügt werden.

set @vSQL = 'Create Database ' + @vNewDB + ' on (Filename = ''' + @vDBFileName + '''), (Filename = ''' + @vLogFileName + ''') for attach'

Je nachdem, ob in der Quelldatenbank mehrere Logfiles existierten, können diese über eine Schleife ebenfalls in die Satelliten publiziert werden.

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @vAddLogfileSQL = 'ALTER DATABASE ' + @vNewDB + ' ADD FILE ( NAME = N' + @vLogName + ',
        FILENAME = N' + @vLogFileName + ' , SIZE = 3072KB , FILEGROWTH = 1024KB )
        TO FILEGROUP [PRIMARY]'
        EXEC (@vAddLogfileSQL)
        FETCH NEXT FROM ...
    END

Da es sich bei der zu kopierenden Hauptdatenbank ganz bestimmt um eine mit DeltMaster Modeler konfigurierte Datenbank handelt, prüfen wir am Ende noch unter T_Model_Parameters den eingestellten Namen der zugehörigen OLAP Datenbank und ändern diesen ebenfalls auf den jeweiligen Klon der Masterinhalte.

Am Schluss wird die Quelldatenbank dann wieder online gestellt und unsere eine Masterdatenbank hat erfolgreich vier Kinder bekommen.

set @vSQL = N'alter database ' + @vSourceDB + ' set online';
exec (@vSQL)

In größeren Datenbanken macht es sicherlich Sinn, vor dem Kopieren noch die Fakten- und Dimensionstabellen zu leeren und diese erst im Anschluß mit den jeweilig korrekten Inhalten zu befüllen.
Auf Nachfrage kann gerne das komplette Skript zur Verfügung gestellt werden, worin auch dieses vorige „Abspecken“ der Quelldatenbank berücksichtigt ist.