Настройка резервного копирования 1C и сжатие файла лога транзакций MS SQL при помощи Transact-SQL

13.02.2020

Введение

Логи транзакций растут при выбранной полной модели восстановления в настройках баз данных. Эта модель подразумивает под собой периодическое резервное копирования лога транзакций. Если вы не бэкапите логи транзакций sql вам лучше перевести свою базу данных на простую модел восстановления и логи транзакций расти не будут, а соответственно отпадает надобность в сжатии (шринке) лога.

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

Настройка полного резервного копирования базы данных MSSQL

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

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

Transact-SQL скрипт ниже выполняет резервное копирование базы данных MSSQL


DECLARE @pathName NVARCHAR(512) 
SET @pathName = 'ДИРЕКТОРИЯ_С_РЕЗЕРВНЫМИ_КОПИЯМИ\ИМЯ_БАЗЫ_' + Convert(varchar(10), GETDATE(), 105) + '.bak' 
BACKUP DATABASE [ИМЯ_БАЗЫ] TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N'ИМЯ_БАЗЫ_full_bkp', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Transact-SQL скрипт для настройки создания резервной копии журнала транзакций

А этот скрипт Transact-SQL выполняет резервное копирования лога транзакций. Тут я немного намудрил с датой. У меня никак не получалось записать файл с названием, где бы было указано в котором часу был сделан бэкап, так как формат даты в Transact-SQL возвращал значение с ":", а данный символ в названии на ОС Windows использовать запрещено. REPLACE мне не помог и пришлось поступить следующим образом: получить в каждую переменную отдельно часы, минуты, секунды и в другой переменной склеить их между собой так, как мне это необходимо было. Вот что получилось:

DECLARE @pathName NVARCHAR(512) 
DECLARE @h NVARCHAR(512) = DATEPART ( hh , GETDATE() );
DECLARE @m NVARCHAR(512) = DATEPART ( mi , GETDATE() );
DECLARE @s NVARCHAR(512) = DATEPART ( ss , GETDATE() ); 
DECLARE @myTime NVARCHAR(512) = @h + '-' + @m + '-' + @s;
SET @pathName = 'ДИРЕКТОРИЯ_С_РЕЗЕРВНЫМИ_КОПИЯМИ\ИМЯ_БАЗЫ_log_' + Convert(varchar(10), GETDATE(), 121) + '[' + @myTime + ']' +  '.bak'

BACKUP LOG [ИМЯ_БАЗЫ] TO  DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N'ИМЯ_БАЗЫ-log', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Transact-SQL скрипт для настройки сжатия лога транзакции

А этим скриптом сжимаем сам лог транзакций:

  • Переводим базу на простую модель восстановления
  • Сжимаем до определённого размера (в мегабайтах)
  • Переводим базу данных обратно на полную модель восстановления
USE ИМЯ_БАЗЫ
GO
ALTER DATABASE ИМЯ_БАЗЫ
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (2, 1);  
GO
ALTER DATABASE ИМЯ_БАЗЫ
SET RECOVERY FULL;
GO

Форматы дат Transact-SQL

Да, кстати вот сразу таблица с форматом дат для ридактирования скрипта. Она мне пригодилась, может пригодится и вам:

Форматы дат Transact-SQL
Формат Значение Пример
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
12 select convert(varchar, getdate(), 12) 061230
23 select convert(varchar, getdate(), 23) 2006-12-30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30
112 select convert(varchar, getdate(), 112) 20061230
Только время
8 select convert(varchar, getdate(), 8) 00:38:54
14 select convert(varchar, getdate(), 14) 00:38:54:840
24 select convert(varchar, getdate(), 24) 00:38:54
108 select convert(varchar, getdate(), 108) 00:38:54
114 select convert(varchar, getdate(), 114) 00:38:54:840
Дата и время
0 select convert(varchar, getdate(), 0) Dec 12 2006 12:38AM
9 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
13 select convert(varchar, getdate(), 13) 30 Dec 2006 00:38:54:840AM
20 select convert(varchar, getdate(), 20) 2006-12-30 00:38:54
21 select convert(varchar, getdate(), 21) 2006-12-30 00:38:54.840
22 select convert(varchar, getdate(), 22) 12/30/06 12:38:54 AM
25 select convert(varchar, getdate(), 25) 2006-12-30 00:38:54.840
100 select convert(varchar, getdate(), 100) Dec 30 2006 12:38AM
109 select convert(varchar, getdate(), 109) Dec 30 2006 12:38:54:840AM
113 select convert(varchar, getdate(), 113) 30 Dec 2006 00:38:54:840
120 select convert(varchar, getdate(), 120) 2006-12-30 00:38:54
121 select convert(varchar, getdate(), 121) 2006-12-30 00:38:54.840
126 select convert(varchar, getdate(), 126) 2006-12-30T00:38:54.840
127 select convert(varchar, getdate(), 127) 2006-12-30T00:38:54.840
Автору на кофе ☕ или просто поднять мотивацию писать больше статей для вас 😉 !
Отдельное спасибо всем, кто отправляет донатики 😀! Очень приятно и неожиданно. Благодаря вам сайт может и в дальнейшем обходиться без рекламных баннеров.
4943