Резервное копирование баз данных Microsoft SQL Server. Автоматизация настройки резервного копирования MS SQL с помощью.NET приложения

Продолжаем разговаривать о резервном копировании и сегодня мы научимся создавать архив базы MS SQL Server 2008 . Рассматривать все будем как обычно на примерах с использованием, как графического интерфейса, так и с использованием SQL запроса, а также настроим автоматическое создание backup с помощью батника .

К вопросу о важности резервного копирования базы данных мы возвращаться не будем, так как мы уже не раз поднимали эту тему, например в материалах:

И в последней статье я говорил, что мы рассмотрим возможность создания архива на СУБД MS SQL Server 2008, поэтому сейчас мы займемся именно этим.

И так как теории уже было много сразу перейдем к практике, а именно к созданию backup базы.

Примечание! Как видно их названия статьи архив мы будем делать на СУБД Microsoft SQL 2008 с использованием Management Studio. Сервер располагается локально. ОС Windows 7.

Как создать архив базы SQL сервера

Давайте определимся, что архив мы будем делать тестовой базы с названием «test». С начала через графический интерфейс, и в процессе этого запишем сценарий, для того чтобы в дальнейшем можно было просто запустить его и уже не отвлекаться на ввод всякого рода параметров.

Открываем Management Studio, раскрываем «Базы данных », выбираем нужную базу, щелкаем правой кнопкой мыши по ней, выбираем Задачи->Создать резервную копию

У Вас откроется окно «Резервное копирование базы данных » где Вы можете задать параметры, архивирования. Я всего лишь задал имя «Резервного набора данных », а также изменил название архива и путь, так как по умолчанию он будет создаваться в папку Program Files, например, у меня по умолчанию был путь

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\

Для примера я изменил его на C:\temp\ и назвал архив test_arh.bak

Также если перейти на пункт «Параметры » то можно задать настройку перезаписывать все наборы данных, сейчас объясню что это. Если Вы оставите все как есть, т.е. добавлять к существующему набору данных то у Вас файл с резервной копией будет один, но с несколькими экземплярами наборов данных, т.е. при восстановлении Вы просто выберете нужный Вам набор. А если поставите «Перезаписывать все существующие резервные наборы данных » то набор всегда будет один, тогда в этом случае Вам необходимо будет создавать архивы (допустим ежедневные) с разными названиями. Я поставил перезаписывать, так как допустим, в дальнейшем, я планирую создавать архивы за каждый день с указанием даты в названии этих архивов, чтобы оперативно в случае необходимости скопировать нужный мне backup за определенную дату в любое место.

И кстати, на этом моменте, после ввода всех параметров, можно создать сценарий, для того чтобы записать его и в дальнейшем использовать. Для этого просто вверху нажимаем «Сценарий »

И в результате этого действия у Вас откроется окно запросов, в котором и будет код данного сценария. К нему вернемся чуть позже, а пока жмем «ОК» и после завершения операции у Вас появится окно, в котором будет указан результат выполнения резервного копирования, если все хорошо, то будет следующее сообщение

Создаем архив базы SQL сервера через запрос

Если Вы все сделали, как указанно выше (т.е. нажали «Сценарий») то у Вас открылось окно запросов, в котором собственно и есть сам запрос создания архива, но мы его немного переделаем, так как я сказал, что мы планируем запускать его каждый день, поэтому чтобы название было соответствующее, мы напишем вот такую SQL инструкцию.

Declare @path as varchar(200) set @path = N"C:\temp\test_arh_" + CONVERT(varchar(10), getdate(), 104) + ".bak" BACKUP DATABASE TO DISK = @path WITH NOFORMAT, INIT, NAME = N"База данных test", SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

И теперь если мы запустим его, то у нас создастся резервная копия базы данных с названием test_arh_Текущая дата .bak

Автоматическое создание backup на SQL сервере

Для этих целей в MS SQL 2008 существует специальная возможность под названием «Планы обслуживания » где как раз можно настроить расписание по созданию резервной копии баз данных, но я предлагаю для этих целей использовать bat-файл чтобы его настроить в планировщике и чтобы он каждый день запускался и делал backup базы данных. Для этого скопируйте SQL инструкцию, которую мы рассмотрели выше, и вставляйте ее в блокнот (рекомендую Notepad++) затем сохраняете с расширением .sql т.е. этот сценарий будет выполняться на MS Sql 2008. Затем нам останется написать батник, для того чтобы он подключился к SQL серверу и выполнил наш сценарий. Также в блокноте пишем:

SET cur_date=%date:~6,4%%date:~3,2%%date:~0,2% osql -S localhost -i C:\temp\test.sql -o C:\temp\%cur_date%_log_sql.log –E

где, я создал переменную cur_date для того чтобы в ней хранить текущую дату, затем подключаюсь к локальному серверу, через утилиту osql которая использует ODBC и выполняю наш сценарий (я его назвал test.sql), а также записываю лог, где и как раз нам понадобилась наша переменная, все, сохраняем с расширением .bat , создаем задание в планировщике и можно сказать забываем про процесс архивирования базы данных, ну только периодически проверяем, создался ли архив или нет.

Для основ этого совсем достаточно, теперь Вы знаете, как можно создавать резервные копии баз данных на 2008 SQL сервере, в следующей статье мы рассмотрим, как можно восстановить базу данных на MS Sql 2008. А пока все! Удачи!

В данной статье будет рассказано как вручную сделать полную резервную копию базы данных в с помощью программы «Среда Microsoft SQL Server Management Studio».

1. Создание резервной копии

На самом деле все довольно просто. Запускаем оснастку «» («Пуск » — «Все программы » — «SQL Server 2008 R2 » — «Среда Microsoft SQL Server Management Studio ») и вводим данные для авторизации.

После чего в Обозревателе объектов раскрываем вкладку «Базы данных » и кликнем правой кнопкой мыши по той базе данных, для которой необходимо сделать резервную копию. В появившемся контекстном меню выберем «Задачи » (Tasks ) — «Создать резервную копию » (Back Up… ) .

Запустится окно «Резервная копия базы данных » (Back Up Data Base ) . Убедимся, что стоит «Полная » (Full ), при необходимости зададим имя и описание, а также укажем назначение резервной копии. По умолчанию выбран путь на жестком диске компьютера в папку Backup основного расположения баз SQL-сервера. Для того чтобы изменить место размещения копии, сначала нажмем «Удалить » (Remove ), чтобы удалить существующее назначение, а затем «Добавить » (Add …) для добавления нового.

Здесь зададим расположение и имя файла резервной копии и нажмем «ОК » . Таких мест назначений можно задать несколько. В этом случае резервная копия будет разбита на равные части, каждая часть в указанном файле.

Когда все настройки установлены, нажимаем «ОК » и дожидаемся завершения задачи. Если все сделано правильно, в указанной директории мы найдем файл резервной копии базы данных SQL.

2. Восстановление базы данных из резервной копии

Восстановление происходит по аналогичной схеме. В «Среде Microsoft SQL Server Management Studio » выбираем базу из которой сделана резервная копия , кликаем по ней правой кнопкой мыши, в контекстном меню выбираем «Задачи » (Tasks ) — «Восстановить » (Restore ) — «База данных… » (Database… ).

Откроется окно «Восстановление базы данных » (Restore Database ). Здесь, в качестве источника укажем «С устройства » (From device ) и выберем файл резервной копии (созданных в пункте 1).

Установим флаг «Восстановить » (Restore ) напротив выбранной резервной копии. При необходимости, на вкладке «Параметры » (Options ), можно указать дополнительные параметры восстановления, о значении которых можно прочитать .

После того, как все настройки сделаны, жмем «ОК » и дожидаемся сообщения об успешном восстановлении базы данных.

3. Восстановление резервной копии в другую базу данных (копирование данных)

Если же необходимо загрузить данные в базу данных, отличную от той из которой была сделана резервная копия , то при загрузке помимо действий, описанных в пункте 2, необходимо на вкладке «Параметры » (Options) задать имена файлов этой базы данных и установить флаг «Перезаписывать существующую базу данных » (WITH REPLACE).

Помогла ли Вам данная статья?

5 ответов

Есть, вероятно, больше способов сделать это, но я обычно щелкаю правой кнопкой мыши базу данных и выбираю "Задачи → Резервное копирование..." с типом резервного копирования "Полный". После этого вы можете скопировать созданный файл на ваш целевой компьютер, подключиться к его SQL Server в SQL Server Management Studio, щелкните правой кнопкой мыши папку "Базы данных" и выберите "Восстановить базу данных". Выберите "Устройство" и выберите файл, затем восстановите.
Другим подходом будет script база данных в SQL Server Management Studio (щелкните правой кнопкой мыши базу данных, затем "Задачи → Сгенерировать скрипты..."). Во время этого процесса будет выполнен шаг под названием "Установить параметры сценариев", где вам нужно будет нажать кнопку "Дополнительно" и внимательно изучить варианты. Вы обязательно захотите выбрать "Данные и схема" для опции "Типы данных для script". Я иногда предпочитаю этот метод, если я действительно хочу, чтобы структуры данных и данные были переданы.

Обновление: Извините, я забыл упомянуть, как восстановить базу данных для параметра сценариев. Я всегда создаю сценарии, выбрав "Сохранить в новом окне запроса" во время шага "Установить параметры сценариев". После создания script просто оставьте его на мгновение.
На целевом сервере создайте новую базу данных с тем же именем, что и для созданных сценариев. Или вы можете создать script для этого на исходном сервере (щелкните правой кнопкой мыши базу данных, выберите "Script База данных как → CREATE TO... → Clipboard") и выполните этот script, щелкнув правой кнопкой мыши сервер node в проводнике объектов SSMS, выбрав "Новый запрос", вставив script в окно запроса и выполнив его. Этот второй вариант - лучший выбор, если вам действительно нужна полная копия базы данных, а не только сама информация.
Как только вы спуститесь по одной из этих двух дорог, вы должны создать новую базу данных. Щелкните правой кнопкой мыши эту базу данных в обозревателе объектов и выберите "Новый запрос", затем скопируйте и вставьте script, содержащий структуру и данные базы данных, в новое окно запроса и выполните запрос. Это должно сделать трюк.

Вы не можете скопировать базу данных на другую машину. Да, вы можете взять резервную копию на тот же компьютер и скопировать его на другую машину и восстановить.

Чтобы выполнить резервное копирование, выполните следующие действия:

• Щелкните правой кнопкой мыши базу данных, которую вы хотите сделать резервной.
• Выберите "Задача" → "Назад".
• В пункте назначения выберите "Добавить".
• В имени файла нажмите кнопку... и выберите папку назначения, в которую вы хотите создать резервную копию с именем backupname.bak. Нажмите Ok, Ok и Ok. и дождитесь завершения процесса резервного копирования. Нажмите "ОК" .

Теперь скопируйте этот файл резервной копии в pendrive или любой носитель и вставьте его на другой компьютер и откройте SQL Server 2008 R2

Чтобы восстановить резервную копию, выполните следующие действия:

• Щелкните правой кнопкой мыши по базам данных.
• Выберите "Восстановить базу данных".
• Введите имя базы данных, которое вы хотите восстановить в поле "База данных"
• Выберите "Из устройства" в источнике для восстановления. Нажмите...
• Нажмите кнопку "Добавить", выберите файл резервной копии базы данных, который вы вставили. Нажмите "ОК" , "ОК" .
• Установите флажок Восстановить в Выбор наборов beckup для восстановления.
• Включите опцию Проверить Перезаписать существующую базу данных и сохранить параметры репликации (эти поля необходимо проверить только при попытке восстановить базу данных, которая уже находится на этом другом устройстве).
• Нажмите "ОК" . дождитесь завершения восстановления и нажмите "ОК" .

Скажите, если у вас возникнут какие-либо проблемы.

По коду

Резервное копирование:

USE DATABASE_NAME; GO BACKUP DATABASE DATABASE_NAME TO DISK = "D:\DATABASE_NAME.Bak" WITH FORMAT, MEDIANAME = "D_SQLServerBackups", NAME = "Full Backup of DATABASE_NAME"; GO

(Если вы хотите поместить резервную копию в любую папку, папка должна существовать до того, как вы сделаете резервную копию.)

Восстановить:

Шаг 1. Извлеките логическое имя файла базы данных из резервной копии.

RESTORE FILELISTONLY FROM DISK = "D:BackUpYourBaackUpFile.bak" GO

Шаг 2: Используйте значения в столбце LogicalName в следующем шаге. ---- Сделать базу данных в режиме одиночного пользователя

ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Восстановить базу данных

RESTORE DATABASE YourDB FROM DISK = "D:BackUpYourBaackUpFile.bak" WITH MOVE "YourMDFLogicalName" TO "D:DataYourMDFFile.mdf", MOVE "YourLDFLogicalName" TO "D:DataYourLDFFile.ldf"

/Если в заявлении нет ошибки перед тем, как база данных будет находиться в многопользовательском режиме Режим. Если возникла ошибка, выполните следующую команду, которую он преобразует база данных в нескольких пользователях. /

ALTER DATABASE YourDB SET MULTI_USER GO

Копирование базы данных с использованием полной резервной копии базы данных не будет копировать транзакции в онлайн-журнале транзакций.

Если это важно, используйте следующие шаги, чтобы отключить базу данных в автономном режиме, скопировать файлы MDF и LDF и прикрепить их:

• Выберите базу данных в SQL Server Management Studio, щелкните правой кнопкой мыши базу данных и выберите "Свойства". Скопируйте расположение файлов MDF и LDF.

2.Нажмите ОК

3.Чтобы снова щелкнуть базу данных, выберите Задачи , В автономном режиме

4. В проводнике Windows скопируйте файлы MDF и LDF, используя местоположение, найденное в шаге # 1

5.Отложить их в другое место 6. В SQL Server Management Studio щелкните правой кнопкой мыши экземпляр SQL Server и выберите Прикрепить

7. В следующем диалоговом окне нажмите Добавить , найдите скопированные файлы, выберите их и нажмите ОК 8.Измените имя по умолчанию, предлагаемое в поле Прикрепить AS . Укажите здесь новое имя для своей базы данных.

По материалам статьи Rahul Sharma на sqlservercentral.com: " Copying a Database from Server to Server "

Метод 1: самый быстрый способ копирования - отсоединение базы данных от исходного сервера и затем прикрепление вместе с журналом на нужный сервер.

Отсоедините базу данных на исходном сервере (Измените, соответственно, имя базы данных):

Use Master GO Exec sp_detach_db "database_name", "true" GO

sp_detach_db отсоединяет базу данных от исходного сервера (у неё два параметра: @dbname, который является именем базы данных и @skipchecks, который является указанием для обновления статистики) и указав значение "true" для второго параметра (@skipchecks) этой хранимой процедуры, что бы удостоверится в том, что если модификация статистики не была выполнена перед отсоединением базы данных от сервера, она обновится после присоединения, что потребует некоторого времени. Скопируйте данные и журналы из каталога Data исходного сервера в каталог данных на новом сервере. Удостоверитесь, что Вы не имеете точно таких же баз данных на сервере адресата.... Если это так, отключите их.
Прикрепите данные и журналы на новый сервер. Выполните на этом сервере:

Use Master GO PRINT "Attaching Database" EXEC sp_attach_db @dbname = "database_name", @filename1 = "c:\mssql7\data\database_name.mdf", -- Это путь к файлу данных @filename2 = "d:\mssql7\data\database_name_log.ldf" -- Это путь к журналу

Этим Вы прикрепите базу данных к новому серверу, но учётные данные пользователей, для подключения к базе не будут скопированы с исходного на новый сервер. Вы можете использовать нижеследующий сценарий, чтобы перенести логины:

/* Установите связанный сервер (используя sp_addlinkedserver и sp_addlinkedsrvlogin), назвав его: sourceserver, и из которого стандартные логины входа в систему должны быть перенесены. Вы можете называть его, как Вам удобно и изменить также имя связанного сервера. Чтобы обеспечить доступу к данным связанного сервера, Вы должны использовать sp_serveroption */

declare @login sysname , @password sysname declare sourcelogins cursor for select name , password from sourceserver.master.dbo.syslogins where isntname = 0 and charindex("repl_" , name) = 0 and charindex("distributor" , name) = 0 and name != "sa" open sourcelogins while (@@fetch_status = 0) begin fetch sourcelogins into @login , @password exec sp_addlogin @login , @password , @encryptopt = "skip_encryption" end close sourcelogins deallocate sourcelogins go

При откреплении и прикреплении баз данных, я столкнулся с ещё одной проблемой, кроме потери связи пользователей и их логинов, описанной выше. Я обнаружил, что статистика останется не эффективной, если Вы выполните sp_updatestats на прикрепляемой базе данных. Так, я рекомендовал бы обновить статистику после того, как Вы уже прикрепили эту базу. Или воспользуйтесь альтернативным вариантом: когда Вы открепляете базу данных, удостоверьтесь, что второй параметр установлен в Ложь, что заставит статистику обновиться, и Вам не придётся обновлять её позже на новом сервере. Любой из этих путей прекрасно работает.
Этот метод также великолепно работает, если необходимо переместить базы данных на диск и затем прикрепить их на другой сервер, который не находится в сети. Также, это - очень быстрый путь копирования баз данных с сервера на сервер. Не забудьте прикрепить копируемую базу назад на исходный сервер, как только Вы скопировали данные и журналы с исходного сервера на сервер адресат.

Метод 2: Использование DTS.

DTS очень часто используется для перемещения баз данных с сервера на сервер. Вы можете использовать мастер экспорта/импорта DTS (SQL 7.0 и 2000). Мастер может использоваться для копирования схем, объектов (хранимые процедуры, представления и триггеры и т.д.), данных и также логинов. Или Вы можете использовать DTS Designer и создать задачу перемещения базы данных и задачу перемещения логинов (доступно только для SQL 2К). Также, Вы можете использовать мастер копирования баз данных (Copy Database Wizard), чтобы решить задачу перемещения базы (доступно только для SQL 2К). DTS - довольно мощный инструмент и если Вы его пока не использовали, Вы лишаете себя многих функциональных возможностей и лёгкости, с которой Вы могли бы решать сложные задачи.
В случае если Вы ищете хорошую ссылку на DTS, я предложил бы Вам почитать:
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/dts_overview.htm

Метод 3: Создайте схему и механизм переноса данных, использующий bcp/bulk insert.

Создайте схему на сервере приёмнике данных, если Вы уже имеете скрипт для создания схемы и затем используете bcp или bulk insert, чтобы скачать данные. Оба операции - нерегистрируемые, так что они отработают очень быстро. Главное различие между bcp и bulk insert - это то, что bulk insert не может экспортировать данные, а bcp может.
Вы можете использовать bcp, чтобы экспортировать данные в плоский файл и затем импортировать данные в новую базу из плоского файла, используя bcp или bulk insert. Даже при том, что bulk insert является быстрым способом закачки данных, он имеет большое количество ограничений.

Метод 4: Традиционный путь: Backup и Restore.

Сделайте полную копию базы данных, и затем восстановите её на новом сервере.

Метод 5: Использование распределённых запросов.

Вы сначала должны создать схему на сервере приёмнике данных, используя ваши скрипты создания схем. После этого Вы можете организовать связанный сервер и написать инструкции вставки, которые будут вставлять данные из источника на новый сервер, используя функции openrowset и openquery для запросов к связанным серверам. Вы должны удостовериться, что foreign key и check constraints отключены до того, как Вы начнёте закачивать данные и затем подключить их, когда всё будет сделано. Этот метод самый медленный из всех упомянутых в этой статье. Зато, с помощью него можно переносить данные на SQL Server из гетерогенных источников, например: Oracle, Sybase, DB2 и т.д.