🥇Trasferimento dei dati di un backup da una nuova versione di MS SQL Server a una versione più vecchia

Contesto

Un giorno, per riprodurre un bug, mi serviva un backup del database di produzione.

Con mio grande stupore, mi sono imbattuto nelle seguenti limitazioni:

Il backup del database è stato effettuato sulla versione SQL Server 2016 e non era compatibile con il mio SQL Server 2014.
Sul mio computer di lavoro era installato Windows 7, quindi non potevo aggiornare SQL Server alla versione 2016
Il prodotto supportato faceva parte di un sistema più grande con un'architettura legacy fortemente interconnessa e interagiva anche con altri prodotti e database, quindi il suo deployment su un'altra stazione poteva richiedere molto tempo.

Tenendo conto di quanto sopra, ho concluso che era giunto il momento di fare affidamento su soluzioni non convenzionali.

Ripristino dei dati dal backup

Ho deciso di usare una macchina virtuale Oracle VM VirtualBox con Windows 10 (è possibile ottenere un'immagine di prova per il browser Edge da qui). Sulla macchina virtuale è stato installato SQL Server 2016 e il database dell'applicazione è stato ripristinato dal backup (una guida).

Configurazione dell'accesso a SQL Server sulla macchina virtuale

Successivamente, è stato necessario intraprendere alcuni passaggi per consentire l'accesso a SQL Server dall'esterno:

Per il firewall, aggiungere una regola per consentire le richieste sulla porta 1433.
È consigliabile che l'accesso al server non avvenga tramite autenticazione di Windows, ma tramite SQL con login e password (più facile da configurare). Tuttavia, in questo caso, non dimenticare di abilitare nelle proprietà di SQL Server la possibilità di autenticazione SQL.
Nelle impostazioni utente su SQL Server, nella scheda User Mapping specificare per il database ripristinato il ruolo utente db_securityadmin.

Trasferimento dati

Il trasferimento dati si compone essenzialmente di due fasi:

Trasferimento dello schema dati (tabelle, viste, procedure memorizzate, ecc.)
Trasferimento dei dati stessi

Trasferimento dello schema dati

Eseguiamo le seguenti operazioni:

Selezioniamo Tasks -> Generate Scripts per il database da trasferire.
Selezioniamo gli oggetti necessari per il trasferimento o lasciamo il valore predefinito (in tal caso verranno creati script per tutti gli oggetti del database).
Indichiamo le impostazioni per il salvataggio dello script. È più comodo salvare lo script in un unico file in codifica Unicode. In questo modo, in caso di errore, non sarà necessario ripetere tutti i passaggi.

Dopo aver salvato lo script, può essere eseguito sul SQL Server di origine (versione precedente) per creare il database richiesto.

Attenzione: Dopo l'esecuzione dello script, è necessario verificare la corrispondenza delle impostazioni del database tra il backup e il database creato dallo script. Nel mio caso, lo script mancava dell'impostazione per COLLATE, il che ha comportato un errore nel trasferimento dei dati e ulteriori complicazioni nella ricreazione del database tramite lo script modificato.

Trasferimento dati

Prima di trasferire i dati, è necessario disabilitare il controllo di tutte le restrizioni sul database:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Il trasferimento dei dati avviene tramite la procedura guidata di importazione dati. Tasks -> Import Data su SQL Server, dove si trova il database creato dallo script:

Indichiamo le impostazioni di connessione alla sorgente (SQL Server 2016 su una macchina virtuale). Ho utilizzato Data Source SQL Server Native Client e l'autenticazione SQL menzionata sopra.
Indichiamo le impostazioni di connessione al luogo di destinazione (SQL Server 2014 sulla macchina host).
Successivamente, configuriamo il mapping. È necessario selezionare tutti i non read-only oggetti (ad esempio, non è necessario selezionare le viste). Come opzione aggiuntiva, è consigliabile scegliere «Consentire l'inserimento in colonne di identità», se utilizzate.
Attenzione: se durante il tentativo di selezionare più tabelle si deve impostare loro questa proprietà. «Consentire l'inserimento in colonne di identità» la proprietà è stata già impostata per almeno una delle tabelle selezionate, nel dialogo verrà indicato che la proprietà è già impostata per tutte le tabelle selezionate. Questo fatto può confondere e portare a errori di trasferimento.
Avviamo il trasferimento.

Ripristiniamo il controllo delle restrizioni:

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Se si verificano errori, controlliamo le impostazioni, eliminiamo il database creato con errori, lo ricreiamo dallo script, apportiamo le modifiche necessarie e ripetiamo il trasferimento dei dati.

Conclusione

Questa operazione si verifica piuttosto raramente ed è causata solo dalle restrizioni sopra menzionate. Nella maggior parte dei casi, la soluzione consiste nell'aggiornamento del SQL Server o nella connessione a un server remoto, se l'architettura dell'applicazione lo consente. Tuttavia, nessuno è immune da codice legacy e da sviluppi di scarsa qualità. Spero che tu non abbia bisogno di questa guida, ma se dovessi averne necessità, spero che possa aiutarti a risparmiare tempo e nervi. Grazie per l'attenzione!