🥇Trasferimento dei dati del backup della nuova versione di MS SQL Server su una versione precedente

Antefatti

Una volta, per riprodurre un bug, avevo bisogno di un backup del database di produzione.

Con mia sorpresa, ho incontrato le seguenti limitazioni:

Il backup del database era stato fatto sulla versione SQL Server 2016 e non era compatibile con il mio SQL Server 2014.
Sul mio computer di lavoro, come sistema operativo, c'era 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 accedeva anche ad altri prodotti e database, quindi la sua distribuzione su un'altra stazione avrebbe potuto richiedere molto tempo.

Tenuto conto di quanto sopra, ho concluso che era giunto il momento di qualche soluzione alternativa.

Ripristino dei dati dal backup

Ho deciso di utilizzare una macchina virtuale Oracle VM VirtualBox con Windows 10 (puoi prendere un'immagine di prova per il browser Edge da qui). Sulla macchina virtuale è stato installato SQL Server 2016 e su di essa è stato ripristinato il database dell'applicazione dal backup (istruzioni).

Impostazione dell'accesso a SQL Server sulla macchina virtuale

Successivamente, era necessario intraprendere alcuni passaggi affinché fosse possibile accedere a SQL Server dall'esterno:

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

Trasferimento dei dati

Il trasferimento dei dati consiste in due fasi:

Trasferimento dello schema dati (tabelle, viste, stored procedure, 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 questo caso verranno creati script per tutti gli oggetti del database).
Indichiamo le impostazioni per salvare lo 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, è possibile eseguirlo 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 dal backup e del database creato dallo script. Nel mio caso, nello script mancava l'impostazione per COLLATE, il che portava a errori nel trasferimento dei dati e a continue modifiche nella ricreazione del database usando uno script aggiornato.

Trasferimento dei dati

Prima di trasferire i dati, è necessario disabilitare il controllo di tutti i vincoli sul database:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

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

Specifichiamo 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 sopra menzionata.
Specifichiamo le impostazioni di connessione per la destinazione (SQL Server 2014 sulla macchina host).
Successivamente, configuriamo il mapping. È necessario selezionare tutti non read-only oggetti (ad esempio, non è necessario selezionare le viste). Come opzioni aggiuntive, si consiglia di selezionare «Consenti inserimenti nelle colonne identity», se utilizzate.
Attenzione: se, tentando di selezionare più tabelle e assegnare loro una proprietà «Consenti inserimenti nelle colonne identity» la proprietà era già stata impostata in precedenza per almeno una delle tabelle selezionate, nella finestra di dialogo sarà indicato che la proprietà è già impostata per tutte le tabelle selezionate. Questo fatto può confondere e portare a errori nel trasferimento.
Avviamo il trasferimento.

Ripristiniamo il controllo dei vincoli:

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 correzioni e ripetiamo il trasferimento dei dati.

Conclusione

Questa situazione si verifica abbastanza raramente e si manifesta solo a causa dei vincoli sopra indicati. La maggior parte delle volte, la soluzione consiste nell'aggiornare SQL Server o connettersi a un server remoto, se l'architettura dell'applicazione lo consente. Tuttavia, nessuno è esente da codice legacy e da errori di sviluppo. Spero che questa guida non ti sarà necessaria, e se dovesse esserti utile, ti aiuterà a risparmiare una grande quantità di tempo e nervi. Grazie per l'attenzione!