Anda memiliki database MS SQL Server yang harus Anda transfer ke komputer fisik lain. Anda telah membuat cadangan dan dengan senang hati memulai pemulihan. Tapi kemudian ternyata di komputer tempat Anda ingin mentransfer database, versi MS SQL Server yang lebih lama diinstal. Stack Overflow meyakinkan Anda bahwa semuanya buruk. Tapi benarkah begitu?
Tentu saja, mentransfer database dari versi yang lebih baru ke yang lama bukanlah skenario klasik dan bukan skenario yang paling benar. Tetapi seringkali database dibuat sedemikian rupa sehingga mereka mendukung versi SQL yang lebih banyak dan lebih baru, dimulai dengan beberapa, misalnya, 2008 R2, karena kompatibilitas langsung dengan MS SQL lebih dari sangat baik. Dan, misalnya, klien Anda telah menginstal MS SQL 2016 untuk dirinya sendiri, dan Anda telah menginstal MS SQL 2014 di server pengujian untuk pengembangan. Dan Anda ingin memperluas basis klien Anda untuk mencari tahu di mana ia memiliki kebingungan data.
Microsoft tidak mengakui masalah - mereka mengatakan mereka tidak memiliki kompatibilitas ke belakang, dan tidak ada masalah. Cadangan yang dibuat di server yang lebih baru tidak dapat dipulihkan ke server yang lebih lama. Ya, mereka memiliki alat seperti DTS, menyalin basis data, ekspor-impor, dll. Tapi mereka sangat merepotkan dan rumit sehingga transfer normal dari database besar dengan banyak tabel untuk membuatnya tidak nyaman. Bagaimanapun, saya pribadi tidak berhasil.
Ya, Anda bisa membuat skrip SQL untuk seluruh database, termasuk data. Tapi bayangkan, Anda memiliki banyak bidang gumpalan dengan data besar di database, dan secara umum ukuran seluruh database adalah 500+ GB. Bayangkan berapa banyak skrip akan mengambil, berapa banyak waktu akan dihasilkan dan dieksekusi.
Jadi, tugasnya adalah untuk secara akurat membuat kembali database (struktur dan data) dari versi baru MS SQL server di versi yang lebih lama. Saya datang dengan solusi yang cukup sederhana yang ingin saya bagikan. Tentu saja, solusi ini memiliki sejumlah keterbatasan, tetapi menurut saya masih lebih baik daripada DTS dan skrip.
Batasan nomor satu adalah bahwa Anda memerlukan akses melalui MS SQL Management Studio ke kedua server - lama dan baru. Jika ini tidak mungkin, maka itu harus dimungkinkan pada mesin dari mana Anda ingin mentransfer database, instal versi SQL di mana Anda perlu mentransfer database untuk mentransfer database terlebih dahulu ke versi ini secara lokal, dan kemudian seret melalui cadangan atau langsung melalui * df file database (via Detach / Attach) ke mesin baru (versi SQL Server dalam hal ini sudah akan cocok).
Batasan lain adalah bahwa Anda akan memerlukan skrip skema database (semua objek, termasuk tabel, indeks, konstanta, prosedur tersimpan, pemicu, dll.) Tanpa data, dan instruksi untuk membuat Batasan Kunci Asing harus masuk ke skrip ini di bagian paling akhir, terpisah dari skrip untuk membuat tabel sendiri.
Saya akan menjelaskan secara singkat algoritma transfer data itu sendiri. Semua tindakan dilakukan dalam sesi Studio Manajemen yang terhubung ke server
tempat Anda ingin mentransfer basis data.
1) Di server baru, buat database kosong dengan file dan grup file yang sama dengan database portabel.
2) Menggunakan skrip skema database, kami membuat semua objek basis data (tabel, indeks, tampilan, pemicu, prosedur dan fungsi tersimpan), tetapi tanpa membuat Batasan Kunci Asing. Anda tidak dapat membuat FK pada tahap ini, karena mereka akan mengganggu penyisipan data.
3) Kami menghubungkan database dari mana kami akan mentransfer data sebagai Server Linked, sehingga Anda dapat menggunakan database lama dalam permintaan ke database baru.
EXEC sp_addlinkedserver @server=N'LinkedServerAlias', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'LinkedServerHost\LinkedServerName'; EXEC sp_addlinkedsrvlogin 'LinkedServerUser', 'false', null, 'RealUser', 'RealUserPassword';
4) Karena struktur basis datanya sama, kita akan menggunakan prosedur tersimpan sp_msforeachtable, yang memungkinkan Anda untuk melakukan kueri pada setiap tabel basis data untuk menghasilkan skrip untuk mentransfer data dari database lama ke yang baru melalui kueri formulir
INSERT INTO ? SELECT * FROM ?
Alih-alih tanda tanya, sp_msforeachtable mengganti nama setiap tabel dan mengeksekusi kueri beberapa kali (satu kali untuk setiap tabel).
Di sini saya menemukan jumlah garu terbesar.
a) Masalah nomor satu adalah bahwa untuk tabel dengan bidang IDENTITAS, Anda harus memanggil:
SET IDENTITY_INSERT ON;
b) Masalah nomor dua adalah Anda tidak dapat melakukan panggilan ini pada tabel yang tidak memiliki bidang IDENTITAS, jadi Anda perlu menentukan secara dinamis apakah tabel tersebut memiliki kolom IDENITY atau tidak.
Ini dapat dilakukan dengan permintaan ini:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME='SomeTable') AND (COLUMNPROPERTY(object_id('dbo.SomeTable'), COLUMN_NAME, 'IsIdentity') = 1)
c) Masalah nomor tiga adalah, ternyata, dalam mode IDENITY_INSERT ON, Anda tidak bisa melakukannya
INSERT INTO ... SELECT * FROM ...
, tetapi Anda perlu mendaftar bidang tertentu.
Anda bisa daftar bidang tabel berturut-turut dengan kueri ini:
SELECT SUBSTRING( (SELECT ', ' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SomeTable' ORDER BY ORDINAL_POSITION FOR XML path('')), 3, 200000);
4) Kami membuat skrip insert untuk semua tabel:
Prosedur Pembuatan Script 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) Kami menjalankan skrip transfer data yang dihasilkan
6) Kami menjalankan skrip untuk membuat semua Batasan Kunci Asing (sekarang sudah mungkin).
7) Dilakukan! Anda mentransfer database dari server SQL baru ke yang lama, meskipun ini dianggap mustahil. Selain itu, transfer dilakukan hanya satu setengah kali lebih lambat dari kecepatan transfer data melalui jaringan, mis. cukup cepat.
8) Kami membersihkan sendiri (kami putuskan Server Linked):
EXEC sp_droplinkedsrvlogin 'LinkedServerUser', null; sp_dropserver 'LinkedServerAlias';
Keterbatasan metode.
1) Menggunakan metode yang serupa, tidak akan mungkin untuk mentransfer tabel di mana ada kolom tipe XML.
Tentunya ada banyak batasan lainnya, seperti database yang saya transfer dengan cara ini tidak menggunakan banyak fitur SQL server. Anda dapat menulis tentang batasan dalam komentar, dan saya akan menambahkan artikel kepada mereka.
Terima kasih atas perhatian anda! Semoga ini bisa membantu seseorang.