Как сделать bak файл ms sql server

Краткое руководство. Резервное копирование и восстановление базы данных SQL Server с помощью SSMS

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

Предварительные условия

Для работы с этим кратким руководством вам понадобится:

SQL Server
SQL Server Management Studio (SSMS)

Создание тестовой базы данных

Запустите среду SQL Server Management Studio (SSMS) и подключитесь к своему экземпляру SQL Server.
Откройте окно Новый запрос.
Выполните следующий код Transact-SQL (T-SQL) для создания тестовой базы данных.

USE [master]; GO CREATE DATABASE [SQLTestDB]; GO USE [SQLTestDB]; GO CREATE TABLE SQLTest ( ID INT NOT NULL PRIMARY KEY, c1 VARCHAR(100) NOT NULL, dt1 DATETIME NOT NULL DEFAULT GETDATE() ); GO USE [SQLTestDB] GO INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1'); INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2'); INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3'); INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4'); INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5'); GO SELECT * FROM SQLTest; GO

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

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

SSMS
T-SQL

Запустите среду SQL Server Management Studio (SSMS) и подключитесь к своему экземпляру SQL Server.
В обозревателе объектов разверните узел Базы данных.
Щелкните правой кнопкой мыши базу данных, наведите указатель мыши на Задачии выберите Резервное копирование. .
В разделе Назначение проверьте правильность пути к резервной копии. Если вам нужно изменить его, выберите Удалить для удаления существующего пути, а затем Добавить, чтобы ввести новый путь. Можно использовать кнопку с многоточием для перехода к определенному файлу.
Чтобы создать резервную копию базы данных, нажмите ОК.

Screenshot of SQL Server Management Studio take backup.

Кроме того, чтобы создать резервную копию базы данных, можно выполнить следующую команду Transact-SQL. Путь может отличаться на вашем компьютере:

USE [master]; GO BACKUP DATABASE [SQLTestDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQLTestDB.bak' WITH NOFORMAT, NOINIT, NAME = N'SQLTestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10; GO

Дополнительные сведения о различных вариантах резервного копирования см. в этой статье.

Восстановление резервной копии

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

Запустите среду SQL Server Management Studio (SSMS) и подключитесь к своему экземпляру SQL Server.
Щелкните правой кнопкой мыши узел Базы данных в обозревателе объектов и выберите Восстановить базу данных. .
Выберите Устройство: и нажмите кнопку с многоточием (. ), чтобы найти файл резервной копии.
Выберите Добавить и перейдите в расположение вашего файла .bak . Выберите файл .bak и затем нажмите ОК.
Снова нажмите ОК в диалоговом окне Выбор устройств резервного копирования, чтобы закрыть его.
Чтобы восстановить резервную копию базы данных, нажмите ОК.

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

USE [master]; GO RESTORE DATABASE [SQLTestDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQLTestDB.bak' WITH FILE = 1, NOUNLOAD, STATS = 5; GO

Очистка ресурсов

Выполните следующую команду Transact-SQL, чтобы удалить созданную базу данных вместе с журналом резервного копирования в базе данных msdb :

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SQLTestDB' GO USE [master]; GO DROP DATABASE [SQLTestDB]; GO

См. также

Восстановление баз данных SQL Server на виртуальной машине Azure — Azure Backup
Краткое руководство по восстановлению резервной копии (SSMS) — Управляемый экземпляр SQL Azure
Восстановление базы данных из резервной копии — Управляемый экземпляр Базы данных & SQL Azure

Обзор резервного копирования и восстановления
Резервное копирование по URL-адресу
Создание полной резервной копии
Восстановление резервной копии базы данных

SQL-Ex blog

5 способов сделать резервные копии в SQL Server

Добавил Sergey Moiseenko on Суббота, 3 июля. 2021

В прошлый раз мы обсуждали 5 типов резервных копий. Сейчас я хочу представить вам пять способов сделать бэкап в SQL Server. Я не смогу продемонстрировать все доступные опции каждого из этих шести методов. Здесь много чего есть даже для такой простой темы как бэкапы.

Метод 1: Использование графического интерфейса в SSMS для создания бэкапа

Для тех, кому нравится SSMS GUI, есть хорошие новости. SQL Server Management Studio предоставляет довольно простой метод для резервирования базы данных. Подключившись к экземпляру SQL Server, содержащего базу данных, для которой вы хотите сделать бэкап, выполните щелчок левой кнопкой на значке «+» рядом с папкой Databases. Затем щелчок правой кнопкой на имени базы данных, которую вы хотите резервировать, выбрать Tasks, затем выбрать Backup в выпадающем меню.

Вы попадете на страницу General Backup Menu page в SSMS. Здесь вы можете получить доступ к множеству настроек, относящихся к создаваемому бэкапу.

В выпадающем списке “Backup type” вы можете выбрать тип создаваемого бэкапа — полный, дифференциальный или журнала.

В разделе “Backup component” можно уточнить, какой бэкап будет делаться — файлов и файловых групп или базы данных (по умолчанию).

В разделе Destination (назначение) вы выбираете, где будет создан бэкап — диск (по умолчанию) или, если выбрать из списка “URL”, то на Azure. При выборе Disk вам предлагается место и имя для бэкапа. Этим местом будет каталог по умолчанию для бэкапов, указанный при установке SQL Server. Если вас не устраивает это место, просто нажмите “Remove” (удалить), а потом “Add” (добавить) для выбора места, которое вы хотите использовать. В меню “Add” можно использовать общие пути.

Раздел Media Options на “Select a Page” позволяет выбрать такие варианты, как хотите ли вы добавить этот бэкап к имеющемуся набору или начать заново.

Мне кажется, что эти опции среды перешли из прошлого, когда бэкапы записывались на физические ленты. Эти ленты тогда должны были перематываться время от времени. Не лучший способ добавлять бэкапы в набор, поскольку все файлы бэкапов добавляются в единый набор. Если что-то случится с этим набором, и он станет непригодным для использования, то и все бэкапы станут недоступными.

В разделе Reliability (надежность) вы можете установить опции “Verify backup when finished” (проверить бэкап по завершению) и “Perform checksum before writing to media.” (посчитать контрольную сумму перед записью на носитель). Эти опции увеличат время создания бэкапа, но помогут с проверкой его целостности по время записи.

В Backup Options меню “Select a page” есть одна очень важная особенность, которую следует отметить.

Здесь имеется опция, связанная со сжатием бэкапа. В более старых версиях SQL Server, например, 2005 и 2008 эта опция была доступна только для Enterprise Edition. Начиная с SQL Server 2008R2, она доступна в Standard Edition. Чтобы сделать использование сжатия по умолчанию для всех ваших бэкапов, просто выполните нижеприведенный код на вашем SQL Server. Затем, когда вы перейдете к этой опции в графическом интерфейсе SSMS, просто оставьте её установленной в “Use the default server setting.” Вам захочется сэкономить пространство, которое предлагает сжатие. Зачем использовать больше пространства на вашем отдельном хранилище бэкапов, чем это необходимо? Я имею в виду, что вы храните свои резервные копии где-то еще, а не на SQL Server, верно?!

EXEC sys.sp_configure N'backup compression default', N'1' 
GO 
RECONFIGURE WITH OVERRIDE 
GO

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

Метод 2: Использование T-SQL для создания резервной копии на SQL Server

T-SQL — проверенный и надежный метод резервного копирования баз данных. При использовании T-SQL доступно больше опций для создания бэкапов, чем при использовании графического интерфейса. Большинство этих опций являются более продвинутыми. Очень базовый пример команды backup, которая создает полную резервную копию, представлен ниже. Затем следуют примеры дифференциального бэкапа и бэкапа журнала.

BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Full.bak' 
BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Differntial.bak' WITH DIFFERENTIAL 
BACKUP LOG [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_log.trn'

Стоит отметить два параметра Buffer Count и maxtransfersize. Вы можете поэкспериментировать с этими параметрами T-SQL, чтобы ускорить создание бэкапов. Значение Buffer Count управляет числом буферов ввода/вывода, которые используются для обработки бэкапа, а maxtransfersize отвечает за то, сколько данных перемещается за один раз.

Ниже я предоставил 3 примера моих тестов, выполненных на домашнем ПК. Исходные данные buffercount и maxtransfersize были получены с помощью установки флагов 3605 и 3213 с последующим обращением к журналу ошибок после выполнения первого бэкапа. После чего я просто экспериментировал со значениями. Имейте в виду, что слишком сильное увеличение числа буферов может вызвать ошибку нехватку памяти.

Как вы можете видеть начальная пропускная способность составляла 219,412Мб/с, а прошедшее время для этой части было 39 секунд. Это были настройки по умолчанию SQL Server.

Увеличение числа буферов до 8 увеличило пропускную способность до 258,653Мб/с, и время выполнения упало примерно на 6 секунд. Сочетание второго изменения с размером maxtransfersize 4Мб увеличило пропускную способность до 270,095 и еще сократило время на 1,4 секунды. Я скинул 8 секунд времени бэкапа. Это была небольшая база данных размером около 14Гб. Для бОльших баз данных увеличение пропускной способности может дать значительную экономию времени.

BACKUP DATABASE [StackOverflow2010] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT, NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM 
GO 
/* 
buffercount = 4 and maxtransfersize = 1024kb 
нет опций 
Обработано 1096456 страниц базы данных 'StackOverflow2010', файл 'StackOverflow2010' в файле 1. 
100 процентов обработано. 
Обработано 2 страницы для файла 'StackOverflow2010', файл 'StackOverflow2010_log' в файле 1. 
Бэкап базы данных успешно обработан 1096458 страниц за 39.041 секунд (219.412 Мб/с). 
Резервный набор для файла 1 валидный. 
*/ 
BACKUP DATABASE [StackOverflow2010] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT, NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM, BUFFERCOUNT = 8 
/* 
buffercount = 8 
Обработано 1096456 страниц базы данных 'StackOverflow2010', файл 'StackOverflow2010' в файле 1. 
100 процентов обработано. 
Обработано 2 страницы для файла 'StackOverflow2010', файл 'StackOverflow2010_log' в файле 1. 
Бэкап базы данных успешно обработан 1096458 страниц за 33,118 секунд (258,653 Мб/с). 
Резервный набор для файла 1 валидный. 
*/ 
BACKUP DATABASE [StackOverflow2010] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT, NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM, BUFFERCOUNT = 8, maxtransfersize = 4194304 
/* 
buffercount = 8 и maxtransfersize = 4Мб 
Обработано 1096456 страниц базы данных 'StackOverflow2010', файл 'StackOverflow2010' в файле 1. 
100 процентов обработано. 
Обработано 2 страницы для файла 'StackOverflow2010', файл 'StackOverflow2010_log' в файле 1. 
Бэкап базы данных успешно обработан 1096458 страниц за 31,715 секунд (270,095 Мб/с). 
Резервный набор для файла 1 валидный. 
*/

Метод 3: Использование Powershell для создания резервных копий

Если вы не используете Powershell с SQL Server, то это того стоит. Если вы не используете модуль DBATools с SQL Server, получите его сейчас. PowerShell может делать фантастические, чудесные вещи, а DBATools может сделать для вас мощные, удивительные вещи во всем, что связано с SQL Server. Ниже простой пример использования команды DBATools Backup-DbaDatabase для создания полного бэкапа. Эта команда имеет полный набор опций, включая резервирование всех баз данных на SQL Server, если не передавать параметр -Database. Проверьте это прямо сейчас.

Backup-DbaDatabase -SqlInstance MySQLServer -Database Stackoverflow2010 -path 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup'

Метод 4: Использование планов обслуживания для создания резервных копий

Тут я лишь поделюсь с вами несколькими мыслями относительно использования планов обслуживания. Во-первых, планы обслуживания (Maintenance Plans) представляют собой еще один метод с графическим интерфейсом для настройки резервных копий. В этом отношении они простой способ «указать и щелкнуть» для обработки хранения резервных копий, о чем мы еще не говорили. Во-вторых, в силу природы этого метода, который позволяет вам выбрать Backups как вариант плана, а затем пройти по шагам каждую часть мастера процесса, Maintenance Plans может стать общим подходом для ИТ-профессионалов, вышедших из системных администраторов. Например, нет необходимости знать или понимать опции, представленные в мастере SSMS Backup.

Расчет контрольной суммы.
Продолжение при ошибке.
Размер блока.
Максимальный размер передачи.

Создание/Востановление резервной копии базы данных MS SQL Server (backup) через Management Studio в ручном режиме

ВАЖНО! Установите автоматическое копирование базы данных, подробно здесь.

Создание резервной копии вручную

Запустить “SQL Server Management Studio”

Выбирать в списке базу данных, резеврную копию которой нужно сделать. Нажать правой кнопкой мышки : “Tasks”-“Back Up…” (“Задачи”-“Резервная копия…”)

В открывшемся окне удалить список файлов:

Когда список пуст — нажать “Add” (“Добавить”)

Выбрать папку куда сохранять резервную копию, и прописать имя файла вместе с расширением “.bak”

ПОЗДРАВЛЯЮ! Резеврная копия создана! Сохрание файл на флешку или облачный диск.

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

Методов востановления есть несколько. Опишем универсальный.

Запустить “SQL Server Management Studio”.

Создать новую пустую базу данных в которую развернем резервную копию, например, создаем базу “bazaR21”. Правой кнопкой мыши “Databases”-“New Database” (“Базы данных”-“Новая база данных”):

Пишем имя базы : “bazaR21” и нажимем “ОК” :

Правой кнопкой на новой базе “Tasks”-“Restore”-“Database…” (“Задачи”-“Востановить”-“База данных…”)

Выбираем файл з резервной копией:

Посте этого, в разделе “Destination” может измениться имя базы. Выбираем из списка новую созданную:

Если SQL Server старая версия, может не быть возможности выбрать новую базу в разделе “Destination”, то на закладке “Files” нужно выбрать новые файлы вручную:

На закладке “Options” поставить галочку “Overwite the existing database” и нажать “ОК”

ГОТОВО!

ВАЖНО! Установите автоматическое копирование базы данных, подробно здесь.

Восстановлений базы данных из bak файла бэкапа Microsoft SQL Server

Покажем как это сделать на примере СУБД Microsoft SQL Server 2014.

Подсоеденитесь к SQL серверу посредством microsoft sql server management studio и в дереве в контекстном меню пункта Databases выберите пункт «Restore Database. «:

В параметрах выберем «Device» и перейдем по кнопке «Обзор«

В открывшемся окне нажимаем «Add» и находим файл бэкапа базы данных:

Перед началом рекомендуется проверить корректность файла, нажав кнопку «Verify Backup Media«:

Далее перейдем в левом меню на раздел «Files» и проверим пути для сохранения восстановленной базы данных. При необходимости меняем их (не обязательно):

Также можно перейти в раздел «Options» и сделать настройки, если требуется (не обязательно):

Нажимаем ОК и дожидаемся сообщения об успешном восстановлении базы: