В ході проведення презентацій про бекап і відновлення баз даних SQL Server, зазвичай задаються два типи питань. Перші задаються прямо по ходу презентації із залу, другі задаються вже після, в приватній бесіді. Ці, «приватні» питання, часто більш цікаві і я спробую дати відповіді на найбільш складні і цікаві з них, замість того щоб писати ще одну статтю про те як ви повинні робити бекапи, або чому ви повинні робити бекапи, або навіть чому ви повинні перевіряти свої бекапи (але ви і справді повинні перевіряти свої бекапи).
Чи можу я розгорнути бекап на версії SQL Server, відмінною від тієї, на якій був зроблений бекап? Які проблеми можуть виникнути?
Для того, щоб визначити на якій версії SQL Server був створений бекап, потрібно подивитися заголовок файлу бекапа:
Чи можу я використовувати операцію відновлення для створення копії бази даних? Що може піти не так?
Так, ви можете це зробити. Якщо ви розвертаєте бекап на іншому сервері, то потрібно переконатися в тому, що на новому сервері у вас присутні ті ж самі логічні диски, що і на «старому» сервері, або вручну прописати правильні шляхи для файлів бази даних, використовуючи опцію WITH MOVE команди RESTORE DATABASE:
У файлів баз даних є як логічні імена, так і фізичні імена файлів. Вам потрібно всього лише прописати всі логічні імена файлів і визначити для кожного з них нове фізичне розміщення.
Основними проблемами, з якими ви можете зіткнутися, є помилки пов'язані з нестачею вільного місця на дисках, на які ви відновлюєте базу даних, або ви можете забути вказати нове ім'я для бази даних і SQL Server буде намагатися відновити базу даних поверх існуючої БД.
Коли ви відновлюєте БД на новому сервері, ви можете зіткнутися з проблемою «Orphaned Users» (користувачів, які втратили зв'язок з обліковим записом, згідно переказу на msdn - прим. Перекладача), якщо користувач бази даних пов'язаний з обліковим записом, не поданою на новому сервері . Вам потрібно буде виправити цю помилку.
Чи можна приєднувати як базу даних файл MDF, якщо у мене немає файлу журналу транзакцій?
Єдиний варіант, коли це допустимо - якщо журнал транзакцій був загублений вже після того як робота бази даних була коректно завершена. У будь-якому випадку - це не дуже гарна ідея. При приєднанні БД, файл журналу транзакцій, так само як і файл даних, потрібен для проведення процесу відновлення БД (тут під відновленням БД розуміється не операція RESTORE DATABASE, а recovery - процес, що відбувається при кожному запуску SQL Server, при якому SQL Server «проходить »за журналом транзакцій і призводить файли даних в узгоджене стан - прим. перекладача). Проте, в деяких випадках можливе приєднання файлу даних без файлу журналу транзакцій, але ця можливість призначена тільки для тих випадків, коли файл журналу транзакцій був пошкоджений або втрачений в результаті проблем з обладнанням і при відсутності резервних копій. Звичайно, база даних не може існувати без журналу транзакцій, і при приєднанні БД без файлу журналу транзакцій, SQL Server просто пересоздаст його.
Приєднання файлу даних без файлу журналу транзакцій руйнує ланцюжок журналів і, в добавок, може виявитися, що в БД порушена транзакційна або структурна цілісність (в залежності від стану БД на момент «втрати» журналу транзакцій). Операція приєднання такої БД може завершуватися помилкою незалежно від того, які б дії не робилися.
Моя БД лежить на SAN. Я чув, що бекапов SAN досить. Це правда?
Це може бути правдою. Головне щоб ваша SAN (СГД, Мережа / Система Зберігання Даних - прим. Перекладача) підтримувала транзакції SQL Server. Якщо це так, тоді вона буде знати про те, що в БД існують транзакції і наявність цих транзакцій може означати, що дані в файлах даних, можуть бути не повними, оскільки процес запису даних, змінених в цих транзакції, на жорсткий диск, може бути не завершений на момент створення резервної копії. Ті бекапи, які робить сам SQL Server, природно, враховують ці моменти.
EMC Data Domain, наприклад - це комбінація ПО і SAN, що забезпечує підтримку транзакцій, як і продукція інших вендорів, але вам все одно потрібно перевірити документацію вашого SAN. Зверніть увагу на наявність фраз на кшталт «transaction consistency», або «transaction aware», або чогось подібного. Якщо ви їх не знайшли, то я б порадив вам перевірити відновлення БД перш ніж ви вирішите, що бекапов SAN вам досить для виконання всіх ваших вимог до резервних копій. Втім, навіть після того, як ви переконалися, що бекапи SAN виконуються коректно, це зовсім не означає, що «рідні» бекапи SQL Server вам більше не потрібні. Якщо вам потрібна можливість відновлення вашої БД на момент часу, наприклад, вам все одно доведеться робити бекапи журналу транзакцій засобами SQL Server.
Зазвичай, при створенні резервної копії, SAN з підтримкою SQL Server, використовує VDI-інтерфейс SQL Server і «заморожує» БД на час створення резервної копії. Якщо ви запустите механізм створення такого бекапа і подивіться в журнал помилок SQL Server, там ви побачите повідомлення про те, що операції IO були заморожені.
Якщо ви покладаєтеся на резервні копії створюються SAN, вам все одно потрібно проводити перевірки цілісності БД або на «живих» БД, або на копіях, відновлених з бекапа SAN. В іншому випадку, ви можете довгий час створювати бекапи пошкодженої БД і навіть не знати про це.
Чому я не можу використовувати в якості резервних копій копії файлів даних, створених Windows? Мені не потрібна можливість відновлення на довільний момент часу.
SQL Server не є звичайним десктопних додатком. Він керує своїми файлами таким чином, щоб забезпечити виконання всіх властивостей ACID (Atomic, Consistency, Isolated, Durable - трохи більш докладно - прим. Перекладача). Якщо коротко, щоб забезпечити успішне завершення транзакцій, SQL Server намагається нікому не давати доступ до своїх файлів і сам модифікує їх так, як йому потрібно.
Якщо ви просто скопіювали файл даних, ігноруючи блокування і транзакції, які можуть виконуватися в даний момент, це означає, що коли ви спробуєте приєднати цей файл пізніше, він буде в неузгоджену стані, що призведе до помилок.
Набагато безпечніше і простіше використовувати вбудований механізм SQL Server
для створення резервних копій. Такий бекап буде повною копією вашої БД, і все властивості ACID будуть виконані.
У мене дуже маленька БД. Чому я не можу просто «вивантажити» кожну таблицю на диск для створення резервної копії?
Ви можете використовувати що-небудь на зразок SQLCMD і вивантажити таблиці в простий текстовий файл, але потім, замість того, щоб просто однією командою відновити БД, вам доведеться виконати цілий ряд команд. По-перше, вам потрібно буде створити порожню БД. Потім, вам потрібно буде створити і завантажити з файлу кожну таблицю. Якщо яка-небудь таблиця містить стовпець IDENTITY, вам потрібно буде виконувати SET IDENTITY_INSERT на кожній з цих таблиць. Так само, вам доведеться ретельно визначати порядок, в якому ви будете завантажувати дані в таблиці, щоб забезпечувати цілісність.
Плюс, враховуйте, що всі ваші таблиці вивантажені на диск в різний час, так що якщо дані якось змінювалися під час вивантаження, після відновлення ви не отримаєте БД в цілісному стані і вам доведеться вручну шукати помилки і виправляти їх.
Звичайно, ви маєте право поступати таким чином. З іншого боку, ви можете просто виконати команду BACKUP DATABASE, а потім, коли знадобиться, відновити цей бекап.
Навіщо платити гроші за утиліти, що роблять бекапи, якщо SQL Server сам вміє це робити?
Існує три основні причини для використання сторонніх програм, що створюють бекапи: керівництво, автоматизація і функціональність. Якщо ви початківець адміністратор баз даних або взагалі не адміністратор баз даних, але змушені обслуговувати СУБД як додаток до своєї основної роботи, ви можете і не знати про те як, де і чому потрібно налаштовувати бекапи в SQL Server. Гарна утиліта (на кшталт SQL Backup Pro) може надати вам якраз такий тип керівництва, який вам потрібен для того, щоб забезпечити збереження ваших БД за допомогою резервних копій.
Бекапи, створювані самим SQL Server, працюють відмінно, але вам потрібно виконати чимало роботи для того, щоб їх налаштувати і ще більше для того, щоб їх автоматизувати. Хороша стороння утиліта зробить процес автоматизації дуже простим. Більш того, з її допомогою ви зможете автоматизувати інші процеси пов'язані з резервних копій, такі як віддзеркалення / доставка журналів та перевірка цілісності бекапа.
Нарешті, хоча бекапи SQL Server і роблять те що вам треба, вони, можливо, роблять це не найкращим чином. Наприклад, деякі утиліти більш ефективно стискають резервні копії, зберігаючи таким чином більше дискового простору і скорочуючи час створення резервної копії. Так само, вони додають функціональність - таку як шифрування файлу резервної копії (щось подібне можливо вбудованими засобами SQL Server тільки в тому випадку, якщо сама БД зашифрована).
Якщо бекап лежить на мережевий кулі, чи може хтось прочитати його?
Більш того, з резервної копії можна дістати схему БД або дані, навіть не відновлюючи його. Якщо у вас є утиліта SQL Data Compare, то вона, запущена з ключем / Export зможе витягти всі дані з резервної копії в CSV-форматі, порівнюючи цей бекап з порожньою БД і не питаючи ніякого пароля. Так само, та ж сама SQL Data Compare зможе створити для вас скрипт створює схему БД.
Для того щоб запобігти несанкціонованому доступу до резервної копії, вам доведеться зробити кілька речей. По-перше, переконатися, що кулі, на якій зберігаються бекапи, доступна обмеженому колу осіб. По-друге, ви повинні зберігати тільки ті бекапи, які вам дійсно потрібні. Нарешті, якщо ви використовуєте сторонні утиліти для створення резервних копій (типу SQL Backup Pro), ви можете зашифрувати бекап, так що якщо хтось і отримає доступ безпосередньо до файлу, то прочитати звідти нічого не зможе.
Без сторонніх утиліт, ви зможете цього домогтися, використовуючи Transparent Data Encryption (TDE).
Для забезпечення найкращого рівня безпеки, вам потрібно виконувати всі перераховані вище дії.
А хто-небудь може змінити вміст резервної копії?
Чи існує який-небудь прапор, встановивши який при створенні резервної копії, я можу бути впевнений, що завжди зможу з нього відновитися?
Якщо під таким прапором ви маєте на увазі, що ваш процес створення резервної копії включає в себе виконання операції RESTORE VERIFYONLY після створення резервної копії, то немає, ви не можете бути впевнені в тому, що зможете відновити БД з цього бекапа. RESTORE VERIFYONLY може виконувати набір з двох перевірок.
По-перше, вона перевіряє заголовок бекапа, щоб переконатися, що в ньому немає помилок. Якщо заголовок пошкоджений, то ви не зможете відновити БД з цього бекапа.
Проблеми можуть виникнути в двох місцях. По-перше, перевірка заголовка в ході виконання VERIFYONLY не перевіряє все що може вплинути на процес відновлення. Це означає, що RESTORE VERIFYONLY може завершитися без помилок, але БД все одно не зможе бути відновлена з «перевіреної» копії.
По-друге, CHECKSUM не може виявити пошкодження, що відбулися в пам'яті. Якщо сторінка даних була оновлена, перебуваючи в пам'яті і потім сталося її пошкодження перш ніж вона була записана на диск (і, відповідно, в бекап), тоді обчислення контрольної суми не покаже ніякої помилки, а просто підтвердить, що в бекап була записана та ж сторінка, що і було передбачено у БД в момент створення бекапа. Тобто якщо сторінка вже була пошкоджена в момент створення резервної копії, помилка не може бути знайдена за допомогою контрольної суми і відновлення з цього бекапа може завершитися помилкою.
Єдиний спосіб дізнатися напевно, що з резервної копії можна відновитися і отримана БД не пошкоджена - це відновити його і, бажано, запустити перевірку цілісності БД на відновленої копії.
Чи не містить бекап що-небудь крім даних? Чи може хто-небудь прочитати паролі з нього?
Бекап містить не тільки дані. Він містить всю структуру бази даних. Вона включається в себе всі дані, процедури, подання, функції і весь інший код. Також, він містить всі налаштування БД. Нарешті, він містить всю інформацію про користувачів БД. Для звичайної БД, кожен користувач БД пов'язаний з обліковим записом SQL Server. Паролі таких користувачів зберігаються разом з обліковим записом, так що цих паролів в бекапе не буде.
Однак, в автономних базах даних (contained databases - прим. Перекладача) існує поняття USER WITH PASSWORD, оскільки сама ідея автономних баз даних передбачає мінімальну зв'язок такої бази з сервером. В цьому випадку, пароль буде перебувати в бекапе, що може привести до спроб дістати його звідти. Паролі зберігаються не відкритим текстом, вони хешіруются, точно так само як паролі облікових записів (які зберігаються в системній базі даних master і, природно, потрапляють в її бекап).
Microsoft пропонує кілька best practices з безпеки автономних баз даних.
Навіщо в бекапе індекси, статистика і інші штуки, які легко перебудувати? Це ж просто втрата часу?
А по-моєму, втрата часу - це спроби розділити речі таким чином і робити резервну копію тільки однієї частини. По-перше, як це зробити? Наприклад, як забекапіть дані, не роблячи, при цьому, бекапа кластерних індексів? Це неможливо, оскільки листової рівень кластерного індексу - це сторінки даних. Тобто можна сказати, що кластерні індекси - це самі таблиці, тому кластерні індекси повинні бути включені в бекап. Звичайно, можливо виділити некластерние індекси в окрему файлову групу і не робити її бекап, але потім, після відновлення того бекапа, що у нас є, нам все одно потрібно буде повертати цю файлову групу до життя і перебудовувати все індекси. Так чого ми доб'ємося?
Зі статистикою так само виникнуть проблеми. SQL Server бекапіт статистику разом з базою даних (і вона займає дуже мало місця, оскільки, гістограма, що називається статистикою, будується всього лише по 200 рядків) і відновлює її разом з БД. Однак, якщо після відновлення ми почнемо пересоздавать індекси, оскільки не робили їх резервної копії, нам доведеться пересоздавать і статистику. Це так само потребують додаткового часу, а база даних, тим часом, буде залишатися недоступною.
Зрештою, я б посперечався з формулюванням «легко перебудувати», оскільки в екстреному випадку, весь цей процес може виявитися дуже заплутаним, що неминуче призведе до того, що люди, які працюють з цією базою даних, не зможуть отримати до неї доступ набагато більше час, ніж в разі простого відновлення з резервної копії.
Сама ідея створення резервної копії полягає в тому, щоб можна було відновити базу даних якомога швидше і ефективніше. Чим складніший процес відновлення, тим менш ефективна резервна копія. Так, для зберігання індексів, користувачів, збережених процедур і всього іншого, потрібно додатковий простір, але збільшення швидкості відновлення за рахунок того, що все лежить в одному місці, варто цього додаткового простору.
ОМГ! Я тільки що видалив таблицю! Я знаю, що це є в журналі транзакцій. Як мені її повернути?
Інший варіант - використання сторонніх утиліт, типу SQL Backup Pro, які можуть виконувати відновлення окремих об'єктів БД в режимі online з наявних резервних копій.
А якщо я просто хочу створити за допомогою бекапа скрипт для побудови БД, без відновлення безпосередньо бекапа ...?
Стандартних засобів для створення такого скрипта в SQL Server не передбачено. Однак, утиліти, типу SQL Compare, можуть сформувати його. Він легко створюється за допомогою GUI, але так само це можливо з використанням PowerShell:
'C: \ Program Files (x86) \ Red Gate \ SQL Compare 8 \ SQLCompare.exe' /Backup1:C:\MyBackups\MyBackupFile.bak / MakeScripts: "C: \ MyScripts \ MyBackupScript"
Так само, ви можете звернути увагу на SQL Virtual Restore. Ця утиліта дозволяє вам примонтировать бекап до вашого SQL Server так, як ніби ви запускали процес відновлення з цього бекапа, але не вимагає використання всього того місця, яке було б необхідним при відновленням. Примонтировать таким чином бекап виглядає як звичайнісінька база даних і ви можете заскріптованних її будь-яким зручним для вас шляхом.