Sie haben eine MS SQL Server-Datenbank, die Sie auf einen anderen physischen Computer übertragen müssen. Sie haben bereits ein Backup erstellt und beginnen gerne mit einer Wiederherstellung. Es stellt sich jedoch heraus, dass auf dem Computer, auf den Sie die Datenbank übertragen möchten, eine ältere Version von MS SQL Server installiert ist. Der Stapelüberlauf versichert Ihnen, dass alles schlecht ist. Aber ist es wirklich so?
Das Übertragen einer Datenbank von einer neueren Version auf eine alte ist natürlich kein klassisches und nicht das korrekteste Szenario. Oft werden die Datenbanken jedoch so erstellt, dass sie immer mehr neue Versionen von SQL unterstützen, beginnend mit einigen, beispielsweise 2008 R2, weil Die direkte Kompatibilität mit MS SQL ist mehr als hervorragend. Beispielsweise hat Ihr Client MS SQL 2016 bereits für sich selbst installiert, und Sie haben MS SQL 2014 für die Entwicklung auf Ihrem Testserver installiert. Außerdem möchten Sie Ihre Client-Basis erweitern, um herauszufinden, wo Datenverwirrungen auftreten.
Microsoft hat das Problem abgelehnt - sie sagen, dass sie keine Abwärtskompatibilität haben und es keine Pleite gibt. Die auf dem neueren Server erstellte Sicherung kann nicht auf dem älteren Server wiederhergestellt werden. Ja, sie haben Tools wie DTS, Kopieren einer Datenbank, Export-Import usw. Sie sind jedoch so unpraktisch und umständlich, dass die normale Übertragung einer großen Datenbank mit vielen Tabellen nicht besonders bequem ist. Auf jeden Fall war ich persönlich nicht erfolgreich.
Ja, Sie können SQL-Skripte für die gesamte Datenbank einschließlich Daten generieren. Stellen Sie sich jedoch vor, Sie haben eine Reihe von Blob-Feldern mit Big Data in der Datenbank, und im Allgemeinen beträgt die Größe der gesamten Datenbank mehr als 500 GB. Stellen Sie sich vor, wie viel ein solches Skript benötigt, wie viel Zeit es generiert und ausgeführt wird.
Die Aufgabe besteht also darin, die Datenbank (Struktur und Daten) aus der neuen Version von MS SQL Server in der älteren Version genau neu zu erstellen. Ich habe eine ziemlich einfache Lösung gefunden, die ich teilen möchte. Natürlich weist diese Lösung eine erhebliche Anzahl von Einschränkungen auf, aber meiner Meinung nach ist sie immer noch besser als DTS und Skripte.
Die größte Einschränkung besteht darin, dass Sie über MS SQL Management Studio Zugriff auf beide Server benötigen - alte und neue. Wenn dies nicht möglich ist, sollte es auf dem Computer möglich sein, von dem Sie die Datenbank übertragen möchten, die SQL-Version zu installieren, in der Sie die Datenbank übertragen müssen, die Datenbank zuerst lokal auf diese Version zu übertragen und sie dann durch die Sicherung oder direkt über * df zu ziehen Datenbankdateien (über Trennen / Anhängen) auf dem neuen Computer (die Version von SQL Server stimmt in diesem Fall bereits überein).
Eine weitere Einschränkung besteht darin, dass Sie ein Datenbankschemaskript (alle Objekte, einschließlich Tabellen, Indizes, Konstanten, gespeicherte Prozeduren, Trigger usw.) ohne Daten benötigen. Die Anweisungen zum Erstellen von Fremdschlüsseleinschränkungen sollten in diesem Skript enthalten sein Ganz am Ende vom Skript trennen, um die Tabellen selbst zu erstellen.
Ich werde den Datenübertragungsalgorithmus selbst kurz beschreiben. Alle Aktionen werden in einer Management Studio-Sitzung ausgeführt, die mit dem Server verbunden ist,
auf den Sie die Datenbank übertragen möchten.
1) Erstellen Sie auf dem neuen Server eine leere Datenbank mit denselben Dateien und Dateigruppen wie die tragbare Datenbank.
2) Mit dem Datenbankschemaskript erstellen wir alle Datenbankobjekte (Tabellen, Indizes, Ansichten, Trigger, gespeicherte Prozeduren und Funktionen), ohne jedoch Fremdschlüsseleinschränkungen zu erstellen. Sie können derzeit keine FK erstellen, da Sie stören das Einfügen von Daten.
3) Wir verbinden die Datenbank, von der wir die Daten übertragen, als Verbindungsserver, damit Sie die alte Datenbank bei Abfragen an die neue Datenbank verwenden können.
EXEC sp_addlinkedserver @server=N'LinkedServerAlias', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'LinkedServerHost\LinkedServerName'; EXEC sp_addlinkedsrvlogin 'LinkedServerUser', 'false', null, 'RealUser', 'RealUserPassword';
4) Weil Da die Datenbankstrukturen identisch sind, verwenden wir die integrierte gespeicherte Prozedur sp_msforeachtable, mit der Sie eine Abfrage für jede Datenbanktabelle durchführen können, um ein Skript zum Generieren von Daten von der alten Datenbank zur neuen Datenbank durch eine Abfrage des Formulars zu generieren
INSERT INTO ? SELECT * FROM ?
Anstelle eines Fragezeichens ersetzt sp_msforeachtable den Namen jeder Tabelle und führt die Abfrage mehrmals aus (einmal für jede Tabelle).
Hier bin ich auf die meisten Rechen gestoßen.
a) Problem Nummer eins ist, dass Sie für Tabellen mit IDENTITY-Feldern Folgendes aufrufen müssen:
SET IDENTITY_INSERT ON;
b) Problem Nummer zwei ist, dass Sie in Tabellen ohne IDENTITY-Felder diesen Aufruf nicht ausführen können. Sie müssen daher dynamisch bestimmen, ob die Tabelle eine IDENITY-Spalte enthält oder nicht.
Dies kann mit dieser Abfrage erfolgen:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME='SomeTable') AND (COLUMNPROPERTY(object_id('dbo.SomeTable'), COLUMN_NAME, 'IsIdentity') = 1)
c) Problem Nummer drei ist, dass Sie, wie sich herausstellte, IDENITY_INSERT ON nicht ausführen können
INSERT INTO ... SELECT * FROM ...
, aber Sie müssen bestimmte Felder auflisten.
Mit dieser Abfrage können Sie die Tabellenfelder in einer Zeile auflisten:
SELECT SUBSTRING( (SELECT ', ' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SomeTable' ORDER BY ORDINAL_POSITION FOR XML path('')), 3, 200000);
4) Wir generieren ein Einfügeskript für alle Tabellen:
Verfahren zur Skripterstellung EXEC sp_msforeachtable N' DECLARE @command varchar(MAX); DECLARE @name varchar(200); SET @name=''?''; SET @name = SUBSTRING(@name, 8, LEN(@name)-8); SET @command = ''''; SELECT @command= SUBSTRING( (SELECT '', '' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''' + @name + '''' ORDER BY ORDINAL_POSITION FOR XML path('''')), 3, 200000); SET @command = ''INSERT INTO ''+ @name +'' (''+ @command + '') SELECT '' + @command + '' FROM '' + ''LinkedServerAlias.SourceDatabase.'' + ''?''; SET @command= ''IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME='''''' + @Name + '''''') AND (COLUMNPROPERTY(object_id(''''dbo.''+@Name+''''''), COLUMN_NAME, ''''IsIdentity'''') = 1)) SET IDENTITY_INSERT '' + @name + '' ON; '' +@command; SET @command=@command+'';'' + ''IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME='''''' + @Name + '''''') AND (COLUMNPROPERTY(object_id(''''dbo.''+@Name+''''''), COLUMN_NAME, ''''IsIdentity'''') = 1)) SET IDENTITY_INSERT '' + @name + '' OFF;''; PRINT (@command);
5) Wir führen das generierte Datenübertragungsskript aus
6) Wir führen ein Skript aus, um alle Fremdschlüsseleinschränkungen zu erstellen (jetzt ist dies bereits möglich).
7) Fertig! Sie haben die Datenbank vom neuen SQL-Server auf den alten übertragen, obwohl dies als unmöglich angesehen wurde. Darüber hinaus wird die Übertragung nur eineinhalb Mal langsamer als die Datenübertragungsrate über das Netzwerk durchgeführt, d.h. ziemlich schnell.
8) Wir räumen nach uns selbst auf (wir trennen den Verbindungsserver):
EXEC sp_droplinkedsrvlogin 'LinkedServerUser', null; sp_dropserver 'LinkedServerAlias';
Methodenbeschränkungen.
1) Mit einer ähnlichen Methode ist es nicht möglich, Tabellen zu übertragen, in denen sich Spalten vom Typ XML befinden.
Sicher gibt es viele andere Einschränkungen, wie Die Datenbank, die ich auf diese Weise übertragen habe, verwendete nicht viele der SQL Server-Funktionen. Sie können in den Kommentaren über Einschränkungen schreiben, und ich werde ihnen einen Artikel hinzufügen.
Vielen Dank für Ihre Aufmerksamkeit! Hoffe es hilft jemandem.