Spostamento di database SQL Server in un nuovo percorso mediante le funzioni di collegamento e scollegamento di SQL Server

In questo articolo viene descritto come modificare il percorso dei file di dati e di log di un database Microsoft SQL Server 2005, SQL Server 2000 o SQL Server 7.0.

Per ulteriori informazioni su come spostare i database di sistema in SQL Server 2005, vedere l'argomento omonimo della documentazione in linea di SQL Server. Per visualizzare questo argomento, visitare il seguente sito Web MSDN (informazioni in lingua inglese):

La procedura da seguire per modificare il percorso di alcuni database di sistema di SQL Server differisce da quella utilizzata per modificare il percorso dei database utente. Questi casi particolari sono descritti separatamente.

Nota I database di sistema di SQL Server 7.0 non sono compatibili con SQL Server 2000. Non collegare i database master, model, msdb o di distribuzione di SQL Server 7.0 a SQL Server 2000. Se si utilizza SQL Server 2005, è possibile collegare i database di SQL Server 2005 solo a un'istanza. In tutti gli esempi presentati in questo articolo viene presupposto che SQL Server sia installato nella cartella D:\Mssql7. Inoltre viene presupposto che tutti i file di dati e di log si trovino nella cartella predefinita D:\Mssql7\Data. Negli esempi i file di dati e di log di tutti i database vengono spostati nella cartella E:\Sqldata.

Prerequisiti

• Eseguire un backup aggiornato di tutti i database, specialmente del database master, dal percorso attuale.
• Per eseguire questa operazione è necessario disporre di autorizzazioni amministrative.
• Occorre conoscere il nome e il percorso attuale di tutti i file di dati e di log del database.
  
  Nota È possibile determinare il nome e il percorso attuale di tutti i file utilizzati da un database tramite la stored procedure sp_helpfile:

  use <database_name>
  go
  sp_helpfile
  go

• È necessario disporre dell'accesso esclusivo al database da spostare. Se insorgono problemi durante il processo e non è possibile accedere a un database spostato oppure se non è possibile avviare SQL Server, vedere il log degli errori di SQL Server e la documentazione in linea di SQL Server per ulteriori informazioni sugli errori che si sono verificati.

Spostamento dei database utente

Nell'esempio seguente viene spostato un database denominato mydb. Tale database contiene un file di dati, Mydb.mdf, e un file di log, Mydblog.ldf. Se il database da spostare contiene più file di dati o di log, specificarli in un elenco separato da virgole nella stored procedure sp_attach_db. La stored procedure sp_detach_db rimane invariata, indipendentemente dal numero di file contenuti nel database, in quanto essi non vengono elencati nella stored procedure sp_detach_db.

1. Scollegare il database come segue:

   use master
      go
      sp_detach_db 'mydb'
      go

2. Quindi copiare i file di dati e di log dal percorso attuale (D:\Mssql7\Data) nel nuovo percorso (E:\Sqldata).
3. Ricollegare il database. Fare riferimento ai file nel nuovo percorso nel modo seguente:

   use master
     go
     sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
     go

   Verificare la modifica nei percorsi dei file utilizzando la stored procedure sp_helpfile:

   use mydb
      go
      sp_helpfile
      go

   I valori nella colonna filename dovrebbero riflettere i nuovi percorsi.

Nota Nell'articolo 922804 della Microsoft Knowledge Base viene descritto un problema con i database di SQL Server 2005 in un dispositivo NAS. Per ulteriori informazioni, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito: Prendere in considerazione questo problema. Inoltre, prendere in considerazione le autorizzazioni applicate a un database quando è scollegato in SQL Server 2005. Per ulteriori informazioni, vedere la sezione sul collegamento e sullo scollegamento di un database nell'argomento dedicato alla protezione dei file di dati e di log nella documentazione in linea di SQL Server. Per visualizzare questo argomento, visitare il seguente sito Web MSDN (informazioni in lingua inglese):

Spostamento dei database di esempio

Per spostare il database di esempio pubs e il database di esempio Northwind in SQL Server 2000 o in SQL Server 7.0, oppure per spostare il database di esempio AdventureWorks e il database di esempio AdventureWorksDW in SQL Server 2005, attenersi alla procedura di spostamento dei database utente.

Spostamento del database model

SQL Server 7.0

1. Assicurarsi che SQL Server Agent non sia in esecuzione.
2. Attenersi alla stessa procedura di spostamento dei database utente.

SQL Server 2005 e SQL Server 2000

In SQL Server 2005 e in SQL Server 2000 non è possibile scollegare i database di sistema mediante la stored procedure sp_detach_db. Quando si tenta di eseguire l'istruzione sp_detach_db 'model', viene visualizzato un messaggio di errore analogo al seguente: Per rimuovere il database model, è necessario avviare SQL Server con le opzioni -c e -m e il flag di traccia 3608. Quest'ultimo impedisce a SQL Server di ripristinare qualsiasi database che non sia il database master.

Nota Successivamente non sarà possibile accedere ad alcun database utente. Mentre si utilizza questo flag di traccia, è necessario evitare di eseguire operazioni che non siano descritte nella procedura sottostante. Per aggiungere il flag di traccia 3608 come parametro di avvio di SQL Server, attenersi alla procedura seguente:

1. In SQL Server Enterprise Manager fare clic con il pulsante destro del mouse sul nome del server, quindi scegliere Proprietà.
2. Nella scheda Generale fare clic su Parametri di avvio.
3. Aggiungere il seguente nuovo parametro:
   -c -m -T3608

Se si utilizza SQL Server 2005, è possibile utilizzare Gestione configurazione SQL Server per modificare i parametri di avvio del servizio SQL Server. Per ulteriori informazioni su come modificare i parametri di avvio, visitare il seguente sito Web MSDN (informazioni in lingua inglese):Dopo avere aggiunto l'opzione -c, l'opzione -m e il flag di traccia 3608, attenersi alla procedura seguente:

1. Arrestare e riavviare SQL Server.
2. Scollegare il database model utilizzando i comandi seguenti:

   use master
      go
      sp_detach_db 'model'
      go

3. Spostare i file Model.mdf e Modellog.ldf dalla cartella D:\Mssql7\Data alla cartella E:\Sqldata.
4. Ricollegare il database model utilizzando i comandi seguenti:

   use master
      go
      sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
      go

5. Rimuovere -c -m -T3608 dai parametri di avvio in SQL Server Enterprise Manager o in Gestione configurazione SQL Server.
6. Arrestare e riavviare SQL Server. È possibile verificare la modifica nei percorsi dei file utilizzando la stored procedure sp_helpfile. Utilizzare, ad esempio, il seguente comando:

   use model
      go
      sp_helpfile
      go

Spostamento del database MSDB

SQL Server 7.0

Nota Se si utilizza questa procedura durante lo spostamento dei database msdb e model, è necessario ricollegare prima il database model e poi il database msdb. Attenersi alla seguente procedura:

1. Assicurarsi che SQL Server Agent non sia in esecuzione.
2. Seguire la stessa procedura per spostare i database utente.

Nota Se SQL Server Agent è in esecuzione, non è possibile eseguire correttamente la stored procedure sp_detach_db e viene visualizzato un messaggio di errore analogo al seguente:

SQL Server 2005 e SQL Server 2000

Per rimuovere il database MSDB, è necessario avviare SQL Server con le opzioni -c e -m e il flag di traccia 3608. Quest'ultimo impedisce a SQL Server di ripristinare qualsiasi database che non sia il database master. Per aggiungere l'opzione -c, l'opzione -m e il flag di traccia 3608, attenersi alla procedura indicata nella sezione "Spostamento del database model". Dopo avere aggiunto l'opzione -c, l'opzione -m e il flag di traccia 3608, attenersi alla procedura seguente:

1. Arrestare e riavviare SQL Server.
2. Assicurarsi che il servizio SQL Server Agent non sia in esecuzione.
3. Scollegare il database msdb come segue:

   use master
   go
   sp_detach_db 'msdb'
   go

4. Spostare i file Msdbdata.mdf e Msdblog.ldf dal percorso attuale (D:\Mssql8\Data) al nuovo percorso (E:\Mssql8\Data).
5. Rimuovere -c -m -T3608 dalla casella dei parametri di avvio in Enterprise Manager.
6. Arrestare e riavviare SQL Server.
   
   Nota Se si tenta di ricollegare il database msdb avviando SQL Server con le opzioni -c e -m e con il flag di traccia 3608, è possibile che venga visualizzato un messaggio di errore analogo al seguente:
   Server: Msg 615, livello 21, stato 1, riga 1
   Impossibile trovare la tabella di database ID 3, nome "model".
7. Ricollegare il database msdb come segue:

   use master
   go 
   sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf' 
   go

Nota Se si utilizza questa procedura con lo spostamento del database model, si tenta di scollegare il database msdb mentre si scollega il database model. Quando si esegue questa operazione, è necessario ricollegare prima il database model e poi il database msdb. Se si ricollega prima il database msdb, viene visualizzato un messaggio di errore analogo al seguente quando si tenta di ricollegare il database model: In questo caso è necessario scollegare il database msdb, ricollegare il database model e quindi ricollegare il database msdb.

Dopo avere spostato il database msdb, è possibile che venga visualizzato un messaggio di errore analogo al seguente: Questo problema si verifica perché la catena di proprietà è stata infranta. I proprietari del database msdb e del database master non sono gli stessi. In tal caso la proprietà del database msdb è stata modificata. Per ovviare a questo problema, eseguire le seguenti istruzioni Transact-SQL utilizzando l'utilità da riga di comando Osql.exe (SQL Server 7.0 e SQL Server 2000) o Sqlcmd.exe (SQL Server 2005): Per ulteriori informazioni, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito:

Spostamento del database master

1. Modificare il percorso dei file di dati e di log del database master in SQL Server Enterprise Manager.
   
   Nota È possibile modificare anche il percorso del log degli errori.
2. In Enterprise Manager fare clic con il pulsante destro del mouse su SQL Server, quindi scegliere Proprietà.
3. Fare clic su Parametri di avvio per visualizzare le voci seguenti:

   -dD:\MSSQL7\data\master.mdf
      -eD:\MSSQL7\log\ErrorLog
      -lD:\MSSQL7\data\mastlog.ldf

   -d è il percorso completo del file di dati del database master.
   
   -e è il percorso completo del file di log degli errori.
   
   -l è il percorso completo del file di log del database master.
4. Modificare questi valori come segue:
   1. Rimuovere le voci attuali per i file Master.mdf e Mastlog.ldf.
   2. Aggiungere nuove voci specificando il nuovo percorso:

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

5. Arrestare SQL Server.
6. Copiare i file Master.mdf e Mastlog.ldf nel nuovo percorso (E:\Sqldata).
7. Riavviare SQL Server.

Nota Se si utilizza SQL Server 2005, utilizzare Gestione configurazione SQL Server per modificare il percorso dei file di dati e di log del database master.

Spostamento del database tempdb

È possibile spostare i file del database tempdb utilizzando l'istruzione ALTER DATABASE.

1. Determinare i nomi di file logici per il database tempdb utilizzando sp_helpfile come segue:

   use tempdb
   go
   sp_helpfile
   go

   Il nome logico di ogni file è contenuto nella colonna name. In questo esempio vengono utilizzati i nomi di file predefiniti tempdev e templog.
2. Utilizzare l'istruzione ALTER DATABASE specificando il nome del file logico come segue:

   use master
   go
   Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
   go
   Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
   go

   Dovrebbero essere visualizzati i seguenti messaggi che confermano la modifica:
   Messaggio 1
   Il file "tempdev" è stato modificato in sysaltfiles. Dopo il riavvio di SQL Server eliminare il vecchio file.
   Messaggio 2
   Il file "templog" è stato modificato in sysaltfiles. Dopo il riavvio di SQL Server eliminare il vecchio file.
3. Se si utilizza sp_helpfile in tempdb, le modifiche non verranno confermate fino a quando non si riavvia SQL Server.
4. Arrestare e riavviare SQL Server.

Spostamento del database master e del database delle risorse

Per ulteriori informazioni sullo spostamento del database master e del database delle risorse, visitare il seguente sito Web MSDN (informazioni in lingua inglese):

Per ulteriori informazioni, fare clic sui numeri degli articoli della Microsoft Knowledge Base riportati di seguito:
Per ulteriori informazioni, vedere i seguenti libri (in lingua inglese):