ProHoster > Блог > адміністрування > Перенесення даних бекапу нової версії MS SQL Server на старішу версію

Перенесення даних бекапу нової версії MS SQL Server на старішу версію

Передісторія

Якось для відтворення бага мені знадобився бекап production-бази.

На подив я зіткнувся з такими обмеженнями:

  1. Бекап бази було зроблено на версії SQL Server 2016 і не був сумісний з моєю SQL Server 2014.
  2. На моєму робочому комп'ютері як ОС використовувалася Windows 7тому я не міг оновити SQL Server до версії 2016
  3. Підтримуваний продукт був частиною більшої системи з сильно пов'язаною легасі-архітектурою і також звертався до інших продуктів і баз, тому розгортання на іншій станції могло зайняти дуже багато часу.

Враховуючи вищевикладене, я дійшов висновку, що настав час милиць нестандартних рішень.

Відновлення даних з бекапу

Я вирішив використати віртуальну машину Oracle VM VirtualBox з Windows 10 (можна взяти тестовий образ для браузера Edge звідси). На віртуальну машину було встановлено SQL Server 2016 і на ньому з бекапу було відновлено базу даних програми (інструкція).

Налаштування доступу до SQL Server на віртуальній машині

Далі було необхідно зробити деякі кроки, щоб з'явилася можливість доступу до SQL Server ззовні:

  1. Для фаєрволу додати правило пропускати запити на порт 1433.
  2. Бажано, щоб доступ до сервера йшов не через windows-автентифікація, а через SQL за логіном та паролем (простіше налаштувати доступ). Однак у цьому випадку слід не забути включити у властивостях SQL Server можливість SQL-автентифікації.
  3. У налаштуваннях користувача на SQL Server на вкладці Відображення користувачів вказати для відновленої бази роль користувача db_securityadmin.

перенесення даних

Власне саме перенесення даних і двох етапів:

  1. Перенесення схеми даних (таблиці, уявлення, процедури, що зберігаються і т.д.)
  2. Перенесення самих даних

Перенесення схеми даних

Виконуємо такі операції:

  1. вибираємо Завдання -> Створення скриптів для бази, що переноситься.
  2. Вибираємо потрібні для перенесення об'єкта або залишаємо значення за замовчуванням (у цьому випадку створять скрипти для всіх об'єктів бази).
  3. Вказуємо налаштування для збереження скрипту. Найзручніше зберегти скрипт у єдиний файл у кодуванні Unicode. Тоді при збої не доведеться знову повторювати всі кроки.

Після збереження скрипта його можна виконати на вихідному SQL Server (старій версії), щоб створити потрібну базу.

Увага: після виконання скрипта необхідно перевірити відповідність налаштувань бази з бекапу та бази, створеної скриптом. У моєму випадку в скрипті не було налаштування для COLLATE, що призводило до збою при перенесенні даних і танцям з бубном перестворення бази за допомогою доповненого скрипта.

перенесення даних

Перед перенесенням даних необхідно вимкнути перевірку всіх обмежень на базі:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Перенесення даних здійснюємо за допомогою майстра імпорту даних Tasks -> Import Data на SQL Server, де знаходиться створена скриптом база:

  1. Вказуємо налаштування підключення до джерела (SQL Server 2016 на віртуальній машині). Я використовував Data Source Рідний клієнт SQL Server та вищезгадану SQL-автентифікацію.
  2. Вказуємо налаштування підключення до місця призначення (SQL Server 2014 на хост-машині).
  3. Далі налаштовуємо мапінг. Необхідно вибрати все не read-only об'єкти (наприклад, уявлення обирати не потрібно). Як додаткові опції слід вибрати «Дозволити вставку в identity-стовпці»якщо такі використовуються.
    Увага: якщо при спробі виділити кілька таблиць та проставити їм властивість «Дозволити вставку в identity-стовпці» Якість вже було раніше встановлено хоча б однієї із виділених таблиць, у діалозі буде зазначено, що якість вже встановлено всім виділених таблиць. Цей факт може спантеличити і призвести до помилок перенесення.
  4. Запускаємо перенесення.
  5. Відновлюємо перевірку обмежень: 
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Якщо виникли будь-які помилки, перевіряємо налаштування, видаляємо створену з помилками базу, заново створюємо її зі скрипту, вносимо виправлення та повторюємо перенесення даних.

Висновок

Це завдання зустрічається досить рідко і виникає лише через зазначених обмежень. Найчастіше рішення полягає в оновленні SQL Server або підключення до віддаленого сервера, якщо це дозволяє архітектура програми. Однак від легаси-коду та кривих рук неякісної розробки ніхто не застрахований. Сподіваюся, що Вам ця інструкція не знадобиться, а якщо все ж таки в ній виникне необхідність, то допоможе заощадити купу часу і нервів. Дякую за увагу!

Список використаних джерел

Джерело: habr.com