Capita frequentemente che durante i progetti di consolidamento di una infrastruttura SQL, ci sia la necessità di spostare i file dei database di sistema, tra cui il TempDB, su una nuova unità. Questo articolo spiega tutti i passaggi da seguire per spostare i file del database temporaneo utilizzato da SQL Server.
Overview sullo spostamento del TempDB in SQL Server
Partiamo con una nota importante: SQL Server non supporta lo spostamento del database TempDB utilizzando il backup-restore e gli altri metodi di detach-attach del database. Se fate una prova, otterrete degli errori di questo tipo:
Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
Dobbiamo quindi seguire necessariamente questi passaggi che andremo ad analizzare successivamente:
- Identificare la posizione dei dati e del file di log del TempDB
- Modificare la posizione dei dati e del file di log del TempDB utilizzando il comando ALTER DATABASE
- Arrestare e riavviare il servizio SQL Server
- Verificare la modifica del percorso dei file
- Eliminare i vecchi file tempdb.mdf e templog.ldf
Identificare la posizione dei dati e del file di log del TempDB
Se utilizziamo SSMS, in una nuova query window, eseguiamo questo script per identificare la posizione dei dati TempDB e del file di log:
USE master; GO SELECT name AS [LogicalName] ,physical_name AS [Location] ,state_desc AS [Status] FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); GO
Dovreste ottenere un risultato simile a questo:
Nella mia macchina di sviluppo, il TempDB è composto da 5 file: 4 file dati (nelle posizioni 1,3,4,5) ed un file di log (posizione 2). Potreste trovare solo i file presenti nelle posizioni 1 e 2 ma la procedura di modifica del percorso non cambia.
Il passaggio successivo sarà creare le rispettive cartelle sulla nuova unità in cui si desidera salvare il TempDB. È fondamentale assicurarsi che la nuova posizione sia accessibile da SQL Server: l'account con cui è in esecuzione il servizio SQL Server deve disporre delle autorizzazioni di lettura e scrittura per la cartella in cui saranno archiviati i file.
Modificare la posizione dei dati e del file di log del TempDB
Eseguiamo il comando ALTER DATABASE riportato di seguito per modificare il percorso del TempDB. Metterò il database all’interno del percorso “D:\MSSQL\DATA\”.
Questa operazione deve essere fatta con il servizio SQL avviato e non ha nessun effetto immediato sull’operatività degli utenti che potranno continuare a lavorare indisturbati.
USE master; GO ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev ,FILENAME = 'D:\MSSQL\DATA\tempdb.mdf' ); GO ALTER DATABASE tempdb MODIFY FILE ( NAME = templog ,FILENAME = 'D:\MSSQL\DATA\templog.ldf' ); GO ALTER DATABASE tempdb MODIFY FILE ( NAME = temp2 ,FILENAME = 'D:\MSSQL\DATA\tempdb_mssql_2.ndf' ); GO ALTER DATABASE tempdb MODIFY FILE ( NAME = temp3 ,FILENAME = 'D:\MSSQL\DATA\tempdb_mssql_3.ndf' ); GO ALTER DATABASE tempdb MODIFY FILE ( NAME = temp4 ,FILENAME = 'D:\MSSQL\DATA\tempdb_mssql_4.ndf' ); GO
Se l’operazione ha avuto successo, riceveremo un messaggio di questo tipo:
Come notate, il messaggio indica chiaramente che il nuovo percorso sarà utilizzato al prossimo riavvio del database. Essendo il TempDB creato ad ogni riavvio del servizio SQL, il prossimo passaggio sarà proprio questo.
Arrestare e riavviare il servizio SQL Server
Arrestiamo il servizio SQL Server e facciamolo ripartire in modo che le modifiche abbiano effetto.
Verificare la modifica del percorso dei file
Eseguiamo nuovamente lo script al punto 1 e verifichiamo che il percorso sia stato effettivamente modificato:
Eliminare i vecchi file tempdb.mdf e templog.ldf
Come accennato precedentemente, ogni volta che il servizio SQL viene fatto partire, il TempDB viene ricreato. Avendo cambiato percorso, i vecchi file, non più utilizzati, saranno ancora presenti nel percorso di partenza e possiamo eliminarli senza problemi.
Conclusioni
Spostare il TempDB è un’operazione semplice se abbiamo chiari tutti i passaggi. Fate particolare attenzione ai diritti di lettura e scrittura per l’utente di servizio: è la prima causa di fallimento di tutto il processo descritto. 😊
