Резервное копирование баз данных
Резервное копирование баз данных можно производить несколькими способами:
Резервное копирование баз данных в SQL Server Management Studio
Для создания резервной копии базы данных необходимо выделить ее в дереве объектов SQL Server Management Studio 2) , в контекстном меню выбрать пункт "Задачи → Создать резервную копию…".
В окне "Резервное копирование базы данных" (Рис. 1) на странице Общие в разделе "Источник" в поле "Тип резервной копии выбрать Полная; в разделе "Назначение" по кнопке "Добавить" указать файл резервной копии базы данных. Для проверки целостности копии базы данных на закладке Параметры в разделе "Надежность" установить флаг Проверить резервную копию после завершения.
Нажатием кнопки "OK" запустить создание резервной копии выбранной базы данных и дождаться сообщения "Резервное копирование базы данных "<имя_базы>" успешно завершено.".
Автоматическое резервное копирование
Настройка автоматического резервного копирования баз данных возможна разными способами. Здесь приведена схема работы скрипта, создающего резервные копии указанных баз данных в указанные папки.
4 Ways to Backup Database in SQL Server Management Studio
In this article, we introduce 4 common backup methods in SQL Server Management Studio, and a third party backup software as alternative.
By Delia 
/ Last update October 18, 2022
SQL Server Management Studio Backup Database Guide
As you probably know, there are multiple backup methods in Microsoft SSMS (SQL Server Management Studio), each with its own advantages and disadvantages. So before you start, I’d like to briefly introduce and compare them for your reference:
◉ Backup GUI: This feature is easy to operate without using scripts, but only one database can be backed up at a time, and the operation cannot be automated.
◉ T-SQL script: You need to know basic T-SQL commands beforehand, and any small mistake may lead to operation failure. But with effective scripts, you can perform some more advanced operations easily.
◉ Maintenance Plans: It allows you to backup multiple databases at once, and add a schedule to automate the task. However, it is only available for non-Express editions and is relatively cumbersome to operate.
◉ SQL Server Agent: It can also automate backups with high flexibility, but you need to fill in the backup script yourself as the core step. Also, it is not applicable to SQL Express editions either.
You can pick the way you like, or use another SQL backup software that can schedule backup multiple databases simply and intuitively.
Method 1. Create a SQL backup task with GUI
To use Microsoft SQL Server Management Studio backup database, the easiest way is using the backup GUI.
1. Launch SSMS and connect to your instance. Right-click the database you want to back up in Object Explorer, choose Tasks > Back Up.
2. Choose the backup type (full/differential/transaction log), and Add a path as you need.
It’s recommended specify a easily accessible path so that the backup file can be easily found when you need to perform recovery. Then, e nter the file name as you like and remember to add the .bak suffix.
3. Click OK to execute the backup. It will prompt you a message once the operation is complete. Then you can find the backup file in the target folder.
Tip: In SQL management stutio, you can also restore SQL database from bak file. Click to learn more details.
Method 2. Backup database with T-SQL commands
The basic T-SQL backup statements is BACKUP DATABASE TO DISK. You can use it flexibly as you need.
Create a full backup:
USE master
GO
BACKUP DATABASE [database] TO DISK = ‘filepath\fileaname.bak’ WITH COMPRESSION, INIT, STATS = 5;
GO
Create a d ifferential backup:
USE master
GO
BACKUP DATABASE [database] TO DISK = ‘filepath\filename.bak’ WITH DIFFERENTIAL
GO
Create a transaction log backup:
USE master
GO
BACKUP LOG [database] TO DISK =’ filepath\filename.trn ‘ WITH NORECOVERY
GO
Method 3. Schedule backup with Maintenance Plans
This feature gives you options to create a plan manually, or complete the operation following a wizard. Here I will mainly introduce the first one.
1. Make sure the SQL Server Agent is enabled, otherwise please Start it first.
2. Navigate to Management > Maintenance Plans. Right-click it and choose New Maintenance Plan. Give it a name to proceed.
3. Click Subplan Schedule (calendar icon) to set up the backup schedule. Keep the schedule type as the default Recurring, and configure the frequency as you like. Click OK to confirm it.
4. Hit Toolbox and drag the Back Up Database Task into the blank area on the right side.
5. Double-click the task to choose the backup type, and the databases to backup. Then specify a target folder.
6. After saving the plan, you can find it under SQL Server Agent > Jobs. It will be executed automatically according to your schedule. You can also right-click it and choose Start Job at Step to test it immediately.
Tip: The procedure of creating a backup task with Maintenance Plan Wizard is largely the same. But it will walk you through all the steps, so it’s more suitable for beginners.
Method 4. Create an auto backup job with SQL Server Agent
This method essentially uses T-SQL commands to perform database backup, but you can add a schedule to automate the task.
1. Right-click SQL Server Agent and choose New > Job.
2. Enter a name and description for the job. Then move to Steps tab.
3. Click New to add a step name and any T-SQL backup commands as you need. Here’s an example ：
DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),120)
set @strPath = REPLACE(@strPath, ‘:’ , ‘.’)
set @strPath = ‘filepath’ + @strPath + ‘.bak’
BACKUP DATABASE [database] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
4. Move to Schedules tab and click New to set up the schedule type and frequency. Click OK to finish the setup.
5. Then you can find the created backup job under SQL Server Agent > Jobs, and choose Start job at Step to test it.
Easier way to backup multiple SQL databases with schedule
If you find the above 4 backup methods in SQL Server Management Studio complicated or not effective enough, you can also try AOMEI Centralized Backupper Database to backup MSSQL.
It supports full or differential SQL backup, and allows you to select multiple databases or even all databases at once. Also, you can set up a schedule to automate the operation easily.
Download the 30-day free trial to proceed:
Install Centralized Backupper Database on the central machine, and client packages on all network connectetd PCs, workstations and servers that you want to protect. Then you can Request Control over these client computers and manage their backup and restore tasks centrally.
Backup SQL databases with centralized backup software:
1. Navigate to Tasks > New Task > SQL Server Backup.
2. Click + Add Computers to detect the client computers with SQL database, select any of them from the result list.
3. Click + Add to detect instances on the specified computer. You can select multiple databases or the entire instance at once.
4. Click on Step 3 > Add Storage to specify a target network share or NAS. Then you can click Start Backup to save and execute the task.
☛ To automate the backup, just click Schedule to configure the frequency and backup type (full or differential).
☛ To restore from the backup, click Advanced at the top right corner of the task, and choose Restore to proceed. It allows you to restore one or multiple databases to any earlier date.
Conclusion
The purpose of this article is to introduce the steps to use SQL Server Management Studio backup database, and a more convenient alternative — AOMEI Centralized Backupper Database Edition.
It not only provides a concise graphic interface, but also simplifies the process of selecting databases and creating schedules. As a centralized managment tool, it even enables you to restore SQL databases to another server directly.
SQL-Ex blog
Примеры резервного копирования базы данных SQL Server
Если вы новичок в SQL Server и хотите узнать, как создать бэкап базы данных SQL Server, а также о том, какие различные типы резервных копий существуют, то эта статья для вас. Здесь обсуждается создание резервных копий с помощью GUI и скриптов T-SQL.
Среди различных функций, которые выполняет администратор баз данных SQL Server, создание резервных копий пользовательских баз данных является наиболее важной. Без резервных копий невозможно восстановление. Важно понимать разницу между типами резервных копий.
Мы сделаем обзор трех основных типов резервных копий (полная, дифференциальная и журнала транзакций), что они из себя представляют, когда и как их использовать.
Типы резервных копий SQL Server
- Полный бэкап базы данных SQL Server
- Как говорит название, это полный бэкап всей базы данных
- Модель восстановления базы данных может быть полной, с неполным протоколированием, или простая
- Основа для восстановления дифференциальных бэкапов и бэкапов журнала транзакций
- Используется для восстановления базы данных на момент завершения резервного копирования
- Резервная копия данных, которые изменились, начиная с последнего успешного бэкапа
- Модель восстановления базы данных может быть только полной или с неполным протоколированием
- После восстановления из полной резервной копии для восстановления базы данных на момент времени, когда была сделана дифференциальная резервная копия.
- Может сделать более быстрое восстановление бэкапов журнала транзакций
- Резервное копирование журнала транзакций (последовательная запись изменений базы данных) от последнего успешного полного бэкапа или бэкапа журнала транзакций (инкрементального бэкапа)
- Модель восстановления базы данных может быть только полной или с неполным протоколированием
- Восстанавливаются в том порядке, в котором были созданы, после полного (и, возможно, дифференциального резервного копирования) для восстановления на момент времени после резервного копирования журнала транзакций
Полная резервная копия базы данных SQL Server SQL Server Management Studio (SSMS)
Откройте браузер объектов в SSMS.
1. Разверните дерево SQL Server
2. Разверните Databases
3. Выполните щелчок правой кнопкой на базе данных, для которой требуется создать резервную копию
4. Выберите Tasks
5. Back Up…
1. Выберите ‘Full’ в выпадающем списке ‘Backup type’
2. Выберите ‘Disk’ в выпадающем списке ‘Backup to’
3. Add.
1. Оставьте каталог размещения по умолчанию, или измените его по желанию
2. Кнопка …
1. Дайте имя файлу (расширение .bak указывает, что это полная резервная копия)
2. Щелкните ОК
1. Выберите страницу ‘Media Options’
2. Установите опцию ‘Overwrite all existing backup sets’ (переписать все существующие наборы резервных копий)
3. Поставьте флажок ‘Perform checksum before writing to media’ (выполнить расчет контрольной суммы перед записью на носитель)
4. Выберите страницу ‘Backup Options’
1. Дайте имя бэкапу
2. Выберите ‘Compress backup’ (сжатый бэкап) из выпадающего списка ‘Set backup compression’
Полная резервная копия — непосредственно выполняемый код SQL
Мы можем осуществить то же самое, просто выполнив следующий код SQL.
Дифференциальная резервная копия — SSMS
Создание дифференциального бэкапа подобно созданию полной резервной копии.
1. Вместо значения по умолчанию Full, выберите ‘Differential’ в выпадающем списке ‘Backup type’
2. Add.
Остальные шаги фактически те же самые.
1. Оставьте местоположение каталога по умолчанию или измените его
2. .
1. Дайте имя файлу (расширение .dif указывает на дифференциальный бэкап)
2. Щелкните ОК
1. Выберите страницу ‘Media Options’
2. Выберите опцию ‘Overwrite all existing backup sets’ (переписать все существующие резервные наборы)
3. Установите флажок ‘Perform checksum before writing to media’ (выполнить расчет контрольной суммы до записи на носитель)
4. Выберите страницу ‘Backup Options’
1. Дайте имя бэкапу
2. Выберите ‘Compress backup’ (сжатый бэкап) в выпадающем списке ‘Set backup compression’
3. Щелкните ОК
Дифференциальная резервная копия — непосредственное выполнение кода SQL
Как вы можете заметить здесь, SQL почти тот же, что и для полной резервной копии, за исключением имен и дополнительной опции ‘DIFFERENTIAL’.
Резервная копия журнала транзакций — SSMS
Создание бэкапа журнала транзакций подобно созданию полной или дифференциальной резервной копии.
1. Вместо значения по умолчанию Full, теперь выберите ‘Transaction Log’ в выпадающем списке ‘Backup type’
2. Add.
1. Оставьте местоположение каталога по умолчанию или измените его
2. Кнопка .
1. Дайте имя файлу (расширение .trn указывает на бэкап журнала транзакций)
2. Щелкните ОК
1. Выберите страницу ‘Media Options’
2. Выберите опцию ‘Overwrite all existing backup sets’ (перезаписать все существующие резервные наборы)
3. Установите флажок ‘Perform checksum before writing to media’ (выполнить расчет контрольной суммы до записи на носитель)
4. Выберите страницу ‘Backup Options’
1. Дайте имя бэкапу
2. Выберите ‘Compress backup’ (сжатый бэкап) в выпадающем списке ‘Set backup compression’
3. Щелкните ОК
Резервная копия журнала транзакций — непосредственное выполнение кода SQL
Помимо имен, разница состоит в том, что резервная копия журнала транзакций выполняется с ‘BACKUP LOG’, а не ‘BACKUP DATABASE’.
Заметки сисадмина о интересных вещах из мира IT, инструкции и рецензии. Настраиваем Компьютеры/Сервера/1С/SIP-телефонию в Москве
Инструкция по созданию/восстановлению резервных копий баз данных
Создание резервной копии базы данных (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.