Determinare via T-SQL le directory di default di un istanza Sql Server

Trovare da codice T-SQL le directory di default di un’istanza Sql Server può essere comodo per automatizzare alcune operazioni, come la creazione di database: mi capita infatti con una relativa frequenza di dover creare script di creazione di nuovi database in ambienti diversi: come minimo, si tratta di almeno un ambiente di sviluppo, uno di test ed uno di produzione.

In questi casi, di solito creo uno script per il database e poi uno script per ognuno degli oggetti (tabelle, viste, funzioni, procedure etc.) che vi sono contenuti. Nel caso dello script di creazione del database la complicazione è data dalla necessità di creare i file di dati e di log nelle directory appositamente definite nella configurazione dell’istanza stessa.

Almeno nella nostra realtà, è normalissimo che l’ambiente di sviluppo (di solito un comune pc), l’ambiente di test (una macchina virtuale) e l’ambiente di produzione (un vero server, in qualche caso un cluster) abbiano dotazioni di dischi diverse, quindi non è possibile “cablare” il percorso dei suddetti file. La soluzione più ovvia – e meno elegante – è quella di adattare manualmente lo script ogni volta guardando le proprietà dell’istanza Sql Server sulla quale si vuole creare il database.

Ma come fare per rendere dinamico lo script?

Sql Server 2012 (e successivi)

In questo caso siamo fortunati: la funzione SERVERPROPERTY ci mette a disposizione due parametri, 'InstanceDefaultDataPath' e 'InstanceDefaultLogPath', che ci restituiscono le directory di default desiderate. È sufficiente interrogare la funzione passando uno dei due parametri:

SELECT [DefaultFile] = SERVERPROPERTY('InstanceDefaultDataPath');
SELECT [DefaultLog] = SERVERPROPERTY('InstanceDefaultLogPath');

Nel mio pc, i valori restituiti sono:

D:\Documenti\SqlServer\Data\
D:\Documenti\SqlServer\Log\

Sql Server 2005, 2008 o 2008 R2

Se ben ricordo, la funzione SERVERPROPERTY è stata introdotta con Sql Server 2008; il problema è che comunque i due parametri visti sopra non erano disponibili, quindi la funzione non è utilizzabile.

La soluzione passa invece attraverso l’extended stored procedure xp_instance_regread che legge dal Registry di Windows le impostazioni dell’istanza Sql Server in uso.

Come ci si potrebbe aspettare, la stored procedure richiede in input i parametri relativi a root key, key e value name, restituendo a sua volta un paramtero di output contenente il value in forma di stringa. Per chi non ha familiarità con il Registro di Windows e con il suo editor regeditor, potremmo paragonare root key all’unità (disco) di un computer, key al path e value name al nome di un file: value sarà quindi paragonabile al contenuto del file value name contenuto nel percorso key nell’unità root key.

Il parametro root key è senz’altro quello più semplice da gestire, visto che va valorizzato al valore N'HKEY_LOCAL_MACHINE'.

Il parametro key già presenta qualche complessità in più, dal momento che potrebbe variare da un’istanza all’altra. La stored procedure ci viene però incontro, perché traduce la stringa N'Software\Microsoft\MSSQLServer\MSSQLServer' nella stringa corrispondente all’istanza in uso.

Il parametro value name assume il valore N'DefaultData' se si vuole recupare la directory di creazione dei file di dati o il valore N'DefaultLog' se si vuole recuperare la directory di creazione dei file di log.

Purtroppo questo non sempre è sufficiente: ad esempio, mi è capitato un pc in cui la xp_instance_regread ha restituito correttamente la directory dei log, ma non quella dei dati.
In questi casi, se il valore restituito è NULL occorre percorrere ulteriori strade:

  1. leggere dal Registry la directory dove è stata installata l’istanza Sql ed aggiungere poi le stringhe '\Data' oppure '\Log'; in questo caso key = N'Software\Microsoft\MSSQLServer\Setup' e value name = N'SQLDataRoot'
  2. leggere dal Registry il path completo dei file dati e log del database 'master'; in questo caso key = N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters' e value name = N'SqlArg0' (per il file dei dati) oppure value name = N'SqlArg2' (per il file di log). Anticipo l’obiezione: value name = N'SqlArg1' restituisce la directory dei file di log di Sql Server, non dei file di log del database. Si noti che la stringa sarà preceduta da un prefisso:
    • '-d' per il file dei dati del database ('SqlArg0')
    • '-e' per il file di log degli errori ('SqlArg1')
    • '-l' per il file di log del database ('SqlArg2')
  3. interrogare le tabelle/viste di sistema per recuperare la locazione dei file di dati e log del database 'master': è molto simile alla strategia precedente, ma non usa la stored procedure estesa.

ricordo ancora che nei casi 2. e 3. ci viene in realtà restituito il path completo del file dei dati oppure dei log del database 'master'; quindi per ottenere la directory che ci interessa dovremo manipolare un pochino la stringa che avremo ottenuto.

Directory dei dati

Vediamo allore di mettere insieme i pezzi:

DECLARE @DefaultData nvarchar(255);

/* 0. Interrogazione sul registry della directory definita per l'istanza corrente */
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                  , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                                  , N'DefaultData'
                                  , @DefaultData OUTPUT

IF @DefaultData IS NULL
    BEGIN
    /* 1. Interrogazione sul registry della directory di base dell'istanza corrente */
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                      , N'Software\Microsoft\MSSQLServer\Setup'
                                      , N'SQLDataRoot'
                                      , @DefaultData OUTPUT;
    SELECT @DefaultData = @DefaultData + '\DATA';
    END

IF @DefaultData IS NULL
    BEGIN
    /* 2. Interrogazione sul registry del path del file di dati del database [master] */
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                      , N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
                                      , N'SqlArg0'
                                      , @DefaultData OUTPUT;
    SELECT @DefaultData = SUBSTRING ( @DefaultData, 3, 255 );
    SELECT @DefaultData = SUBSTRING ( @DefaultData
                                    , 1
                                    , LEN( @DefaultData ) - CHARINDEX( '\', REVERSE( @DefaultData ) ) );
    END

IF @DefaultData IS NULL
    BEGIN
    /* 3. Interrogazione sulla tabella di sistema del path del file di dati del database [master] */
    SELECT @DefaultData = SUBSTRING( physical_name
                                   , 1
                                   , CHARINDEX( N'\master.mdf'
                                               , LOWER( physical_name ) ) - 1 )
    FROM [master].[sys].[master_files]
    WHERE [database_id] = 1
      AND [file_id] = 1;
    END

SELECT [Default Data] = @DefaultData;

A scopo divulgativo, io ho voluto mostrare tutti i metodi per ottenere la directory ove creare il file di dati. Nella realtà, dal momento che ognuno dei punti 1., 2. e 3. restituiscono un risultato, il primo che viene eseguito impedisce l’esecuzione dei successivi, quindi nei nostri script potremmi limitarci al punto 0. (sempre, per primo) e poi uno solo degli altri tre, a scelta.

Directory dei log

In questo caso lo script è molto simile:

DECLARE @DefaultLog nvarchar(255);

/* 0. Interrogazione sul registry della directory definita per l'istanza corrente */
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                  , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                                  , N'DefaultLog'
                                  , @DefaultLog OUTPUT

IF @DefaultLog IS NULL
    BEGIN
	/* 1. Interrogazione sul registry della directory di base dell'istanza corrente */
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                      , N'Software\Microsoft\MSSQLServer\Setup'
                                      , N'SQLDataRoot'
                                      , @DefaultLog OUTPUT;
	SELECT @DefaultLog = @DefaultLog + '\LOG';
    END

IF @DefaultLog IS NULL
    BEGIN
	/* 2. Interrogazione sul registry del path del file di log del database [master] */
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                      , N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
                                      , N'SqlArg2'
                                      , @DefaultLog OUTPUT;
    SELECT @DefaultLog = SUBSTRING ( @DefaultLog, 3, 255 );
    SELECT @DefaultLog = SUBSTRING ( @DefaultLog
                                   , 1
                                   , LEN( @DefaultLog ) - CHARINDEX( '\', REVERSE( @DefaultLog ) ) );
    END

IF @DefaultLog IS NULL
    BEGIN
	/* 3. Interrogazione sulla tabella di sistema del path del file di dati del database [master] */
    SELECT @DefaultLog = SUBSTRING( physical_name
                                   , 1
                                   , CHARINDEX( N'\mastlog.ldf', LOWER( physical_name ) ) - 1 )
    FROM [master].[sys].[master_files]
    WHERE [database_id] = 1
      AND [file_id] = 2;
    END

SELECT [Default Log] = @DefaultLog;

Directory dei file di backup

Qui per fortuna le cose sono molto più semplici. È sufficente interrogare il registry, passando come value name semplicementeN'BackupDirectory'

DECLARE @DefaultBackup nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                  , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                                  , N'BackupDirectory'
                                  , @DefaultBackup OUTPUT;
SELECT @DefaultBackup;

che nel mio caso restituisce:

D:\Documenti\SqlServer\Backup

Vedi anche:

ServerFault : Default Data and Log Directories from remote server using TSQL

Tenbulls : How to set and manage your SQL Server default paths

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

*