Базові операції з обслуговування баз даних в SQL Server 2008 R2 можна робити як з консолі за допомогою службового програми sqlcmd і мови запитів T-SQL, так і за допомогою графічної утиліти SQL Server Managment Studio. Відмінною особливістю кошти SQL Server Managment Studio є, то що в ньому можна використовувати обидва способи, причому під час виконання основних операцій, програма дозволяє переключаться в режим перегляду коду T-SQL. Досить натиснути кнопку Script у верхній частині вікна програми. Для виклику кошти sqlcmd, досить набрати в командному рядку sqlcmd. Для підключення до віддаленого сервера або іменованого примірнику необхідно додати ключ -s:
Повний перелік команд доступних в інтерактивному режимі, можна отримати якщо набрати:
До основних операцій з обслуговування баз даних можна віднести резервне копіювання (Back up Database), відновлення (Restore Database), стиснення (Shirink Database), дефрагментацію. Розглянемо кожну з них на прикладі.
1. Створення резервних копій (Back up Database)
SQL-сервером підтримується широкий перелік методів резервного копіювання. Найбільш поширеними є:
Повне резервне копіювання (Full) - виконує повне резервне копіювання всіх даних в базі і всіх змін, які виникають під час виконання резервного копіювання, а так само всіх транзакцій зафіксованих в журналі транзакцій. Виконується в разі коли база даних має невеликий розмір і резервне копіювання здійснюється протягом прийнятного часу. База даних зазнає незначних змін або доступна тільки для читання.
Різницеві резервні копії (Differential) - слід виконувати для мінімізації часу, який необхідний для відновлення часто змінюваної бази даних. Можливо тільки в тому випадку, коли створена повна резервна копія бази даних.
Резервні копії журналу транзакцій (Transaction Log) - в них записуються всі зміни бази даних. Виконується тільки при наявності повної резервної копії бази, і повної моделі відновлення. Має сенс виконувати при наявності часто змінюваних баз даних.
Щоб створити резервну копію, в SQL Server Management Studio необхідно перейти на закладку Database, натиснути правою кнопкою на потрібній базі і в контекстному меню Task вибрати Back Up. У вікні вибрати метод резервного копіювання (Backup type), в поле «Back up To» задати шлях для зберігання резервної копії або залишити за замовчуванням. Натиснути ок.
Та ж операція на мові запитів T-SQL буде виглядати наступним чином:
2. Відновлення. (Restore Database)
Загальний план відновлення зазвичай виглядає наступним чином. На початку, проводиться відновлення повної резервної копії, потім відновлення разностной резервної копії і тільки потім резервних копій журналів транзакцій.
Перед відновленням необхідно переконається, що доступ підлягає до відновлення базі, закритий для користувачів. Закрити його можна двома способами:
- За допомогою параметра RESTRICTED_USER. (Обмежити доступ користувачам) в додаткових властивостях (Options) бази даних. Якщо користувачі можуть підключаться до бази з правами dbo, то необхідно виставити параметр в SINGLE_USER. В T-SQL за аналогічну дію відповідає параметр WITH RESTRICTED_USER.
- Якщо на сервері є тільки одна робоча база даних, то краще просто на час відновлення відключити мережевий доступ до SQL Server. Для цього можна, на час відновлення відключити протокол TCP / IP в контейнері SQL Server 2008 Network Configuration в SQL Server Configuration Manager
Може трапитися так, що база даних пошкоджена настільки сильно, що змінити її властивості не вдається. Вона при цьому може знаходитися в стані suspect (підозріле) або в автономному режимі offline (інформацію про стан можна переглянути, наприклад, з контейнера Datаbases в Management Studio). Якщо база даних знаходиться в автономному режимі, то запустити її відновлення не вдасться. У цій ситуації найпростіший вихід - від'єднати (detach) пошкоджену базу даних і провести відновлення з резервної копії так, як ніби ця база даних відсутня на сервері взагалі. Відзначимо, що для того, щоб від'єднати базу даних, позначену як suspect (підозріла), її необхідно спочатку перевести в стан emergency (екстреної необхідності):
Існує три моделі відновлення баз даних. Повна (Full), проста (Simple), c неповним протоколированием Bulk-Logged).
Повною моделі відновлення, використовуються копії баз даних (* .bak) і всі відомості журналів. (* .Ldf) Сервером SQL Server заносяться в журнал всі зміни бази даних, включаючи масові операції та операції створення індексів. Якщо самі журнали не пошкоджені, сервером можуть бути відновлені всі дані за винятком транзакцій, які оброблялися на момент збою. Оскільки всі транзакції записані в журнал, відновлення може бути виконано до будь-якого моменту часу. Головне обмеження цієї моделі - великий розмір файлів журналів та підсумкові витрати пам'яті і процесорного часу. Повне відновлення має сенс, тільки якщо:
- База даних має невеликий розмір. Резервне копіювання виконується протягом прийнятного часу.
- База даних зазнає незначних змін або доступна тільки для читання.
Проста модель відновлення (simple) використовується для малих баз даних або баз даних, в яких дані змінюються рідко. Копії журналів не передбачені. У цій моделі відновлення використовуються повні або різницеві копії баз даних, і відновлення обмежується відновленням бази даних до моменту, коли була створена остання резервна копія. Всі зміни, зроблені після створення резервної копії, втрачаються. Основна перевага цієї моделі, що для зберігання журналів (* .ldf) потрібно менше місця.
У моделі відновлення з неповним протоколированием (Bulk-Logged) для відновлення бази даних використовуються резервні копії як бази даних так і журналу. Це доповнення до повної моделі відновлення, що дозволяє виконувати високопродуктивні операції масового копіювання. Основна перевага - зменшує місце займане журналами, за рахунок протоколювання більшості масових операцій. Можливо відновлення до кінця будь-резервної копії. Відновлення до заданої точки не підтримується.
Уявімо, що стався збій і база даних newdb ушкодилася і відповідно виникла необхідність в її відновленні. Для цього в SQL Server Managment Studio в контейнері Databases вибираємо базу newdb, потім Tasks - Restore - Database.
Тут параметр 'To database' задає в яку базу відновлювати бекап. В місце вибору бази даних доступний так само варіант введення свого імені. В цьому випадку з резервної копії на сервері буде створена нова база даних. У деяких випадках може бути зручно відновити копію існуючої бази даних під іншим ім'ям, а потім при необхідності стару базу даних видалити, а відновлену перейменувати, присвоївши їй стару назву.
Параметр 'To a point in time' (До моменту часу) задає момент часу на який потрібно відновити базу, за замовчуванням: найостанніший. Зазвичай використовується тільки в ситуації, коли користувач зробив помилку (наприклад, видалив важливі дані) і ви знаєте приблизно, коли це сталося. Використовується тільки при відновленні журналів транзакцій.
From Database - задає з резервної копії якої бази виробляти відновлення, за замовчуванням пропонує те, що знаходиться в. \ Microsoft SQL Server \ MSSQL10_50.MSSQLSERVER \ MSSQL \ Backup. У списку можна вибрати не тільки поточну базу даних, але і інші бази даних, які є на цьому сервері;
From Device - пропонує вибрати резервну копію розташовану на іншому диску або пристрої. Ця можливість використовується в тих ситуаціях, коли потрібно відновити базу даних на інший сервер або місцезнаходження резервної копії змінилося.
Погоджуємося з параметрами обраними за замовчуванням, натискаємо ОК, і отримуємо помилку:
Restore Failed fo Server 'server'. (Microsoft.SqlServer.SmoExtende d) System.Data.SqlClient.SqlError: The tail of the log for the database «newdb» has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose . Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log .
'Завершальний фрагмент журналу бази даних «newdb» не додано до резервну копію. Якщо журнал містить дані, які потрібно зберегти, використовуйте для його резервного копіювання BACKUP LOG WITH NORECOVERY. Використовуйте пропозицію WITH REPLACE або WITH STOPAT інструкції RESTORE для заміни вмісту журналу. '
Схоже спрацьовує, перевірка безпеки, яка забороняє проводити відновлення бази даних, якщо на ній залишилася частина журналу транзакцій (tail-log), резервне копіювання якої ще не проводилося. Дана перевірка спрацьовує, тільки в разі повної моделі відновлення і моделі відновлення з неповним протоколированием. Тому, що б відновити таку базу даних необхідно перейти в розділ опцій і додати параметр Overwrite the existing database (WITH REPLACE), який відключає перевірку безпеки. Або під час резервного копіювання бази, робити так само резервну копію журналу транзакцій і виробляти відновлення в місці з журналом.
Ще, при відновленні існують перевірка не допускає випадкового перезапису бази даних іншою базою даних, тобто якщо дана база, яку потрібно відновити вже існує на сервері, а ім'я бази даних або набір файлів для заданої бази даних відрізняється від імені або набору файлів записаного в резервному наборі даних, то її відновлення так само не буде виконано. Так само заборонено перезаписувати файли, які відносяться до баз даних, що знаходяться в автономному режимі (offline), і, крім цього, взагалі будь-які файли, які не належать до SQL Server;
Наступний параметр Preserve the replication settings (WITH KEEP_REPLICATION) (Зберегти настройки реплікації при відновленні) - використовується тільки тоді, коли база даних одночасно бере участь і в реплікації, і в автоматичній доставці журналів (log shipping).
Prompt before restoring each backup (Виводити запрошення перед кожним відновленням) - виводити запрошення перед відновленням кожної наступної резервної копії з обраного вами списку. Якщо використовується стример.
Restrict access to the restored database (Обмежити доступ до відновлюваної базі даних) - після відновлення доступ буде відкритий тільки членам ролі бази даних db_owner і членам серверних ролей dbcreator і sysadmin. Цей параметр зазвичай застосовується в тих випадках, коли після відновлення бази даних вам необхідно провести додаткові перевірки або внести виправлення.
Restore the database files as (Відновити файли бази даних як) - даний параметр, дозволяє визначити новий шлях для відновлюваних файлів баз даних. Застосовується, в тих ситуаціях, коли проводиться відновлення бази даних на інший сервер, на якому конфігурація дисків виглядає по-іншому.
Recovery state (Стан відновлення) - цей параметр визначає, чи буде база даних відкрита для користувачів після закінчення відновлення з носія. Можливі три варіанти:
1. WITH RECOVERY - відновлення в звичайному режимі. Після закінчення відновлення почнеться процедура RECOVERY, все незавершені транзакції будуть скасовані, і в підсумку база даних буде відкрита для користувачів. Цей параметр використовується за умовчанням;
2. WITH NORECOVERY - після закінчення процесу відновлення з носія процедура RECOVERY не почнеться. Бази даних залишиться в неробочому стані відновлення. Цей параметр використовується тоді, коли після відновлення резервної копії ви хочете відновити додаткові копії, наприклад, після відновлення повної резервної копії відновити резервну копію журналу транзакцій;
3. WITH STANDBY - процедура RECOVERY почнеться, але вся інформація про всіх скасованих незавершених транзакцій буде записана в файл скасування (його потрібно буде вказати). В результаті користувачі зможуть звертатися до відновленої базі даних для читання (наприклад, для створення звітів), але в той же час зберігається можливість застосування наступних резервних копій журналів транзакцій. Таке рішення використовується зазвичай тільки при застосуванні автоматичної доставки журналів на резервний сервер (log shipping).
Мовою запитів відновлення бази даних виглядає наступним чином:
3. Стиснення баз даних (Shirink Database)
Залежно від того, що міститься у файлі баз даних в даний момент, може виникнути потреба зменшити його розмір на диску. Найбільший ефект від операції стиснення досягається після операції, що видаляє з бази даних дуже великий обсяг даних, а також тільки в разі, коли таке вільне місце не планується використовувати повторно.
Більшості баз даних потрібен якийсь вільний простір для виконання звичайних щоденних операцій. Якщо стиснення баз даних проводиться регулярно, але її розмір знову збільшиться, це означає, що місце звільнене при стисненні, необхідно для нормальної роботи. В цьому випадку регулярне стиснення бази даних не дасть потрібного ефекту.
Операція стиск не зберігає стан фрагментації індексів в базі даних і, як правило, призводить до більшої фрагментації. Тому не слід організовувати регулярне стиснення файлів баз; Виробляти стиснення слід тільки при появі великого обсягу невикористаного місця в результаті виконання операції, яка значно впливає на використовуване місце в базі даних. Для усунення фрагментації, після виконання операції стиснення слід виконувати операцію перестроювання індексів, але це викликає повторний зростання бази даних і поява невикористаного місця!
Для виконання операції стиснення файлів даних і журналів транзакцій в конкретній базі необхідне виконання інструкції DBCC SHRINKDATABASE. Для стиснення окремих файлів використовується команда DBCC SHRINKFILE.
Для виконання операції стиснення баз даних (Shirink Database) за допомогою графічного кошти Managment Studio в контейнері Database клацати правою кнопкою і в контекстному меню вибираємо: Tasks - Shrink - Database.
У вікні в полі Currently allocated space (Виділене в даний момент місце) вказується сумарний обсяг даних, які використовує база даних і лог транзакцій.
В поле Available Free Space (Доступне вільне місце) вказується обсяг даних який вивільниться після операції стиснення.
Reorganize files before releaising unused space. (Реорганізовувати файли перед звільненням невикористаного місця). Установка даного прапорця еквівалентна виконанню інструкції DBCC SHRINKDATABASE із завданням цільового процентного параметра. Зняття цього прапорця еквівалентно виконанню інструкції DBCC SHRINKDATABASE з параметром TRUNCATEONLY (звільняє для операційної системи весь вільний простір в кінці файлу, але не виконує переміщення сторінок всередині файлу) За замовчуванням при відкритті діалогового вікна цей прапорець не встановлено. Якщо цей прапорець встановлений, то необхідно задати цільове процентне значення в поле Maximum free space in files after shirinking (максимальне вільний простір в%, яке повинно залишитися в базі даних після її стиснення.)
Аналогічна операція на мові запитів T-SQL, буде виглядати так:
Іноді, може виникнути ситуація, коли файли журналу почали істотно збільшуватися в розмірі, часом перевищуючи за обсягом саму базу даних в кілька разів. Відбувається це, тому що, у властивостях бази даних виставлена повна модель відновлення (Full) або модель з неповним протоколированием (Bulk-Logged), яка в порівнянні з простою моделлю відновлення (Simple) не припускав автоматичне очищення журналу транзакцій. Тому, що уникнути даної ситуації необхідно на рівні з повним резервним копіюванням регулярно створювати резервні копії журналу транзакцій.
Для того що б зменшити розмір файлу журналу транзакцій, необхідно спочатку:
Виконати його резервне копіювання (* .trn), при цьому переконавшись що повна резервна копія самої бази (* .bak) так само присутній.
Потім виконати стиснення файлу журналу транзакції за допомогою вищезгаданої операції або за допомогою команди DBCC SHRINKFILE або відповідної їй операції в графічному контексті Managment Studio:
У контейнері Database клацати правою кнопкою і в контекстному меню вибираємо: Tasks - Shrink - Files. 
У вікні в полі File Type вибираємо Log. Натискаємо Ок.
За допомогою команд T-SQL:
4. Дефрагментація індексів.
Індекси - це визначені користувачем структури даних, що дозволяють організувати швидкий доступ до даних без пошуку по всій БД.
Існує два способи дефрагментації індексу: реорганізація і перестроювання. Реорганізація (переіндексація) індексу (Reorganize Index) дефрагментирует кінцевий рівень кластеризованих і некластерізованних індексів таблиць, фізично змінюючи порядок сторінок кінцевого рівня для відповідності логічного порядку (зліва направо) вузлів кінцевого рівня. Упорядкування сторінок покращує продуктивність перегляду індексів. Індекс реорганізується всередині існуючих сторінок, виділених для індексу, нові сторінки не виділяються. Якщо індекс займає кілька файлів, файли реорганізовуються по одному. Сторінки не переміщаються між файлами. Реорганізація індексу так само стискає сторінки індексу. Всі порожні сторінки, створені цим стисненням видаляються, вивільняючи дисковий простір.
Перестроювання (пересозданіе) індексу (Rebuild Index) видаляє індекс і створює новий. При цьому фрагментація зникає, а дисковий простір звільняється за допомогою стиснення сторінок, використовуючи заданий або існуюче значення коефіцієнта заповнення. Рядки індексу упорядковуються заново в суміжних сторінках (при необхідності виділяються нові сторінки). Це може підвищити швидкодію диска, зменшуючи число читань сторінок, необхідне для отримання запитаних даних.
Рішення про те, реорганізовувати або перебудовувати індекс для усунення дефрагментації, має ґрунтуватися на існуючому рівні фрагментації індексу повідомляється середовищем SQL Server Managment Studio або процедурою sys.sm_db_index_physical_stats. например:
якщо sys.sm_db_index_physical_stats <= 30% - реорганізація.
якщо sys.sm_db_index_physical_stats = 30% - перестроювання.
Так само можна спробувати в роботі скрипт який сам виробляє аналіз фрагментації індексів і за результатами виробляє або реорганізацію або перестроювання.
5. Створення планів обслуговування.
Більшу частину адміністративних завдань можна автоматизувати при допомоги планів обслуговування (Maitenance Plans), які дозволяють планувати і виконувати за розкладом, різні операції з обслуговування. У версії SQL 2008R2 доступні для виконання наступні завдання:
Check Database Integrity - Перевірка цілісності баз даних (Відповідає виконання команди DBCC CHECKDB);
Shirink Database - Дозволяє зменшити розмір бази даних, вивільнивши з її файлів незайнятого простору. Зазвичай таке завдання застосовується для баз даних проміжного зберігання з великою кількістю тимчасових об'єктів і даних;
Reorganize Index - завдання для «м'якої» реорганізації індексів. При використанні цього завдання індекси реорганізуються, а не перебудовуються. Ступінь дефгментаціі виходить дещо гірше, зате потрібно менше системних ресурсів, і таблиці на час реорганізації залишаються доступними для користувачів;
Rebuild Index - повне перестроювання індексів (зазвичай для цілей дефрагментації). При налаштуванні цього завдання необхідно вибрати таблицю або подання або вказати всі таблиці або подання в певній базі даних;
Update strong Statistics - завдання оновлює статистику оптимізатора запитів (Query Optimizer) для об'єктів в базах даних SQL Server 2008. Зазвичай таке завдання використовується для великих баз даних, в яких автоматичне оновлення статистики відключено в цілях оптимізації продуктивності.
Clean Up History - очищення історії резервного копіювання, або історії виконання завдань SQL Server Agent, або історії виконання планів обслуговування. Мета проста - зменшити розмір бази даних msdb, видаливши з неї непотрібну інформацію;
Execute SQL Server Agent Job - завдання, за допомогою якої можна виконати завдання SQL Server Agent;
Back up Database Full - Повне резервне копіювання баз даних. На виході створюються файли з розширенням * .bak;
Back up Database Differential - Різницеве копіювання баз даних. Слід виконувати для мінімізації часу, який необхідний для відновлення часто змінюваних баз даних. Можливо тільки, в тому випадку, якщо створена повна резервна копія бази даних;
Back up Database (Transaction Log) - Резервне копіювання журналу транзакцій, на виході створюються файли з розширенням * .trn. Рекомендується виконувати перед повним резервним копіюванням баз даних, що дозволить істотно скоротити час відновлення на момент збою, а так само не дозволить розростися журналу транзакцій в часто змінюваних базах даних. Як правило не використовується в простій моделі відновлення (Simple);
Maintenance Clean Up Task - це завдання дозволяє видалити старі файли. Зазвичай вона використовується для видалення старих файлів резервних копій, але нічого не заважає вам використовувати її і для видалення будь-яких інших файлів;
Особисто мені в роботі не доводилося стикатися з великими часто змінюваними базами даних і тому завжди було досить в одному плані обслуговування тримати максимум три, чотири завдання, які виконуються в основному вночі, коли бази не використовуються. Зазвичай спочатку, у мене, запускається тест цілісності баз даних (Check Database Integrity), потім в разі успіху запускається або реорганізація (Reorganize Index) або перестроювання індексів (Rebuild Index), потім, необов'язковий крок, це резервне копіювання журналу транзакцій (Back up Database (Transaction Log)), і нарешті повне резервне копіювання самої бази (Back up Database Full). Розглянемо на прикладі: Перейдемо на вкладку Managment, далі Maitenance Plans - Maitenance Plan Wizzard (Майстер обслуговування планів). Задамо ім'я плану обслуговування:
В поле Schedule (Планувальник), натискаємо Change, де задаємо час виконання завдання.
Натискаємо ОК, Next. Потім виберемо ті завдання які будемо використовувати. 
На наступному екрані кнопками Move Up, Move Down виберемо черговість виконання завдань. Натискаємо Next.
Для кожного із завдань вибираємо бази, які хочемо задіяти. Всі інші настройки за замовчуванням. На екрані Select Report Options, вибираємо режим оповіщення про виконання плану (в текстовий файл або по e-mail), тиснемо Next, і нарешті Finish. В результаті отримаємо наступну картинку: 
Тут зелені стрілки означають, що перехід до наступного завдання можливий, тільки в разі успіху виконання попереднього (Success). Так само, можливі варіанти «не успіх», провалу завдання (Failure) - задаються червоними стрілками і варіант переходу до наступного завдання в будь-якому випадку (Completion) - задаються синіми стрілками. Для перемикання режимів, клацає на стрілочки правою кнопкою і вибираємо потрібний. Варіант переходу в разі успіху (Success), нам потрібен тільки на самому початку, коли перевіряється цілість баз даних (Check Database Integrity). Відповідно, якщо він не пройдений, то всі наступні завдання будуть не виконані. Решта зв'язку виставимо в режим Completion.
Отже, спочатку виконується перевірка цілісності баз даних (операція DBCC CHECKDB), потім в разі успіху проводиться повне перестроювання індексів, потім виконується резервне копіювання журналу транзакції (необхідна наявність повних резервних копій файлів баз даних), розмір якого істотно зростає, після операції перестроювання індексів, що дозволяє, істотно скоротити час відновлення бази в разі збою, і тільки потім повне резервне копіювання. Після виконання всіх завдань, трохи пізніше у мене в планувальнику стартує простий батнічек, який додатково пакує бекапи баз за поточну дату в окремі zip-архіви, які надалі можна буде записати на двд-болванку, usb-flash носій і здати за потрібне людям на зберігання :)
SET SQLBackup = "D: \ Microsoft SQL Server \ MSSQL10_50.MSSQLSERVER \ MSSQL \ Backup \" SET yyyy =% date: ~ 6,4% SET mm =% date: ~ 3,2% SET dd =% date: ~ 0 , 2% "% ProgramFiles% \ 7-Zip \ 7z.exe" a -tzip -mx7 H: \ Backups \ BUH8_backup_% date% .zip% SQLBackup% \ BUH_8_backup_% yyyy% _% mm% _% dd% _ * .bak ""% ProgramFiles% \ 7-Zip \ 7z.exe "a -tzip -mx7 H: \ Backups \ UT8_backup_% date% .zip% SQLBackup% \ UT_8_backup_% yyyy% _% mm% _% dd% _ * .bak "
Якщо необхідно складати бекапи в мережу, то скрипт наприклад може виглядати наступним чином:
net use z: \\ server \ Backups $ / user: Domain \ BackupUser password SET SQLBackup = "D: \ Microsoft SQL Server \ MSSQL10_50.MSSQLSERVER \ MSSQL \ Backup \" SET yyyy =% date: ~ 6,4% SET mm =% date: ~ 3,2% SET dd =% date: ~ 0,2% "% ProgramFiles% \ 7-Zip \ 7z.exe" a -tzip -mx7 Z: \ Backups \ BUH8_backup_% date% .zip% SQLBackup% \ BUH_8_Work_backup_% yyyy% _% mm% _% dd% _ *. bak ""% ProgramFiles% \ 7-Zip \ 7z.exe "a -tzip -mx7 Z: \ Backups \ UT8_backup_% date% .zip% SQLBackup% \ UT_8_backup_% yyyy% _% mm% _% dd% _ *. bak "net use z: / delete
Для найкращого стиснення, замість zip, можна іcпользовать рідний формат 7z, для цього прибираємо параметр -tzip і міняємо розширення одержуваних файлів на * .7z.