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

Настройка резервного копирования (резервирования) баз данных 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_DataBackUp’

                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_20241022_0500.bak

XX_HRM_backup_20241019_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_DataBackUp’

                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 ServerNameInstance -U User -P Passvord -i C: BackUp_ToolsBackUp_DB.sql

del \ ServerName BackUpDFF_*.bak

robocopy "\ServerNameBackUp" "\DisasterServerNameBackUp" *.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 ServerNameInstance -U User -P Passvord -i C: BackUp_ToolsBackUp_DB_Diff.sql

robocopy "\ServerNameBackUp" "\DisasterServerNameBackUp" 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 и получаем свежую разностную копию.