Как сделать копию базы данных sql server

Копирование баз данных путем создания и восстановления резервных копий

В SQL Server можно создать новую базу данных, восстанавливая резервную копию пользовательской базы данных, созданной с помощью SQL Server 2005 (9.x) или более поздней версии. Однако резервные копии главных, моделей и msdb, созданных с помощью более ранней версии SQL Server, не могут быть восстановлены SQL Server. Кроме того, резервные копии SQL Server не могут быть восстановлены любой более ранней версией SQL Server.

SQL Server 2016 использует путь по умолчанию, отличный от пути, использованного в предыдущих версиях. Поэтому для восстановления резервной копии базы данных, созданной в месте расположения по умолчанию для ранних версий, необходимо использовать параметр MOVE. Сведения о новом пути по умолчанию см. в разделе Расположение файлов для экземпляра по умолчанию и именованных экземпляров SQL Server. Дополнительные сведения о перемещении файлов баз данных см. в статье «Перемещение файлов баз данных» далее в этом подразделе.

Основные этапы копирования базы данных, используя функции резервного копирования и восстановления

При использовании резервного копирования и восстановления для копирования базы данных на другой экземпляр SQL Server компьютер-источник и целевой компьютер могут быть любой платформой, на которой запускается SQL Server.

Создайте резервную копию исходной базы данных, которая может находиться в экземпляре SQL Server 2005 (9.x) или более поздней версии. Компьютер, на котором выполняется этот экземпляр SQL Server, является исходным компьютером .
На компьютере, куда нужно скопировать базу данных ( целевой компьютер), подключите экземпляр SQL Server, на котором будет восстановлена база данных. При необходимости создайте те же устройства резервного копирования на целевом экземпляре сервера, что использовались для резервного копирования баз данных- источников .
Восстановите резервную копию базы данных- источника на целевом компьютере. При восстановлении базы данных автоматически создаются все ее файлы.

Рассматриваются дополнительные вопросы, которые могут повлиять на процесс.

Перед восстановлением файлов базы данных

При восстановлении базы данных необходимые файлы базы данных создаются автоматически. По умолчанию у файлов, созданных SQL Server в процессе восстановления, те же имена и пути, что и у файлов резервной копии исходной базы данных на компьютере-источнике.

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

Это может быть необходимо в следующих ситуациях.

Нужная структура каталогов или сопоставление дисков, используемые на исходном компьютере, могут отсутствовать на другом компьютере. Например, возможно, резервная копия содержит файл, который нужно восстановить на диск E, но на целевом компьютере диска E — нет.
на целевом диске может быть недостаточно свободного места;
Если используется имя базы данных, которое уже существует на целевом сервере восстановления, а имя каждого из ее файлов совпадает с именем файла базы данных в резервном наборе данных, происходит одно из следующих действий.
- Если существующий файл базы данных может быть перезаписан, он будет перезаписан (это не затронет файл, относящийся к базе данных с другим именем).
- Если существующий файл не может быть перезаписан, возникнет ошибка восстановления.
Во избежание ошибок и непредвиденных последствий перед операцией восстановления можно использовать таблицы журнала backupfile , чтобы найти в резервной копии файлы базы данных и журнала, которые планируется восстановить.

Перемещение файлов баз данных

Если файлы резервной копии базы данных невозможно восстановить на целевом компьютере, необходимо переместить файлы в новое место назначения, где они могут быть восстановлены. Например:
- Нужно восстановить базу данных из резервных копий, созданных в месте расположения по умолчанию для предыдущей версии.
- Может оказаться необходимым восстановить некоторые файлы базы данных из резервной копии на другой диск из-за нехватки места на диске по умолчанию. Такое случается довольно часто, потому что у большинства компьютеров в организациях разное число и параметры дисковых накопителей и различные конфигурации программного обеспечения.
- Может оказаться необходимым создать копию существующей базы данных на том же компьютере для тестирования. В этом случае файлы базы данных для исходной базы данных уже существуют, и когда во время операции восстановления создается копия базы данных, должны быть указаны другие имена файлов.
Дополнительные сведения см. в подразделе «Восстановление файлов и файловых групп в новое место назначения» далее в этом разделе.

Изменение имени базы данных

Можно изменить имя базы данных при восстановлении на целевом компьютере без восстановления файлов с последующим изменением имени базы вручную. Например, нужно изменить имя базы данных с Sales на SalesCopy , чтобы указать на то, что это копия базы данных.

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

Обновление базы данных с помощью восстановления

При восстановлении резервных копий из предыдущей версии желательно заранее знать, существует ли на целевом компьютере путь (диск и каталог) для каждого полнотекстового каталога резервной копии. Чтобы вывести список логических имен и физических имен, пути и имени файла) каждого файла в резервной копии, включая файлы каталога, используйте инструкцию RESTORE FILELISTONLY FROM . Дополнительные сведения см. в разделе Инструкция RESTORE FILELISTONLY (Transact-SQL).

Если нужный путь на целевом компьютере не существует, есть два варианта.
- Создать нужное сопоставление дисков или структуру каталогов на целевом компьютере.
- Переместить файлы каталогов на новое место назначения во время операции восстановления с помощью предложения WITH MOVE инструкции RESTORE DATABASE. Дополнительные сведения см. в статье Инструкция RESTORE (Transact-SQL).
Дополнительные сведения об альтернативных параметров обновления полнотекстовых индексов см. в разделе Обновление полнотекстового поиска.

Владелец базы данных

При восстановлении базы данных на другом компьютере имя входа SQL Server или пользователь Microsoft Windows, который инициирует операцию восстановления, автоматически становится владельцем новой базы данных. При восстановлении базы данных системный администратор или владелец новой базы данных могут сменить ее владельца. Для предотвращения несанкционированного восстановления базы данных устанавливайте пароли на носители или сами резервные копии.

Управление метаданными при восстановлении базы данных на другой экземпляр сервера

Чтобы обеспечить целостность работы пользователей и приложений при восстановлении базы данных на другой экземпляр сервера, на новом экземпляре необходимо повторно создать некоторые или все метаданные, например имена входа и задания. Дополнительные сведения см. в статье Управление метаданными при обеспечении доступности базы данных на другом экземпляре сервера (SQL Server).

Просмотр файлов данных и журналов в резервном наборе данных

Восстановление файлов и файловых групп в новом расположении
- Восстановление файлов в новое расположение (SQL Server)
- Restore a Database Backup Using SSMS
Восстановление файлов и файловых групп поверх существующих файлов

Восстановление базы данных с новым именем

Перезапуск прерванной операции восстановления

Изменение владельца базы данных

Копирование базы данных с помощью управляющих объектов SQL Server (SMO)

Резервное копирование баз данных

Резервное копирование баз данных можно производить несколькими способами:

Автоматическое резервное копирование (см. Автоматическое резервное копирование ниже).

Резервное копирование баз данных в SQL Server Management Studio

Для создания резервной копии базы данных необходимо выделить ее в дереве объектов SQL Server Management Studio 2) , в контекстном меню выбрать пункт «Задачи → Создать резервную копию…».

В окне «Резервное копирование базы данных» (Рис. 1) на странице Общие в разделе «Источник» в поле «Тип резервной копии выбрать Полная; в разделе «Назначение» по кнопке «Добавить» указать файл резервной копии базы данных. Для проверки целостности копии базы данных на закладке Параметры в разделе «Надежность» установить флаг Проверить резервную копию после завершения.

Нажатием кнопки «OK» запустить создание резервной копии выбранной базы данных и дождаться сообщения «Резервное копирование базы данных «» успешно завершено.».

Автоматическое резервное копирование

Настройка автоматического резервного копирования баз данных возможна разными способами. Здесь приведена схема работы скрипта, создающего резервные копии указанных баз данных в указанные папки.

Скрипт запускается непосредственно на SQL Server’e, имя инстанции SQL Server указывается в скрипте. Для выполнения SQL -кода указывается путь к соответствующей утилите. Создается резервная копия базы данных с указанием даты в имени файла. Файл сохраняется локально по указанному пути. Создаются лог-файлы резервного копирования для каждой базы с указанием имени базы в названии файла и общий лог-файл.

Файл резервной копии запаковываются архиватором. В скрипте необходимо указать используемый архиватор.

Инструкция по созданию/восстановлению резервных копий баз данных

Резервные копии рабочих баз данных, т.н. backup (бэкап), рекомендуется делать регулярно (на случай аппаратных и/или программных сбоев на сервере) с использованием планировщиков заданий, процесс настройки которых подробно описан в инструкциях по установке сетевых версий программ Альта-Софт (включая «Альта-ГТД»). Кроме того, в самой программе «Альта-ГТД» имеется функция, позволяющая создать резервную копию рабочей базы – см. меню Сервис/Бэкап БД SQL.

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

Перенос базы данных с одного SQL-сервера на другой также необходимо делать с помощью операций:
- Создание резервной копии базы данных (backup)
- Восстановление базы данных из резервной копии (restore)
Создание резервной копии базы данных (backup)
1. Запустить утилиту SQL Server Management Studio (из состава MS SQL Server).
2. Подключиться к серверу под учетной записью администратора или владельца БД (можно использовать встроенную учетную запись «sa», пароль для которой задавался при установке SQL Server, либо выбрать вариант «Проверка подлинности Windows» в случае если текущий пользователь сеанса Windows обладает правами администратора в SQL Server, а также использовать любую другую учетную запись SQL Server или Windows, которая включена в роль «db_owner» копируемой базы):
3. Нажать правой кнопкой мыши на имени копируемой БД (в разделе «Базы данных») и выбрать меню «Задачи/Создать резервную копию»:
4. В разделе «Назначение» указать путь и имя файла (путь всегда задается для компьютера, где установлен сам SQL Server!), в который будет выгружена база данных, для чего использовать кнопки «Удалить» и «Добавить» до тех пор, пока в поле «Создать резервную копию на» не будет отображен ровно один желаемый путь:
5. На странице «Параметры» установить переключатель «Создать резервную копию в новом наборе носителей…» и галочку «Проверить резервную копию после завершения»:
6. Нажать кнопку «ОК».
Примечание. Резервные копии баз данных SQL Server обычно хорошо сжимаются архиваторами (WinRAR, WinZIP и т.п.), поэтому размер полученного файла (*.bak) можно сильно уменьшить с их помощью.

Восстановление базы данных из резервной копии (restore)
1. Запустить утилиту SQL Server Management Studio (из состава MS SQL Server).
2. Подключиться к серверу под учетной записью администратора или владельца БД (можно использовать встроенную учетную запись «sa», пароль для которой задавался при установке SQL Server, либо выбрать вариант «Проверка подлинности Windows» в случае если текущий пользователь сеанса Windows обладает правами администратора в SQL Server, а также использовать любую другую учетную запись SQL Server или Windows, которая включена в роль «db_owner» восстанавливаемой базы при наличии таковой или серверную роль «dbcreator»):
3. Нажать правой кнопкой мыши на разделе «Базы данных» и выбрать меню «Восстановить базу данных»:
4. На странице «Общие» выполнить следующие действия:
  - В поле «В базу данных» ввести имя для восстанавливаемой базы (если будет указано имя существующей базы, то это эквивалентно тому, что сначала полностью удалить существующую базу и затем восстановить из резервной копии новую базу, т.е. все данные существующей базы будут утеряны!);
  - Установить переключатель «С устройства» и указать путь к файлу резервной копии, нажав кнопку «…»;
  - Установить галочку «Восстановить» в нужной строке (которых может быть несколько, если один файл *.bak содержит несколько резервных копий базы):
5. На странице «Параметры» установить галочку «Перезаписать существующую базу данных» и проверить пути в списке «Восстановить файлы базы данных как» (должны указывать на существующую папку на SQL-сервере, к которой предоставлены права на запись – пути по умолчанию обычно должны заканчиваться папкой DATA, а не просто MSSQL):
6. Нажать кнопку «ОК».
Примечание. После восстановления базы данных на другой версии SQL Server (например, при переносе с SQL2005 на SQL2008) рекомендуется в свойствах базы данных переключить параметр «Уровень совместимости» на последнюю версию (см. страницу «Параметры» в свойствах БД).

Кроме того, после переноса базы на любой новый SQL-сервер придется заново настроить авторизацию и регулярное резервное копирование в соответствии с инструкцией по установке сетевой версии соответствующей программы.

Для переноса большого количества «имен входа» (логинов) с их паролями на новый сервер можно воспользоваться способом, описанным в статье базы знаний Microsoft. Однако, следует иметь в виду, что данный способ не переносит серверные права логинов (принадлежность «серверным ролям», право на «просмотр состояния сервера» и т.п.), а также не подходит для переноса с SQL 2012 на более ранние версии SQL Server.

Резервное копирование MS SQL Server

Обновлено: 23.12.2019 Опубликовано: 22.06.2017

Есть несколько способов создания резервной копии MS SQL. Для разовых операций прекрасно подойдет графический инструмент SQL Management Studio. Для автоматизации — Powershell или cmd. Данные операции применяются к любым базам, как для 1С, так и любых других приложений.

С помощью графического интерфейса

Открываем MS SQL Management Studio. Кликаем правой кнопкой мыши по базе, для которой хотим сделать резервную копию — Задачи — Создать резервную копию:

В открывшемся окне оставляем полный тип копий и путь к резервному файлу (при необходимости, можно его поменять, удалив и создав снова. Можно указать как локальный диск, так и сетевой):

После завершения процесса мы увидим сообщение «Резервное копирование базы . успешно завершено».

С помощью командной строки (cmd)

Данный способ удобно использовать для автоматизации резервного копирования. Более того, команды подходят как для Windows, так и Linux. Выполняется при помощи утилиты sqlcmd.

Пример готового скрипта

@echo off
set dd=%DATE:~0,2%
set mm=%DATE:~3,2%
set yyyy=%DATE:~6,4%
set curdate=%dd%-%mm%-%yyyy%
set username=sa
set password=my_pass

set db=work1
sqlcmd -S localhost -U %username% -P %password% -Q «BACKUP DATABASE [%db%] TO DISK = N’D:\Backup\MSSQL\%db%_%curdate%.bak’ WITH NOFORMAT, NOINIT, NAME = N’%db%-full’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10»

set db=work2
sqlcmd -S localhost -U %username% -P %password% -Q «BACKUP DATABASE [%db%] TO DISK = N’D:\Backup\MSSQL\%db%_%curdate%.bak’ WITH NOFORMAT, NOINIT, NAME = N’%db%-full’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10»

* в данном примере мы подключаемся к локальному SQL серверу под учетной записью sa с паролем my_pass и делаем резервную копию баз work1 и work2. Резервные копии размещаем по пути D:\Backup\MSSQL. Имя файлов резервных копий work1_.bak и work2_.bak
* некоторые опции могут не работать, в зависимости от используемой редакции MS SQL.

Для автоматизации скрипта, создайте задание в планировщике, чтобы скрипт запускался по расписанию.

Типы резервных копий

Хорошей практикой является создание разных типов копий:

1) Полное копирование — резервирование всей базы. Выполняется командой, рассмотренной выше, например:

sqlcmd -S localhost -U sa -P my_pass -Q «BACKUP DATABASE work1 TO DISK = N’D:\Backup\MSSQL\bak_full.bak’ WITH NOFORMAT, NOINIT, NAME = N’bak-full’, SKIP, NOREWIND, NOUNLOAD, STATS = 10»

* в данном примере мы подключаемся к локальному серверу под пользователем sa с паролем my_pass и делаем полную копию базы work1; саму копию сохраняем в виде файла D:\Backup\MSSQL\bak_full.bak.

2) Разностное (дифференциальное) — резервирование базы данных с момента создания последней полной копии. Выполняется командой для резервного копирования с добавлением опции DIFFERENTIAL:

sqlcmd -S localhost -U sa -P my_pass -Q «BACKUP DATABASE work1 TO DISK = N’D:\Backup\MSSQL\bak_diff.bak’ WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N’bak-diff’, SKIP, NOREWIND, NOUNLOAD, STATS = 10»

3) Инкрементальное или копирование логов. Выполняется Transact-SQL:

sqlcmd -S localhost -U sa -P my_pass -Q «BACKUP LOG work1 TO DISK = N’D:\Backup\MSSQL\bak_log.bak’ WITH NOFORMAT, NOINIT, NAME = N’bak-log’, SKIP, NOREWIND, NOUNLOAD, STATS = 10»

* обратите внимение, команда похожа на команду для полного резервного копирования — вместо DATABASE пишем LOG.

С помощью Powershell

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

Для выполнения команды, сначала импортируем модуль:

import-module sqlps -DisableNameChecking

Backup-SqlDatabase -ServerInstance -Database -BackupFile

Пример скрипта на powershell

$server = «SQL01»
$curdate = Get-Date -Format yyyyMMdd

import-module sqlps -DisableNameChecking

$db = work1
Backup-SqlDatabase -ServerInstance $server -Database $db -BackupFile $db_$curdate.bak

* где выполняется резервное копирования базы work1 на сервере SQL01

Также как и для cmd, данный скрипт можно поместить в планировщик для запуска по расписанию.

Срок действия резервного набора данных

Данная настройка позволяет указать, через какой промежуток времени резервную копию можно удалить (перезаписать). Важно понимать, что настройка не влияет на сам период восстановления — если срок истек, восстановиться из набора можно.

Задать параметр можно в основном окне при создании резервной копии:

Путь расположения резервных копий

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

Кликаем правой кнопкой мыши по корневому разделу SQL Server и выбираем свойства:

Переходим в раздел Параметры баз данных (1) — в подразделе «Места хранения, используемые базой данных по умолчанию» мы увидим путь до места размещения резервных копий (2), который можно поменять кнопкой справа (3):
Похожие публикации: