Бекап средствами MS SQL во внешний источник по датам

Бекап средствами MS SQL (Transact-SQL) всех баз во внешний источник по папочкам и датам

Поставили задачу, сделать скрипт для бекапа, чтобы все базы разкладывал по папочкам и папочки были даты.

Есть очень много вариантов решений такой задачи. Но безопасники ограничили выбор чисто на MS SQL.

Пошарив в интернете и написал такой код, может, не идеальный, но рабочий. Открыт для поправок и критики =)

USE Master;
GO
SET NOCOUNT ON

DECLARE @DBName nvarchar(500)
DECLARE @DBName_folder nvarchar(500)
DECLARE @DBDate_folder nvarchar(500)
DECLARE @Server nvarchar(500)
DECLARE @Server_folder nvarchar(500)
DECLARE @DataPath_backUp nvarchar(500)
DECLARE @LogPath nvarchar(500)
DECLARE @DBName_file nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
DECLARE @DirTree_date TABLE (subdirectory nvarchar(255), depth INT)



--Путь к внешнему источнику(хранилишю)
SET @DataPath_backUp = '\ServerBackup1c-backups'

--Получаем дерево всех папок
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath_backUp


--Делаем имя папки текущую дату
set @DataPath_backUp  = @DataPath_backUp + '' + Convert(varchar(8), GETDATE(), 112)

--Проверяем есть ли такая папка, если нет то создаем
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = Convert(varchar(8), GETDATE(), 112))
EXEC master.dbo.xp_create_subdir @DataPath_backUp

--Делаем дерево всех папок вложеных в папку день(это уже базы)
INSERT INTO @DirTree_date(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath_backUp


--Делаем запрос в SQL всех баз для бекапа, я делал запрос по модели восстановления - полная
DECLARE @CURSOR CURSOR

SET @CURSOR  = CURSOR SCROLL
for (SELECT name FROM sys.databases
where service_broker_guid <> '00000000-0000-0000-0000-000000000000' and recovery_model_desc = 'FULL')

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
--перебераем базы и создаем папки

set @DBName_folder =  @DataPath_backUp + ''+ @DBName

IF NOT EXISTS (SELECT 1 FROM @DirTree_date WHERE subdirectory = @DBName)
begin

EXEC master.dbo.xp_create_subdir @DBName_folder
end

set @DBName_file =  @DBName_folder +''+ @DBName + '_' + Convert(varchar(8), GETDATE(), 112)+ '.bak'
--код самого бекапа
BACKUP DATABASE @DBName TO  DISK = @DBName_file WITH NOFORMAT, NOINIT,  NAME = @DBName, COMPRESSION, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
--
FETCH NEXT FROM @CURSOR INTO @DBName
END

-- очищаем данные
DELETE FROM @DirTree
DELETE FROM @DirTree_date

10 Comments

sansys 08.02.2017 at 12:06

Хороший скрипт, ещё-бы список баз прикрутить, а то есть тестовые которые сохранять нет необходимости.

Reply ↓
sssss_aaaaa_2011 08.02.2017 at 12:07

(0)Бэкап сразу на сетевой путь есть большое зло. Лучше на локальный диск и потом уже с него в сеть.

Reply ↓
sssss_aaaaa_2011 08.02.2017 at 12:13

(1)Не очень хороший.

1. Использует сетевые пути. Кроме описанного в моем первом сообщении еще и права надо sql дать на сервере с бэкапами.

2. Курсор, туча переменных и чисто императивный подход в декларативном языке. Можно обойтись 1(одним!) select’ом для генерации всего одной строки со всеми командами, которые таки вызываются через курсор. После чего exec (@cmd) и вуа-ля!

Reply ↓
sansys 08.02.2017 at 12:16

(3) Пример в студию! Будет интересно посмотреть.

Reply ↓
sssss_aaaaa_2011 08.02.2017 at 12:36
(4) Прямо сейчас под рукой нет, но вот есть пример для понимания самой идеи.

Задача: получить список всех таблиц с количеством записей в каждой.

Исходно задача решалась, как и обсуждаемая здесь, так же в лоб, с курсором и примерно таким же количеством кода.

Однако, нашелся и такой вариант:
```
declare @str varchar(max);
cre ate   table #t (T_Name sysname, cnt int);
sel ect
@str = replace(
(
sel ect ‘ins ert into #t select »’+TABLE_NAME+»’,count(*) fr om ‘+TABLE_NAME+char(160) as ‘data()’
fr om INFORMATION_SCHEMA.TABLES wh ere TABLE_TYPE = ‘BASE TABLE’
for xml path(»)
)
, char(160), ‘;’)
exec (@str)
sele ct * fr om #t
dr op   table #t
```
Показать
Reply ↓
ILYXA 08.02.2017 at 12:40

(1) Из-за я выделил уровень восстановление, у рабочих FULL, у тестовых Simple

Reply ↓
ILYXA 08.02.2017 at 12:42

(3)

1. Да, службу SQL я запускаю с служебного пользователя с доступами. Так стояла задача.

2. Есть чему учиться =)

Reply ↓
sssss_aaaaa_2011 08.02.2017 at 12:58

(7) Ну так надо админам объяснить, что сие решение не самое хорошее ибо передача по сети может не успевать за выполнением бэкапа и тормозить процесс, к тому же и по надежности сильно не соответствует важности операции. Плюс еще пляски с юзерами и правами. И вместо этого давно придуман вариант с отдельным локальным диском, с которого уже сами админы могут забирать бэкап любыми понравившимися им способами.

Reply ↓
Saipl 08.02.2017 at 14:48

Реализовали данную схему подключением диска для бэкапов с использованием iscsi (вроде локальный да не совсем) я думаю организация которая имеет «безопасников» может позволить себе железку с поддержкой данной технологии. Но как написал автор это одно из решений за это +

Reply ↓
asved.ru 15.02.2017 at 08:43

И снова русская народная забава по изобретению велосипедов.

https://ola.hallengren.com/

Reply ↓

Бекап средствами MS SQL во внешний источник по датам

Related Posts

10 Comments

Leave a Comment Отменить ответ