Gestione e Copia File MDF e LDF in SQL Server: La Guida Definitiva Per Tutti

Microsoft SQL Server è diventato uno dei sistemi di gestione di database relazionali più popolari sia per le piccole che per le grandi imprese. Gli esperti IT devono essere in grado di indicare esattamente dove sono memorizzate le posizioni dei file di database SQL importanti senza ritardi o errori per vari motivi, come per facilitare i processi di backup e recupero, trasferire i file in un'altra posizione o trovare la cartella se un'istanza del server è stata installata in una posizione non standard. Comprendere la struttura e le modalità di gestione dei file MDF e LDF è fondamentale per qualsiasi amministratore di database.

Cosa sono i file MDF e LDF di SQL Server?

SQL Server memorizza i dati utilizzando due estensioni di file:

I file MDF sono file di dati che contengono dati e oggetti come tabelle, indici, procedure memorizzate e viste. MDF (Master Database File) funge da file di dati principale in SQL Server che contiene tutti i dati utente come tabelle, indici, stored procedure, viste e altri oggetti del database. Questo file contiene sia lo schema che i dati effettivi, il che lo rende la parte più importante di qualsiasi SQL Server database. L'estensione .mdf identifica questo file primario che interagisce con i file di registro (.ldf).
I file LDF sono i file di log delle transazioni che registrano tutte le transazioni e le modifiche al database effettuate da ciascuna transazione. I file di log contengono le informazioni necessarie per ripristinare tutte le transazioni nel database. Deve essere presente almeno un file di registro per ciascun database.

Ogni database SQL Server dispone di almeno due file nel sistema operativo: un file di dati e un file di log (transaction log).

Tipologie di File di Database SQL Server

Per una comprensione più approfondita, ecco una tabella che riassume i tipi di file principali:

File	Descrizione
Primario (.mdf)	Contiene informazioni di avvio per il database e punta agli altri file nel database. Ogni database ha un file di dati primario. L'estensione del nome file consigliata per i file di dati primari è .mdf.
Secondario (.ndf)	File di dati (opzionali) definiti dall'utente. I dati possono essere distribuiti su più dischi inserendo ciascun file su un'unità disco diversa. L'estensione del nome file consigliata per i file di dati secondari è .ndf.
Transaction Log (.ldf)	Il log contiene le informazioni utilizzate per ripristinare il database. Deve essere presente almeno un file di registro per ciascun database.

Per impostazione predefinita, i dati e i transaction log vengono inseriti nello stesso disco e percorso, per gestire i sistemi a disco singolo.

Leggi anche: Guida Completa all'Errore CRC

TIP: I file di dati e transaction log di Sql Server possono essere scritti nei file system FAT o NTFS.

TIP: È una buona idea mantenere le estensioni di file predefinite. Non ci sono veri vantaggi nell'usare estensioni diverse e farlo aggiunge ulteriore complessità.

WARNING: I filegroup di dati e transaction log in lettura/scrittura non sono supportati su un file system NTFS compresso. Solo i database di sola lettura e i filegroup secondari di sola lettura possono essere inseriti in un file system NTFS compresso.

Localizzazione dei File di Database SQL Server

Conoscere la posizione fisica dei file è cruciale. Il percorso predefinito del file di dati del server SQL dipende dalla versione del software Microsoft SQL Server:

SQL Server 2017 - C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\
SQL Server 2019 - C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\
SQL Server 2022 - C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\

Esistono diverse metodologie per identificare la posizione esatta dei file del database.

Metodi Nativi per la Localizzazione dei File

Hai due opzioni native per scoprire dove il server SQL memorizza i suoi file di database: puoi fare clic con il tasto destro sul nome dell'istanza in SQL Server Management Studio (SSMS) e navigare nella scheda 'Database Settings' oppure utilizzare una query T-SQL.

SSMS ti permette di controllare un database alla volta, e le query richiedono una certa competenza e tempo per essere composte. Se si sta spostando un database, prima che venga scollegato dall'istanza di SQL Server esistente, usare la vista del catalogo di sistema sys.database_files per esaminare i file associati al database e i relativi percorsi correnti.

Per visualizzare i percorsi dei file fisici del database tramite T-SQL, copiare lo script Transact-SQL seguente nell'editor di query e selezionare Esegui.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'NomeDatabase');

Assicurarsi di tenere conto di tutti i file associati al database prima di scollegare, spostare e collegare. Questo include il logical_file_name, che è il nome utilizzato per fare riferimento al file fisico in tutte le istruzioni Transact-SQL. Il nome del file logico deve essere conforme alle regole per gli identificatori di SQL Server e deve essere univoco tra i nomi di file logici all’interno del database. Il os_file_name, invece, è il nome del file fisico incluso il percorso della directory.

Utilizzo di Strumenti di Terze Parti come Netwrix Auditor

Per una maggiore efficienza, strumenti specifici possono semplificare il processo. Ottieni informazioni dettagliate sulla posizione dei file del database SQL Server in pochi clic con soluzioni dedicate. Con Netwrix Auditor for SQL Server, puoi ottenere un rapporto completo sulle posizioni dei file del database SQL Server che include un riassunto di tutte le impostazioni chiave con solo pochi clic. Apri Netwrix Auditor e vai su Reports -> Predefiniti -> SQL Server - Stato nel Tempo -> Database SQL Server. Clicca View. Puoi anche segnalare e ricevere notifiche sui cambiamenti di configurazione, come modifiche a funzioni importanti, schemi, funzioni, opzioni del database e proprietari degli oggetti.

Gestione e Ottimizzazione dei File di Log (LDF)

Un problema comune è l'eccessiva dimensione dei file di log delle transazioni. Se il t-log è arrivato a queste dimensioni significa che ci sono delle enormi carenze amministrative.

In particolare, le cause possono essere:

Il recovery model è diverso da SIMPLE.
Non è MAI stato eseguito un backup del transaction log.

Entrambe queste condizioni sono spesso vere in situazioni di log sovradimensionati. La prima cosa che si suggerisce di fare è di mettere il recovery model a SIMPLE per ridurre le dimensioni logiche del t-log (ma non quelle fisiche). Una volta impostato il recovery model a SIMPLE puoi ridimensionare il t-log nelle sue dimensioni fisiche ad un valore più consono tramite il comando DBCC SHRINKFILE (nomedb, size). Di questo comando troverai ampia documentazione sul BOL (la guida di SQL Server).

Per evitare di trovarsi nuovamente nella stessa situazione, puoi lasciare il recovery model a SIMPLE (rinunciando quindi a tutte le peculiarità del t-log), oppure puoi impostare il recovery model a BULK LOGGED o a FULL e pianificare una adeguata strategia di backup del t-log per ciascun database. L'impostazione sia del recovery model che della frequenza di backup del t-log (se decidi per avere un recovery model diverso da SIMPLE) devi farla per ciascun database. Attenzione che il "max size" di default non è la dimensione attuale, ma il limite massimo; ovviamente il max size reale sarà quello inferiore tra questo valore e lo spazio disco effettivamente presente.

SQL Server Transaction Log Backup - Step by Step Demo for DBAs

È importante notare che NTBackup non è in grado di fare backup con file in uso. Quando si parla di backup, in SQL Server si intende SOLO il backup fatto (con i comandi T-SQL) del database come oggetto logico. Il backup a livello di file system NON è un backup del database; idem il backup del file con estensione ldf NON è il backup del t-log.

Copiare e Spostare i File MDF e LDF di SQL Server: Metodi e Considerazioni

Questo articolo descrive come associare un database in SQL Server tramite SQL Server Management Studio o Transact-SQL. La copia di file MDF e LDF è spesso necessaria per migrazioni, backup manuali o risoluzione di problemi. Il metodo più comune per spostare o copiare un database tra istanze è tramite le operazioni di scollegamento (detach) e collegamento (attach).

Il Metodo Detach/Attach (Scollegamento e Collegamento)

Nel caso in cui venga spostato un database da un'istanza a un'altra, il database deve prima essere scollegato da qualsiasi istanza di SQL esistente. Se si tenta di collegare un database che non è stato scollegato, viene restituito un errore. Durante il collegamento di un database è necessario che siano disponibili tutti i file di dati del database. Spesso, questi file hanno estensioni .mdf o ndf (per i file di dati) e .ldf (per i file di registro delle transazioni). Inoltre, tutti i filegroup dei dati FILESTREAM devono essere presenti e disponibili.

Procedere con i passaggi di scollegamento, copia file e collegamento del database.

Passaggi in SQL Server Management Studio (SSMS)

Per scollegare un database, fare clic con il pulsante destro del mouse sul database e selezionare Proprietà, quindi cercare l'opzione di scollegamento. Assicurarsi di tenere conto di tutti i file associati al database prima di scollegare, spostare e collegare.
Dopo aver scollegato il database, i suoi file (MDF, NDF, LDF) possono essere copiati manualmente nella nuova posizione o nel nuovo server.
Per collegare un database, avvia Microsoft SQL Server Management Studio (MSSMS). Nel menu File, fai clic su Connect Object Explorer. Nella finestra di dialogo Connect to Server: Nella casella di riepilogo Server type, selezionare Database Engine. Nella casella di testo Server name, digita il nome del server cluster SQL. Nella casella di elenco Authentication, seleziona il tuo metodo di SQL Server Authentication e specifica le credenziali utente.
Nella finestra di dialogo Collega database selezionare Aggiungi per specificare il database da collegare. Consente di visualizzare un'icona che indica lo stato dell'operazione di collegamento. Consente di individuare i file principali del database necessari. Consente di visualizzare i nomi dei file da collegare.
Se un file non esiste, nella colonna Messaggio viene visualizzato "Non trovato". Se non viene trovato un file di log, esiste in un'altra directory o è stato eliminato. È necessario aggiornare il percorso del file nella griglia Dettagli database in modo che indichi la posizione corretta oppure rimuovere il file di log dalla griglia.
Consente di visualizzare il percorso del file di database selezionato.

Metodo Transact-SQL per Collegamento

Il database potrebbe avere file di dati aggiuntivi (comunemente, .mdf o .ndf) e richiedere file aggiuntivi da includere nell'istruzione CREATE DATABASE ... FOR ATTACH. Inoltre, tutti i filegroup dei dati FILESTREAM devono essere inclusi anch'essi nell'istruzione.

In alternativa, è possibile usare la stored procedure sp_attach_db o sp_attach_single_file_db. Tuttavia, queste stored procedure verranno eliminate nelle versioni future di Microsoft SQL Server. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata.

Considerazioni Importanti per la Copia e il Collegamento

Quando si spostano file di database all'interno della stessa istanza, è consigliabile spostare i database usando la procedura di rilocazione pianificata ALTER DATABASE invece delle operazioni di scollegamento e collegamento.
Non è consigliabile usare le operazioni di collegamento e scollegamento per il backup e il ripristino.
Le autorizzazioni di accesso ai file vengono impostate durante l'esecuzione di diverse operazioni del database, inclusi il collegamento e lo scollegamento. Quando un database viene scollegato o collegato, il motore di database tenta di rappresentare l'account di Windows della connessione che esegue l'operazione per garantire che l'account disponga dell'autorizzazione per accedere al database e ai file di log.
È consigliabile evitare di collegare o ripristinare database provenienti da origini sconosciute o non attendibili. Tali database possono contenere codice dannoso che potrebbe eseguire codice Transact-SQL indesiderato o causare errori modificando lo schema o la struttura fisica del database. Prima di utilizzare un database da un'origine sconosciuta o non attendibile, eseguire DBCC CHECKDB sul database in un server non di produzione ed esaminare il codice contenuto nel database, ad esempio le stored procedure o altro codice definito dall'utente.

Aggiornamento del Database dopo il Collegamento

Una volta aggiornato utilizzando il metodo di collegamento, il database viene reso immediatamente disponibile. Il database verrà aggiornato automaticamente al livello di versione interno della nuova istanza. Se il database include indici full-text, questi vengono importati, reimpostati o ricompilati dal processo di aggiornamento, a seconda dell'impostazione della proprietà del server Opzione di aggiornamento full-text. Se l'opzione di aggiornamento è impostata su Importa o Ricompila, gli indici full-text non sono disponibili durante l'aggiornamento. A seconda della quantità di dati indicizzati, l'importazione può richiedere diverse ore, mentre la ricompilazione può risultare 10 volte più lunga.

Dopo l'aggiornamento, il livello di compatibilità del database rimane al livello di compatibilità prima dell'aggiornamento, a meno che il livello di compatibilità precedente non sia supportato nella nuova versione. In tal caso, il livello di compatibilità del database aggiornato viene impostato sul livello di compatibilità più basso supportato. Ad esempio, se si collega un database che aveva un livello di compatibilità pari a 90 prima del collegamento a un'istanza di SQL Server 2019 (15.x), dopo l'aggiornamento il livello viene impostato su 100, ovvero il livello di compatibilità supportato più basso in SQL Server 2019 (15.x).

Architettura dei File di SQL Server: Filegroup e Spazio

I file di SQL Server possono aumentare automaticamente rispetto alla dimensione specificata originariamente. Quando si definisce un file, si può specificare un incremento di crescita specifico. Ogni volta che il file viene si riempie aumenta le sue dimensioni in base all'incremento specificato. La quantità di spazio allocato aumenta automaticamente, ma non diminuisce mai dopo operazioni di delete. Gli snapshot del database vengono utilizzati internamente da alcuni comandi DBCC. Questi comandi includono DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC e DBCC CHECKFILEGROUP.

Un singolo database può essere composto da più file, organizzati in filegroup.

Primary Filegroup: È il filegroup che contiene il file primario. Quando gli oggetti vengono creati nel database senza specificare a quale filegroup appartengono, vengono assegnati al filegroup predefinito. In qualsiasi momento, un filegroup viene designato come filegroup predefinito. Il filegroup PRIMARY è il filegroup predefinito a meno che non venga cambiato tramite l'istruzione ALTER DATABASE.

I filegroup utilizzano una strategia di riempimento proporzionale su tutti i file all'interno di ogni filegroup. Quando i dati vengono scritti nel filegroup, SQL Server Database Engine scrive una quantità proporzionale allo spazio libero nel file su ogni file all'interno del filegroup, invece di scrivere tutti i dati sul primo file fino al riempimento. Ciò significa che per i filegroup contenenti più di un file, non si ha alcun controllo su quale file viene utilizzato per archiviare il singolo oggetto.

La maggior parte dei database funziona bene con un singolo file di dati e un singolo file di transaction log. Se si utilizzano più file di dati, creare un secondo filegroup per il file aggiuntivo e rendere quel filegroup il filegroup predefinito. In questo modo, il file primario conterrà solo tabelle e oggetti di sistema.

Ottimizzazione delle Prestazioni con i Filegroup

Per massimizzare le prestazioni, è possibile implementare diverse strategie:

Creare file o filegroup su dischi diversi il più possibile.
Mettere gli oggetti che utilizzano spazio in modo massivo in filegroup diversi.
Utilizzare i filegroup per consentire il posizionamento di oggetti su dischi fisici specifici.
Inserire tabelle diverse utilizzate nelle stesse query di join in filegroup diversi. Questo passaggio migliorerà le prestazioni, grazie alla ricerca parallela di I/O su disco per i dati in join.
Collocare le tabelle con accesso intensivo e gli indici non cluster che appartengono a tali tabelle su filegroup diversi. L'utilizzo di filegroup diversi migliorerà le prestazioni, a causa dell'I/O parallelo se i file si trovano su dischi fisici diversi.
Non posizionare il o i file di transaction log sullo stesso disco fisico in cui si trovano gli altri file e filegroup.

Se c’è la necessità di estendere un volume o una partizione su cui risiedono file di database utilizzando strumenti come Diskpart, si dovrebbe prima eseguire il backup di tutti i database di sistema e utente e arrestare i servizi di SQL Server.

Rilevamento e Recupero dalla Corruzione dei File MDF

I file MDF sono le basi del meccanismo di archiviazione dati di SQL Server. Questi componenti critici richiedono un rilevamento rapido e metodi di ripristino adeguati in caso di danneggiamento per ridurre al minimo la perdita di dati. La corruzione del database SQL Server richiede un rilevamento rapido e metodi di ripristino adeguati per ridurre al minimo la perdita di dati. Rilevare rapidamente la corruzione dei file MDF può far risparmiare molto tempo di ripristino.

Segnali di Corruzione dei File MDF

I segnali di danneggiamento dei file MDF aiutano ad agire rapidamente in caso di problemi. Tra gli errori comuni troviamo:

Failed to retrieve data for this request. xxx.mdf is not a primary database file.
Attach database failed for Server ‘xxx’. The header for file ‘xxx.mdf’ is not a valid database file header.
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x2abc3894; actual: 0x2ebe208e). It occurred during a read of page (1:1) in database ID 12 at offset 0x00000000002000 in file ‘xxx.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB).

Metodi Nativi di Ripristino

SQL Server fornisce metodi gratuiti per riparare la corruzione dei file MDF. Il metodo di ripristino più affidabile inizia con il ripristino da un backup recente. Questo approccio evita la corruzione dei dati riportando il database a uno stato integro. È possibile utilizzare il comando RESTORE DATABASE con i parametri FROM DISK appropriati per riportare il database allo stato precedente alla corruzione.

Altre opzioni includono l'utilizzo di DBCC CHECKDB. Le opzioni di riparazione presentano dei rischi; ad esempio, REPAIR_ALLOW_DATA_LOSS potrebbe rimuovere completamente le pagine danneggiate, causando la perdita permanente di dati. Ignora inoltre i vincoli di chiave esterna, che possono compromettere l'integrità relazionale tra le tabelle. Gli amministratori del database ottengono l'accesso in sola lettura tramite la modalità di emergenza in caso di errore dell'accesso normale. Il processo ricostruisce il log delle transazioni, ma si perde la coerenza transazionale e si interrompe la catena di ripristino.

Strumenti di Terze Parti e Servizi Online

Se i metodi manuali sopra indicati falliscono, puoi provare strumenti professionali, come DataNumen SQL Recovery, che ti aiuta a riparare i file MDF danneggiati che gli strumenti integrati di SQL Server non sono in grado di gestire. Vedrai una barra di avanzamento che mostra come procede la riparazione. Le aziende con diversi database danneggiati risparmieranno tempo grazie alla riparazione in batch. È possibile aggiungere tutti i file MDF danneggiati a una coda, impostare le preferenze di output e avviare il ripristino. DataNumen SQL Recovery fa più del recupero standard. Solitamente i metodi standard non riescono a riparare i database crittografati dal malware. DataNumen SQL Recovery sfrutta algoritmi speciali per ottenere dati da file MDF crittografati da ransomware senza pagare gli aggressori. Se i file MDF sono stati recuperati da DataNumen Data Recovery (o altri strumenti di recupero dati) non possono essere allegati in SQL Server, questi file potrebbero ancora essere danneggiati.

In alternativa, i servizi online offrono un modo diverso per riparare i database SQL quando scaricare e installare un software di recupero non è possibile o non è conveniente. Questi servizi cloud funzionano con file di database SQL Server come .mdf, .ndf e .ldf. Gli strumenti di recupero offline offrono maggiore controllo e privacy, ma richiedono maggiori competenze tecniche. La maggior parte dei servizi online consente di testarne l'efficacia con demo gratuite. La scelta migliore tra recupero online e offline dipende dalle tue esigenze.

Convalida Post-Ripristino e Prevenzione

Riparare il file MDF danneggiato risolve solo metà del problema. Un controllo di convalida dettagliato garantisce che tutti i dati rimangano intatti dopo la riparazione. Questo comando individua i valori di colonna che potrebbero non essere validi o fuori intervallo per i rispettivi tipi di dati. L'ottimizzazione delle prestazioni diventa il passo successivo una volta verificata l'integrità dei dati. Il team principale dovrebbe innanzitutto ricostruire gli indici più utilizzati per ripristinare rapidamente le prestazioni sui sistemi critici. L'opzione CHECKSUM verifica l'integrità del backup durante l'operazione. Il monitoraggio proattivo aiuta a rilevare potenziali problemi prima che danneggino i dati. Monitoraggio di Azure o SQL Server Management Studio può configurare questi avvisi.

Prevenire è meglio che curare. La manutenzione regolare del database, le corrette procedure di backup e il monitoraggio dello stato del disco riducono sostanzialmente i rischi di corruzione.

tags: #copiare #file #mdf #e #log #di