Настройка резервного копирования (резервирования) баз данных 1С: Предприятие на MS SQL Server

Администрирование - Архивирование (backup)

администрирование backup резервные копии резервирование архивирование 1С

17
Настройка резервного копирования (резервирования) баз данных на "бюджетной" версии 1С Предприятие под MS SQL Server. Используется пример MS SQL Server 2008 R2 под Windows. Для малых и средних предприятий, исключая производственные и торговые, так как тестирование на них не проводилось.

Настройка резервного копирования (резервирования) баз данных 1С Предприятие на MS SQL Server.

В данной статье используется MS SQL Server 2008 R2 под Windows.

Хочу сразу сказать, что статья посвящена настройке самого бюджетного варианта размещения 1С для среднестатистической фирмы с не более чем 10-ю рабочими местами, использующей только Бухгалерию Предприятия и ЗУП. Подойдет ли это для торгового предприятия – не проверял.

Можно разместить 1С сервер на PostgreSQL и Linux. Это будет дешевле за счет меньшей стоимости лицензий 1С и «бесплатности» SQL-ной части. Но так ли это на самом деле? Для обслуживания серверной части необходим специалист в штате или постановка на обслуживание в одной из фирм-франчайзи 1С. Совсем не бюджетный вариант.

Обычно в каждой малой или средней фирме всегда есть хотя бы один системный администратор, он же «железячник», хорошо разбирающейся в «окнах». Всегда есть Windows сервер и можно выделить место или машину для 1С. Почему бы не использовать MS SQL Server? Он очень хорошо дружит с Windows. Кто бы спорил. Дорого. Так ли это?

В линейке SQL Server у Microsoft существует выпуск под названием «Express». Это и есть наш бюджетный вариант. Microsoft о нем говорит следующее: «Бесплатная база данных начального уровня, которая идеально подходит для обучения и создания приложений для обработки данных на настольных компьютерах и небольших серверах (размером до 10 ГБ)». На 4 ядра процессора, буфер 1410 Мб, объем памяти на базу 352 Мб, максимальный размер базы 10 Гб. А для нашей фирмы надо больше? Нет.

Естественно, бесплатный сыр – только в мышеловке. Поэтому придется устраивать «пляски с бубном у костра», но один раз. Поплясали - и пускай работает.

 

1.Особенности резервирования MS SQL Server Express.

 

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

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

 

2.Стратегия резервирования.

 

Для нашего случая (малая или средняя не торговая фирма) стратегия будет такая:

- полная резервная копия 1 раз в день. Этого достаточно. Лучше делать во внерабочее время – ночью или утром.

- несколько разностных копий в рабочее время с периодичностью 1 – 3 часа. Разностные копии сохраняют только изменения внесенные в 1С после создания полной резервной копии. Поэтому они существенно меньше и их создание занимает меньше времени. Если нужно откатиться на несколько времени – делаем восстановление из полного и накатываем разностный по ближайшему времени до возникновения ошибки. От этого времени до момента ошибки – руками в 1С. А что делать? Бюджетная модель. На практике восстанавливать приходится очень редко и периодичность разностных копий в 3 часа вполне достаточна.

- сохранение всех резервных копий не только на основной носитель, но и на резервный файловый сервер. Обычно на сервере, где крутится 1С, не так много свободного места. Поэтому время удержания (количество хранящихся резервных копий по дням) может быть невелико. Увеличить время удержания и обезопасить себя от сбоев основного сервера можно выделив место на стороннем файловом сервере и передавать туда копии бэкапов после их создания на основном сервере.

Будем использовать штатные возможности SQL Server и Windows. Подойдем к решению задачи комплексно. Максимум начальной работы и минимум на поддержание, проверки и прописывание новых без данных.

 

3. Как это сделать. Часть на SQL Server.

 

Для этого создаем в системной базе данных Master определяющую процесс табличку adm_reserved_db_names. Вот скрипт ее создания:

 

USE [master]

GO

CREATE TABLE [dbo].[adm_reserved_db_names](

                [id] [int] IDENTITY(1,1) NOT NULL,

                [name_rsrv_db] [varchar](50) NOT NULL,

                [flag_rsrv] [int] NOT NULL,

                [flag_diff] [int] NOT NULL

) ON [PRIMARY]

GO

 

Id – номер по порядку, далее имя базы данных (совпадает с Database Name), флаг создания полной резервной копии и флаг создания разностной резервной копии. Флаги – целые числа. Удобно ставить и менять. 0 – делать, 1 – не делать. Понятно, что если не делаем полную копию, то разностная пойдет от последней сделанной полной или выдаст ошибку.

В результате заполненная рабочая таблица adm_reserved_db_names будет выгляеть так:

 

 

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

Полное резервирование adm_BackUp_Bases:

 

USE [master]

GO

CREATE PROCEDURE [dbo].[adm_BackUp_Bases]

AS

BEGIN

                declare @pathbkp varchar(100)

                declare @extbcp varchar(5)

                declare @dltfromdate varchar(19)

                declare @file varchar(200)

                declare @filename varchar(50),@logfile varchar(50)

                declare @dt varchar(15)

                declare @rdnc int

 

                declare CurDBName cursor for

select name_rsrv_db from master.dbo.adm_reserved_db_names

where flag_rsrv = 0 order by 1;

 

                set @pathbkp = 'C:\SQL_Data\BackUp\'

                set @extbcp = 'bak'

                set @rdnc = 3

                select @dltfromdate = convert(varchar(19),dateadd(DAY,-@rdnc, getdate()), 126); --'2011-11-23T11:14:48'

                select @dt = cast(datepart(yyyy,getdate()) as varchar(4))

                               +case

                                               when len(cast(datepart(mm,getdate()) as varchar(2)))=1

                                                               then '0'+cast(datepart(mm,getdate()) as varchar(2))

                                               else cast(datepart(mm,getdate()) as varchar(2))

                                end

                               +case

                                               when len(cast(datepart(dd,getdate()) as varchar(2)))=1

                                                               then '0'+cast(datepart(dd,getdate()) as varchar(2))

                                               else cast(datepart(dd,getdate()) as varchar(2))

                                end                                                      

                               +'_'

                               +case

                                               when len(cast(datepart(hh,getdate()) as varchar(2)))=1

                                                               then '0'+cast(datepart(hh,getdate()) as varchar(2))

                                               else cast(datepart(hh,getdate()) as varchar(2))

                                end

                               +case

                                               when len(cast(datepart(mi,getdate()) as varchar(2)))=1

                                                               then '0'+cast(datepart(mi,getdate()) as varchar(2))

                                               else cast(datepart(mi,getdate()) as varchar(2))

                               end;

 

                open CurDBName;

                fetch next from CurDBName into @filename;

 

                while @@FETCH_STATUS = 0

                begin

                                select @file = @pathbkp+@filename+'_backup_'+@dt+'.'+@extbcp;

                                BACKUP DATABASE @filename TO DISK = @file WITH NOFORMAT, NOINIT;

                                EXECUTE master.dbo.xp_delete_file 0,@pathbkp,@extbcp,@dltfromdate

 

                                fetch next from CurDBName into @filename;

                end;

               

                close CurDBName;

                deallocate CurDBName;

END

 

Определяем курсор CurDBName для перебора таблицы, переменной @pathbkp определяем путь хранения резервных копий, @extbcp - расширение резервного файла, @dt - дата-идентификатор файла, @rdnc - удержание в сутках. Запускаем курсор, считываем имя базы данных, формируем имя резервного файла, делаем backup средствами T-SQL и удаляем устаревшие резервные файлы. И так – до конца таблицы. Получаем файлы с такой нотацией:

XX_ACC_backup_20181022_0500.bak

XX_HRM_backup_20181019_0500.bak

Причем количество символов в названии должно быть равно у всех баз данных. Зачем – объясню позднее.

Разностное резервирование adm_BackUp_Bases_Diff:

 

USE [master]

GO

CREATE PROCEDURE [dbo].[adm_BackUp_Bases_Diff]

AS

BEGIN

                declare @pathbkp varchar(100)

                declare @extbcp varchar(5)

                declare @dltfromdate varchar(19)

                declare @file varchar(200)

                declare @filename varchar(50),@logfile varchar(50)

                declare @dt varchar(15)

                declare @rdnc int

 

                declare CurDBName cursor for

                select name_rsrv_db from master.dbo.adm_reserved_db_names

                where flag_diff = 0 order by 1;

 

                set @pathbkp = 'C:\SQL_Data\BackUp\'

                set @extbcp = 'bak'

               

                open CurDBName;

                fetch next from CurDBName into @filename;

 

                while @@FETCH_STATUS = 0

                begin

                                select @file = @pathbkp+'DFF_'+@filename+'.'+@extbcp;

                                BACKUP DATABASE @filename TO DISK = @file WITH DIFFERENTIAL, NOFORMAT, NOINIT;

                                fetch next from CurDBName into @filename;

                end;

               

                close CurDBName;

                deallocate CurDBName;

END

 

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

DFF_XX_ACC.bak

Все разностные копии SQL сервером кладутся в один файл и он, с течением времени, увеличивает свой размер.

 

 

4. Как это сделать. Часть на Windows.

 

В примере использован Windows Server 2008 R2 Standard.

Так как в MS SQL Server Express отсутствует SQL Agent, то никакое задание на стороне SQL сервера мы выполнить не можем. Но можно управлять им из командной строки операционной системы.

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

Один файл содержит инструкцию на языке T-SQL и имеет расширение *.sql, другой обычный командный файл с расширением *.com или *.bat в зависимости от версии ОС. Содержимое пишется в обычном текстовом редакторе и сохраняется с соответствующим расширением.

Полное резервирование sql-инструкция BackUp_DB.sql:

execute master.dbo.adm_BackUp_Bases

Здесь дается команда выполнить процедуру на SQL сервере в системной базе данных Master.

Полное резервирование командный файл BackUp1C.cmd:

 

@echo off

sqlcmd -S ServerName\Instance -U User -P Passvord -i C:\ BackUp_Tools\BackUp_DB.sql

del \\ ServerName \BackUp\DFF_*.bak

robocopy "\\ServerName\BackUp" "\\DisasterServerName\BackUp" *.bak /R:0 /MAXAGE:1

 

Sqlcmd - служебная программа, которая позволяет вводить инструкции T-SQL, системные процедуры и файлы скриптов в командной строке. Она предустанавливается с MS SQL сервером или ее можно скачать с сайта Microsoft. ServerName – имя сервера где размещена 1С, Instance – имя экземпляра MS SQL Server где крутится 1С, User и Passvord – имя и пароль пользователя с правами администратора (sa). Пароль в открытом доступе очень плохо. Но в нашем бюджетном варианте от этого никуда не денешься. Лучше всего создать в MS SQL Server специального пользователя только для создания backup-ов и хорошо защитить операционную систему от проникновения извне.

Как я говорил выше, после создания полной резервной копии файлы разностных backup-ов теряют смысл, поэтому их удаляем.

DisasterServerName – имя резервного файлового сервера, где размещается архив полных резервных копий. Копируем их после удаления всего лишнего утилитой robocopy. Для нашего случая она подходит идеально. Задаем пути откуда брать и куда класть, шаблон имен файлов и время удержания. В нашем примере MAXAGE:1 – это файлы с текущей датой. Robocopy качаем из интернета и устанавливаем.

Разностное резервирование sql-инструкция BackUp_DB_Diff.sql:

execute master.dbo.adm_BackUp_Bases_Diff

Разностное резервирование командный файл BackUp1C_Diff.cmd:

@echo off

sqlcmd -S ServerName\Instance -U User -P Passvord -i C:\ BackUp_Tools\BackUp_DB_Diff.sql

robocopy "\\ServerName\BackUp" "\\DisasterServerName\BackUp" DFF_*.bak /R:0 /MAXAGE:1

Дальше надо настроить выполнение заданий в операционной системе. Используем Task Sheduler. Для разностного резервирования можно использовать такой вариант периодичности:

Через каждые 3 часа начиная с 8 утра до 8 вечера.

 

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

 

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

Выгоняем всех пользователей из этой базы данных. Галочкой отмечаем полную и ту разностную копию, которая предшествовала по времени моменту возникновения ошибки. Нажимаем OK и ждем. После завершения задания база готова к работе.

 

6. Небольшой лайфхак для администратора.

 

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

Создаем на 1С сервере пустую тестовую базу данных. Переходим в SQL Server Management Studio.Аналогично как в п.п. 5 откроем окно восстановления нашей тестовой базы данных.

Будем использовать архив нужной Вам базы. Для этого выбираем ее из списка в разделе From database. Переходим в раздел задания параметров Options.

Ставим галочку перезаписывания тестовой базы данных (2), проверяем выбор статуса восстановления (3). Нажатием кнопок (4 и 5) заменяем наименование файла базы-иметента данных (файл *.mdf) на наименование Вашей тестовой базы данных из списка (4) и заменяем наименование файла журнала событий базы-иметента данных (файл *.ldf) на наименование журнала событий Вашей тестовой базы данных из списка (5). Нажимаем ОК и ждем. После завершения работы открываем тестовую базу 1С в режиме конфигуратора и, никому не мешая, делаем файловую копию.

Если нужны данные «на сейчас», тогда предварительно запускаем на Windows сервере командный файл BackUp1C_Diff.cmd и получаем свежую разностную копию.

17

См. также

Комментарии
Избранное Подписка Сортировка: Древо
1. Gilev.Vyacheslav 1796 30.10.18 17:25 Сейчас в теме
почему Express не 2016 например?
9. Timur.V 31 31.10.18 10:45 Сейчас в теме
(1)
А есть разница между Express 2016 и Express 2008 ?
1С какие-то новые функции поддерживает?
10. Gilev.Vyacheslav 1796 31.10.18 14:43 Сейчас в теме
(9) Разница по размеру файла
2. Fox-trot 91 30.10.18 17:49 Сейчас в теме
видима потому, что статья писалась надцать лет тому назад
3. blackhole321 803 30.10.18 19:36 Сейчас в теме
Начальный уровень SQL сервера в плане администрирования нашей задачи означает, что в нем используется простая модель восстановления данных. Т.е. нельзя сделать резервную копию журналов транзакций базы данных,

SQL Express не поддерживает full recovery model?
5. unclevad 23 31.10.18 09:01 Сейчас в теме
(3)
Эта система сделана под MS SQL 2008, соответственно в 2008 году, о чем в начале статьи упомянуто. Если система работает стабильно и ее возможности устраивают клиента, то перестраивать ее можно только при глобальных изменениях.
7. Shevon 31.10.18 09:52 Сейчас в теме
(3) Все таки... Полная модель восстановления SQLEXPRESS поддерживается. Почему нельзя делать резервную копию журнала транзакций. И чем так критично отсутствие SQL SERVER Agent?
13. unclevad 23 31.10.18 17:33 Сейчас в теме
(7)

SQL SERVER Agent выполняет административные задания, определенные Вами, на SQL SERVER по Вашему расписанию и в соответствии с Вашим планом обслуживания. Maintenance Plans также не входит в EXPRESS. Приходится использовать окольные пути.
При полной модели восстановления, после создания резервной копии базы данных, резервирование журнала транзакций нужно для сохранения всех завершенных транзакций во время создания backup. В рассматриваемых мной задачах нагрузка на базу данных небольшая и от этого можно отказаться и применить простую модель восстановления. Вопрос спорный, но мне легче оперировать с одним файлом дифференциальной копии, нежели с несколькими файлами *.trn, если учесть, что поддерживаемых баз несколько.
4. blackhole321 803 30.10.18 19:38 Сейчас в теме
И зачем лезть в master etc. Скрипт в windows scheduler не подходит?
6. unclevad 23 31.10.18 09:12 Сейчас в теме
(4)
И зачем лезть в master etc. Скрипт в windows scheduler не подходит?


Скрипт статичен. При создании новой или тестовой базы данных и необходимости ее резервировать скрипт придется дописывать. Таблицу можно менять динамически. Наиболее подходящая системная база - master. Не надо ее бояться.
8. blackhole321 803 31.10.18 10:27 Сейчас в теме
(6)Дык никто ее не боится :) Просто она предназначена для хранения системной информации sql server. Для всего остального - есть пользовательские базы.
Таблицу можно менять динамически.

Можно, только надо-ли? Вы же будете ее менять при помощи какого-либо инструмента, ведь так? Ну собственно эт то же самое, что и поправить скрипт.
Скрипт прозрачен и понятен любому dba/админу без всяких предположений о наличии/отсутствии каких-либо таблиц в master etc.
Просто backup database ..., добавилась база - ctrl+c, ctrl+v
11. unclevad 23 31.10.18 15:58 Сейчас в теме
(8)

В пользовательской базе системные обслуживающие процедуры делать можно, но некорректно.
У меня, как у DBA, всегда открыт Management Studio. Т.е. все под рукой. Открыть его в три клика. А искать из пары-тройки десятков скриптов по нескольким серверам то еще удовольствие. Я, обычно, пишу текстовые документы типа "Топология резервирования...". Иначе через несколько месяцев забывается.
12. blackhole321 803 31.10.18 16:35 Сейчас в теме
(11)
В пользовательской базе системные обслуживающие процедуры делать можно, но некорректно.

Почему же :)? Как раз-таки СУБД и предназначена для создания и работы с Вашими данными. Создаете свою служебную БД и делаете в ней что хотите, а системная БД для системы. Если Вы dba, то вроде как ...
У меня, как у DBA, всегда открыт Management Studio. Т.е. все под рукой.

Тем более, открываете свой sql скрипт в management studio и правите. А смысл писать скрипт delete from, ins ert in to или update ВашаТаблица, для того, чтобы обновить Вашу таблицу с параметрами?
14. unclevad 23 31.10.18 17:45 Сейчас в теме
(12)
писать скрипт delete from, ins ert in to или update ВашаТаблица


Правая кнопка мыши, Open Table и все изменения, удаления, вставки как в Excel... Но можно и текст процедуры менять. Вопрос подхода.

В MS SQL 2008 базы Resource нет, все системные объекты хранятся в Master. Такой же план обслуживания, только несколько иного вида.
16. KRIHA 51 04.11.18 09:15 Сейчас в теме
"Можно разместить 1С сервер на PostgreSQL и Linux. Это будет дешевле за счет меньшей стоимости лицензий 1С" - что вы имели ввиду сказав эту фразу?
17. unclevad 23 06.11.18 12:43 Сейчас в теме
(16)
Оговорился. Лицензии на 1С сервер + SQL.
Не могу найти ссылку, но видел скидку "на все" при условии поддержки 1С и PostgreSQL этой компанией.
Оставьте свое сообщение