Eliminazione di tutti i dati da un database di SQL Server

Da Joe Kunk

Ci sono momenti come sviluppatore che vorrete essere in grado di cancellare tutti i dati da un database nel modo più efficiente possibile. Purtroppo, così facendo può non essere così facile come sembra.

Ci sono momenti come sviluppatore che vorrete essere in grado di cancellare tutti i dati da un database nel modo più efficiente possibile. No, non sto cercando di invogliare al lato oscuro e il tuo nome apparirà come un titolo sul telegiornale locale. Quando viene utilizzato per scopi pacifici, la possibilità di cancellare in modo efficiente i dati da un database può essere molto utile per lo sviluppo e la verifica di database di grandi dimensioni o altamente confidenziali.

Avevo bisogno di questa capacità in un progetto di consulenza recente per costruire un sistema di gestione cruscotto di reporting per un database molto grande medico su un server con una connessione lenta DSL.

Avevo bisogno di copiare il database di SQL Server, con le sue modifiche allo schema più recenti, fino al mio portatile per una dimostrazione cliente in un luogo in cui la connettività a Internet potrebbero non essere disponibili. Anche compresso, il backup del database avrebbe preso la maggior parte della serata di copiare dal server remoto. In caso di non copiare, non avrei tempo di provare di nuovo prima della dimostrazione. Non ho bisogno di dati reali, una copia vuota del database più recente sarebbe sufficiente. Potrei facilmente aggiungere un record di prova pochi per la dimostrazione dopo l'installazione del database sul mio portatile.

Ho creato una copia del database sul server remoto e ha generato uno script SQL per troncare tutte le tabelle nel database di copia come mostrato nel Listato 1. Dopo un rapido backup del database appena pulito scaricato al mio portatile, vorrei essere a casa in tempo per 06:00.

'TRUNCATE TABLE' SELECT + ['+ + TABLE_CATALOG'].['+ TABLE_SCHEMA'].['+ TABLE_NAME + +'] 'DA DOVE INFORMATION_SCHEMA.TABLES TABLE_TYPE =' BASE TABLE 'ORDER BY TABLE_NAME

Listato 1. SQL Server 2005 o versioni successive script per generare istruzioni TRUNCATE per tutte le tabelle.

Chiunque abbia provato questo su tutto, ma il database più semplicistica ha trovato, come ho fatto io, che questo non funzionerà. Qualsiasi tabella a cui fa riferimento una relazione di chiave, partecipa a una vista indicizzata, o viene pubblicato utilizzando transazionale o di tipo merge non può essere troncato. Un DELETE FROM funziona in questi casi, ma richiede risorse di sistema molto più che ogni riga è individualmente cancellato e incluso nel log delle transazioni. Un altro potenziale svantaggio è che le colonne IDENTITY non sono riportati al loro valore Seed originale con il DELETE FROM. Una dichiarazione TRUNCATE è preferibile in quanto cade proprio tutte le pagine di dati associati al tavolo, non si innesca il fuoco tavolo, e le colonne IDENTITY resetta.

Nel caso di cui sopra, ho fatto ricorso a usare DELETE FROM istruzioni per ogni tavolo, e subito la pena di prestazioni. Che cosa avrei preferito è la capacità di eseguire uno script SQL per cancellare tutti i dati dal database utilizzando il metodo più efficiente possibile su un tavolo da base da tavolo, nel giusto ordine.

Nella parte 2 di questo articolo, vi presento il codice sorgente di un programma Visual Basic per generare uno script SQL per eliminare in modo efficiente tutti i dati da un database a seconda della configurazione attuale e le autorizzazioni disponibili della connessione al database. Relazioni di chiave esterna vengono eliminati e ricreati, se possibile, per consentire a più tabelle di partecipare alla dichiarazioni TRUNCATE. Le colonne Identity vengono esplicitamente reimpostato se la tabella non può essere troncato. Tabelle figlio vengono cancellati prima di tabelle padre.

Come precauzione di sicurezza, il programma non esegue alcuna direttamente o DELETE FROM TRUNCATE azioni, che richiedono invece una sufficiente conoscenza degli strumenti di database a cui connettersi ed eseguire uno script SQL. Per chiarezza, lo script non fa uso di loop, ogni azione che interessano un tavolo è individualmente elencati in ordine di presentare un quadro chiaro della esattamente le azioni che verranno eseguite, per la revisione e l'approvazione prima di eseguire lo script.

Vi invito a condividere le vostre esperienze quando si lavora con database di grandi dimensioni in ambito online commenti di questo articolo. Hai bisogno di cancellare un database come ho descritto qui? Avete trovato un modo chiaro per realizzarlo? Quali sono le altre sfide che avete affrontato e forse risolto quando si lavora con database di grandi dimensioni?

Corso SQL Server - Corso Hyper-V -Corso Windows Server –

Certificazione Microsoft