Задача: оформить в виде пошаговых инструкций, как сделать и развернуть из бекапа базу данных
Почему я заостряю свое внимание на столь незначительных простых действиях, а все дело в том, что у меня на прошлой неделе был запланирован долго готовящийся переход в программных средств обеспечения инфраструктуры предприятия и старого сетевого оборудования на новое — а именно я всю компанию перевел на продукцию именуемую, как Mikrotik.
И вот входе этого самого переезда я столкнулся с невозможностью на тот момент когда осуществлялся переезд сделать столь простые действия, хотя уже сейчас понимаю, что здесь в этих действиях не было столько сложного как на первый взгляд казалось. Все дело было в недостатке сна который требовал организм после 40 часов работы в режиме — работа.
Также я повествую о всех трудностях возникнувших у меня и как я их решал. Публикую все это на своем блоге я создаю своего рода заготовку на будущее (следующие более продуктивные работы в частности перевод инфраструктуры IT на новое на следующих местах работы).
Есть сервер базы данных развернутый по заметке моего блога, бекап имеющейся базы данных делается следующим образом:
Открываю оснастку Microsoft SQL Server Management Studio
Start — All Programs — Microsoft SQL Server 2008 R2 — SQL Server Management Studio, авторизуюсь (у меня настроена двухфакторная авторизация)
Server type: Database Engine
Server name: (local)
Authentication: Windows Authentication
User name: POLYGON\ekzorchik
Password:
и нажимаю Connect
Теперь находясь в оснастке управления сервером баз данных поступаю следующим образом, на имеющейся базе данных (имя базы: telephone) вызываем задачу Back up… (как это сделать смотрите ниже представленный скриншот)
(local) (SQL Server 10.50.1600 — POLYGON\ekzorchik) — Databases — telephone — через правый клик мышью — Tasks — Back up…
После чего настраиваем задачу на создание резервной копии базы данных с именем: telephone
На вкладке General преднастраиваю:
Database: telephone
Backup Type: Full (тип бекапа, я выбираю полный, для меня так проще, я ведь не специалист по администрированию MSSQL чтобы заморачиваться с различными опциями, мне нужна резервная копия и точка)
Backup set:
Name: telephone-Full Database backup
Description: telephone
Destination: Disk — Add — и указываю путь до каталога где я хочу видеть создаваемую резервную копию базы данных: C:\backupbd\Telephone
Переключаюсь на вкладку Options:
Back up to the existing media set: Overwrite all existing backup sets
Reliability: Verify backup when finished
Set backup compression: Compress backup
Как только все параметры задачи предопределены нажимаю кнопку «OK» и в левом углу начинается исполнение задачи:
Чем больше база тем длительней процесс.
На заметку: если хотите попробовать что-то с базой и функционалом который она выполняет, всегда делайте резервную копию.
По прошествии некого количества времени появится всплывающее сообщение с текстом, что резервная копию успешно создалась:
TITLE: Microsoft SQL Server Management Studio
——————————
The backup of database ‘telephone’ completed successfully.
——————————
BUTTONS:
OK
Предположим, что изменения с базой привели к ее падению или базу удалили:
(local) (SQL Server 10.50.1600 — POLYGON\ekzorchik) — Databases — telephone — через правый клик мышью — Delete
было отмечено: Delete backup and restore history information for databases
Теперь я пройдусь по шагам восстановления из созданного бекапа:
Также находясь в оснастке SQL Server Management Studio — через правый клик на Databases — вызываю меню: Restore Database…
To database: указываю как именовать восстанавливаемую базу данных, в моем случае это telephone
Source for restore: From device: и через обзор нахожу в файловой системе резервную копию, в моем случае: C:\backupbd\telephone.bak
Нажимаю кнопку «Ок» и ожидаю также в левом углу отображается процесс исполнения задачи согласно действиям выше. По аналогии с бекапированием ожидаем появление на экране надписи, что восстановление успешно завершено.
TITLE: Microsoft SQL Server Management Studio
——————————
The restore of database ‘telephone’ completed successfully.
——————————
BUTTONS:
OK
Это все конечно хорошо, но вот когда баз данных становится много осуществлять их резервное копирование через ручной способ уже не совсем просто, а я бы сказал утомительно и однообразно. Нужно автоматизировать данную процедуру. Когда настраиваем параметры для создания резервной копии в оснастке выше есть возможность те параметры которые я предопределяю (Script — Script Action to File или сочетание клавиш: Ctrl + Shift + F) сохранить в файл с расширение sql. Выглядит его содержимое следующим образом:
BACKUP DATABASE [telephone] TO DISK = N'C:\backupbd\Telephone.bak' WITH NOFORMAT, NOINIT, NAME = N'telephone-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'telephone' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'telephone' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''telephone'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\backupbd\Telephone.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
изменяя его содержимое под другие базы данных получаем sql файлы которые запускаем так:
sqlcmd -S SEVERNAME -U UserName -P Password -i, а вообще лучше ознакомиться самостоятельно с мануалом так будет намного полезнее чем приводить все возможные комбинации аутентификации в сервисе баз данных MS SQL 2008 R2
(На заметку: если используется двухфакторная авторизация в оснастке Management Studio, то команда:)
C:\Windows\system32>sqlcmd -i c:\1\backup.sql
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 497048 pages for database ‘telephone’, file ‘telephone’ on file 1.
100 percent processed.
Processed 2 pages for database ‘telephone’, file ‘telephone_log’ on file 1.
BACKUP DATABASE successfully processed 497050 pages in 111.659 seconds (34.777 M
B/sec).
The backup set on file 1 is valid.
А запустить скрипт через оснастку можно так:
находясь в оснастке SQL Server Management Studio — File — Open — File… – указываем местонахождение скрипта (в моем случае: c:\1\backup.sql) и нажимаем на элемент Execute, смотрите скриншот чтобы понять где этот элемент находится.
На заметку: По такому принципу можно создать задание где указать все параметры и базы и сохранить задание в командный файл sql.
Но вот, что мне еще нужно это возможность добавление временного штампа в создаваемый бекап, т. к. запуск скрипта (у меня предопределена опция переписывания создаваемой резервной копии) привод к затиранию уже существующего, можно конечно поправить, то если бы резервную копию наделить именованием даты и времени было бы как нельзя кстати:
DECLARE @pathName NVARCHAR(512)
SET @pathName = 'C:\backupbd\telephone_' + Convert(varchar(8), GETDATE(), 112) + '.bak'
BACKUP DATABASE [telephone] TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N'telephone',SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
После выполнения данного скрипта резервная копия базы данных приняла вид: telephone_20151022.bak — что мне и требовалось.
Если мне потребуется осуществлять резервное копирование нескольких баз, то я для этих целей подготавливаю простой bat файл следующего вида:
sqlbackup.bat
echo off
sqlcmd -i c:\script\telephone.sql
sqlcmd -i c:\script\<database2>.sql
sqlcmd -i c:\script\<database3>.sql
sqlcmd -i c:\script\<database4>.sql
exit
который в последствии можно прикрутить к планировщику заданий Windows системы.
На этом я заканчиваю с этой заметкой, и прощаюсь до встречи, с уважением автор блога — ekzorchik.