Spostare i database di sistema

Si applica a: SQL Server

Questo articolo illustra come ripristinare i database di sistema in SQL Server. Lo spostamento dei database di sistema potrebbe risultare utile nelle situazioni seguenti:

• Recupero da errore. Ad esempio, il database è in modalità sospetta oppure viene chiuso a causa di un errore hardware.

• Rilocazione pianificata.

• Rilocazione per una manutenzione pianificata del disco.

Le procedure seguenti consentono di spostare i file di database all'interno della stessa istanza di SQL Server. Per spostare un database in un'altra istanza di o in un altro server, usare l'operazione di backup e ripristino.

Le procedure descritte in questo articolo richiedono il nome logico dei file di database. Per ottenere il nome, eseguire una query sulla colonna name della vista del catalogo sys.master_files .

Importante

Se si sposta un database di sistema e successivamente si ricompila il database master, è necessario spostare nuovamente il database di sistema, in quanto l'operazione di ricompilazione ha come conseguenza l'installazione di tutti i database di sistema nei rispettivi percorsi predefiniti.

Spostare i database di sistema

Per spostare un file di dati o di log del database di sistema nell'ambito di un'operazione di rilocazione pianificata o di manutenzione pianificata, attenersi alla procedura seguente. Sono inclusi i database di sistema model, msdb e tempdb.

Importante

Questa procedura è valida per tutti i database di sistema ad eccezione dei database master e Resource. Vedere più avanti in questo articolo per i passaggi per spostare il database master. Il database Resource non può essere spostato.

1. Registrare il percorso esistente dei file di database da spostare riesaminando la vista del catalogo sys.master_files.

2. Verificare che l'account del servizio per il motore di database SQL Server disponga delle autorizzazioni complete per il nuovo percorso dei file. Per altre informazioni, vedere Configurare account di servizio e autorizzazioni di Windows. Se l'account del servizio motore di database non può controllare i file nel nuovo percorso, l'istanza di SQL Server non viene avviata.

3. Per ogni file di database che si desidera spostare, eseguire l'istruzione seguente.

   ALTER DATABASE database_name
       MODIFY FILE (NAME = logical_name, FILENAME = 'new_path\os_file_name');
   

   Fino al riavvio del servizio, il database continuerà a utilizzare i file di dati e di resoconto nella posizione esistente.

4. Arrestare l'istanza di SQL Server per eseguire la manutenzione. Per ulteriori informazioni, vedere Avviare, arrestare, sospendere, riprendere e riavviare i servizi SQL Server.

5. Copiare il file di database o i file nella nuova posizione. Questo non è un passaggio necessario per il database di sistema tempdb, tali file verranno creati automaticamente nel nuovo percorso.

6. Riavviare il servizio SQL Server per l'istanza del server. Per ulteriori informazioni, vedere Avviare, arrestare, sospendere, riprendere e riavviare i servizi SQL Server.

7. Verificare la modifica ai file eseguendo la query riportata di seguito. I database di sistema devono segnalare i nuovi percorsi dei file fisici.

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

8. Poiché nel passaggio 5 i file di database sono stati copiati anziché spostati, ora è possibile eliminare in modo sicuro i file di database inutilizzati dal percorso precedente.

Completamento: dopo lo spostamento dei database di sistema msdb

Se il database msdb viene spostato e la posta elettronica database è configurata, completare i seguenti passaggi aggiuntivi.

1. Verificare che Service Broker è abilitato per il database msdb eseguendo la query seguente.

   SELECT is_broker_enabled
   FROM sys.databases
   WHERE name = N'msdb';
   

   Se Service Broker non è abilitato per msdb, deve essere riabilitato per far funzionare Posta elettronica database. Per altre informazioni, vedere ALTER DATABASE ... SET ENABLE_BROKER.

   ALTER DATABASE msdb
       SET ENABLE_BROKER
       WITH ROLLBACK IMMEDIATE;
   

   Accertare che il valore di is_broker_enabled sia ora 1.

2. Verificare il funzionamento di Posta elettronica database inviando un messaggio di prova.

Procedura di recupero da errore

Se è necessario spostare un file a causa di un errore hardware, eseguire la procedura seguente per rilocare il file in una nuova posizione. Questa procedura è valida per tutti i database di sistema ad eccezione dei database master e Resource. Gli esempi seguenti usano il prompt della riga di comando di Windows e l'utilità sqlcmd.

Importante

Se non è possibile avviare il database, ossia se il database è in modalità sospetta o in stato non recuperato, il file può essere spostato solo dai membri del ruolo predefinito di amministratore di sistema.

1. Verificare che l'account del servizio per il motore di database SQL Server disponga delle autorizzazioni complete per il nuovo percorso dei file. Per altre informazioni, vedere Configurare account di servizio e autorizzazioni di Windows. Se l'account del servizio motore di database non può controllare i file nel nuovo percorso, l'istanza di SQL Server non viene avviata.

2. Arrestare l'istanza di SQL Server, se avviata.

3. Avviare l'istanza di SQL Server in modalità di recupero del solo master digitando uno dei comandi seguenti al prompt dei comandi. Usare il parametro di avvio 3608 impedisce a SQL Server di avviare e recuperare automaticamente database ad eccezione del database master. Per altre informazioni, vedere Parametri di avvio e TF3608.

   I parametri specificati in questi comandi fanno distinzione tra maiuscole e minuscole. I comandi hanno esito negativo se i parametri non vengono specificati come illustrato.

   Per l'istanza predefinita (MSSQLSERVER), eseguire il comando seguente:

   NET START MSSQLSERVER /f /T3608
   

   Per un'istanza denominata, eseguire il comando riportato di seguito:

   NET START MSSQL$instancename /f /T3608
   

   Per ulteriori informazioni, vedere Avviare, arrestare, sospendere, riprendere e riavviare i servizi SQL Server.

4. Immediatamente dopo l'avvio del servizio con il flag di traccia 3608 e /f, avviare una connessione sqlcmd al server per richiedere la connessione singola disponibile. Ad esempio, quando si esegue sqlcmd localmente nello stesso server dell'istanza predefinita (MSSQLSERVER) e per connettersi con l'autenticazione di integrazione di Active Directory, eseguire il comando seguente:

   sqlcmd
   

   Per connettersi a un'istanza denominata nel server locale con l'autenticazione di integrazione di Active Directory:

   sqlcmd -S localhost\instancename
   

   Per altre informazioni sulla sintassi sqlcmd, vedere Utilità sqlcmd.

   Per ogni file da spostare, usare i comandi sqlcmd oppure SQL Server Management Studio per eseguire la seguente istruzione. Per altre informazioni su come usare l'utilità sqlcmd , vedere Usare l'utilità sqlcmd. Dopo aver aperto la sessione sqlcmd, eseguire l'istruzione seguente una volta per ogni file da spostare:

   ALTER DATABASE database_name
       MODIFY FILE (NAME = logical_name, FILENAME = 'new_path\os_file_name');
   GO
   

5. Uscire dall'utilità sqlcmd o da SQL Server Management Studio.

6. Arrestare l'istanza di SQL Server. Ad esempio, eseguire NET STOP MSSQLSERVER nel prompt della riga di comando.

7. Copiare il file o i file nel nuovo percorso.

8. Riavviare l'istanza di SQL Server. Ad esempio, eseguire NET START MSSQLSERVER nel prompt della riga di comando.

9. Verificare la modifica ai file eseguendo la query riportata di seguito.

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

10. Poiché nel passaggio 7 i file di database sono stati copiati anziché spostati, ora è possibile eliminare in modo sicuro i file di database inutilizzati dal percorso precedente.

Spostare il database master

Per spostare il database master, effettuare le operazioni seguenti.

1. Verificare che l'account del servizio per il motore di database SQL Server disponga delle autorizzazioni complete per il nuovo percorso dei file. Per altre informazioni, vedere Configurare account di servizio e autorizzazioni di Windows. Se l'account del servizio motore di database non può controllare i file nel nuovo percorso, l'istanza di SQL Server non viene avviata.

2. Dal menu Start, individuare e avviare Gestione configurazione SQL Server. Per altre informazioni sul percorso previsto, vedere Gestione configurazione SQL Server.

3. Nel nodo Servizi di SQL Server, fare clic con il pulsante destro del mouse sull'istanza di SQL Server, ad esempio SQL Server (MSSQLSERVER), e scegliere Proprietà.

4. Nella finestra di dialogo Proprietà (nome_istanza) di SQL Server selezionare la scheda Parametri di avvio.

5. Nella casella Parametri esistenti, selezionare il parametro -d. Nella casella Specificare un parametro di avvio, modificare il parametro sul nuovo percorso del file di dati master. Seleziona Aggiorna per salvare la modifica.

6. Nella casella Parametri esistenti, selezionare il parametro -l. Nella casella Specificare un parametro di avvio, modificare il parametro sul nuovo percorso del file di resoconto master. Seleziona Aggiorna per salvare la modifica.

   Il valore del parametro per il file di dati deve seguire il parametro -d e il valore per il file di log deve seguire il parametro -l . L'esempio seguente illustra i valori dei parametri per il percorso predefinito del file di dati master.

   -dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf
   -lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
   

   Se la rilocazione pianificata del file di dati master è E:\SQLData, i valori dei parametri verranno modificati nel modo seguente:

   -dE:\SQLData\master.mdf
   -lE:\SQLData\mastlog.ldf
   

7. Selezionare OK per salvare in modo permanente le modifiche e chiudere la finestra di dialogo Proprietà (nome istanza) di SQL Server.

8. Arrestare l'istanza di SQL Server facendo clic con il pulsante destro del mouse sul nome dell'istanza e scegliendo Arresta.

9. Copiare i file master.mdf e mastlog.ldf nel nuovo percorso.

10. Riavviare l'istanza di SQL Server.

11. Verificare la modifica dei file per il database master eseguendo la query seguente.

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

12. A questo punto SQL Server dovrebbe essere eseguito normalmente. Microsoft consiglia comunque di modificare anche la voce del Registro di sistema in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup, dove instance_ID è simile a MSSQL13.MSSQLSERVER. In tale hive, modificare il valore SQLDataRoot impostando il nuovo percorso della nuova posizione dei file di database master. Se non si aggiorna il Registro di sistema, l'applicazione di patch e l'aggiornamento possono avere esito negativo.

13. Poiché nel passaggio 9 i file di database sono stati copiati anziché spostati, ora è possibile eliminare in modo sicuro i file di database inutilizzati dal percorso precedente.

Spostamento del database delle risorse

Il percorso di Resource del database è \<drive>:\Program Files\Microsoft SQL Server\MSSQL\<version>.<instance_name>\MSSQL\Binn\. Il database non può essere spostato.

Completamento: dopo lo spostamento di tutti i database di sistema

Se tutti i database di sistema sono stati spostati in un nuovo volume o unità oppure in un altro server con una lettera di unità diversa, effettuare gli aggiornamenti riportati di seguito.

• Modificare il percorso del log di SQL Server Agent Se non si aggiorna questo percorso, non è possibile avviare SQL Server Agent.

• Modificare il percorso predefinito del database. La creazione di un nuovo database potrebbe non venir completata correttamente se la lettera di unità e il percorso specificati come posizione predefinita non esistono.

Modificare il percorso del log di SQL Server Agent

Se tutti i database di sistema sono stati spostati in un nuovo volume o hanno migrato a un altro server con una lettera di unità diversa e il percorso del file SQLAGENT.OUT di log degli errori di SQL Agent non esiste più, effettuare i seguenti aggiornamenti.

1. In Esplora oggetti di SQL Server Management Studio, espandere SQL Server Agent.

2. Fare clic con il pulsante destro del mouse sulla cartella Log degli errori e selezionare Configura.

3. Nella finestra di dialogo Configura log degli errori di SQL Server Agent specificare il nuovo percorso del file SQLAGENT.OUT. Il percorso predefinito è C:\Program Files\Microsoft SQL Server\MSSQL\<version>.<instance_name>\MSSQL\Log\.

Modificare il percorso predefinito del database

1. In SQL Server Management Studio connettersi a all'istanza di SQL Server desiderata in Esplora oggetti. Fare clic con il pulsante destro del mouse sull'istanza e selezionare Proprietà.

2. Nella finestra di dialogo Proprietà server selezionare Impostazioni database.

3. In Percorsi predefiniti databaseselezionare il nuovo percorso sia per i file di dati sia per quelli di log.

4. Per completare la modifica, avviare e arrestare il servizio SQL Server.

Esempi

R. Spostare il database tempdb

Nell'esempio seguente i file dei dati e di log del database tempdb vengono spostati in un nuovo percorso nell'ambito di una rilocazione pianificata.

Suggerimento

Cogli l'occasione di riesaminare i file tempdb per ottenere dimensioni e posizione ottimali. Per altre infiormazioni, vedere Ottimizzazione delle prestazioni di tempdb in SQL Server.

Poiché tempdb viene ricreato a ogni avvio dell'istanza di SQL Server, non è necessario spostare fisicamente i file di dati e di log. I file vengono creati nel nuovo percorso quando il servizio viene riavviato nel passaggio 4. Fino al riavvio del servizio, tempdb continua a utilizzare i file di dati e di resoconto nella posizione esistente.

1. Determinare i nomi dei file logici del database tempdb e la relativa posizione corrente sul disco.

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

2. Verificare che l'account del servizio per il motore di database SQL Server disponga delle autorizzazioni complete per il nuovo percorso dei file. Per altre informazioni, vedere Configurare account di servizio e autorizzazioni di Windows. Se l'account del servizio motore di database non può controllare i file nel nuovo percorso, l'istanza di SQL Server non viene avviata.

3. Modificare il percorso di ogni file tramite ALTER DATABASE.

   USE master;
   GO
   
   ALTER DATABASE tempdb
       MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   
   ALTER DATABASE tempdb
       MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
   GO
   

   Fino al riavvio del servizio, tempdb continua a utilizzare i file di dati e di resoconto nella posizione esistente.

4. Arrestare e riavviare l'istanza di SQL Server.

5. Verificare la modifica ai file.

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

6. Eliminare i file tempdb inutilizzati dal percorso originale.

Contenuto correlato

• Database Resource
• Database tempdb
• Database master
• database msdb
• Database modello
• Spostare database utente
• Spostare file del database
• Avviare, arrestare, sospendere, riprendere e riavviare i servizi SQL Server
• ALTER DATABASE (Transact-SQL)
• Ricompilare i database di sistema