Скрипт выполняет архивацию указанных БД учитывая, по каким дням недели делать полные копии, сколько хранить последних полных и разностных архивов. Предназначен для архивации баз данных с простой моделью восстановления, если вам необходимо архивировать ещё и логи, то по аналогии вполне можете дополнить скрипт сами. Возможно, когда мне понадобится, я допишу данный скрипт. По окончанию архивации устаревшие архивы БД удаляются с диска.
Данный код можно сразу добавить в назначенное задание и запускать его раз в сутки или чаще, кому как нравится, при этом установив некоторые параметры, скрипт достаточно прокомментирован, остановлюсь лишь вкратце:
Указываем пути, где будем хранить наши архивы, в момент запуска архивации в данных папках создадутся подпапки по каждой архивируемой БД:
-- пути до бэкапов
declare @FullPath varchar(500) = 'D:\Work\Full'
declare @DiffPath varchar(500) = 'D:\Work\Diff'
Если не указываем список конкретных БД в @IncludeBase для архивации, то берутся все БД и из них исключаются базы указанные в @ExcludeBase:
-- архивируемые и исключенные из архивации БД
declare @IncludeBase varchar(500) = '' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные
declare @ExcludeBase varchar(500) = 'master, model, tempdb'
Тут укажем через запятую дни недели для полных архивов:
-- дни недели для полных бэкапов
declare @FullDay varchar(13) = '7'
Сколько последних копий для каждого типа архивов оставлять на диске:
-- сколько минимум хранить архивов
declare @MinFull int = 3
declare @MinDiff int = 3
При работе скрипта на сервере включается сжатие архивов и возможность запуска процедуры xp_cmdshell. У службы должны быть права на чтение/запись/удаление в каталоги с архивами.
Текст скрипта:
-- пути до бэкапов
declare @FullPath varchar(500) = 'D:\Work\Full'
declare @DiffPath varchar(500) = 'D:\Work\Diff'
-- архивируемые и исключенные из архивации БД
declare @IncludeBase varchar(500) = '' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные
declare @ExcludeBase varchar(500) = 'master, model, tempdb, msdb'
-- дни недели для полных бэкапов
declare @FullDay varchar(13) = '7'
-- сколько минимум хранить архивов
declare @MinFull int = 3
declare @MinDiff int = 3
-- включим сжатие
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'backup compression default', 1
RECONFIGURE WITH OVERRIDE
-- включим xp_cmdshell
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
set datefirst 1
declare @tempcmd varchar(500) =''
declare @tempname varchar(500) =''
-- создание путей
set @tempcmd= 'md '+@FullPath
exec xp_cmdshell @tempcmd, no_output
set @tempcmd= 'md '+@DiffPath
exec xp_cmdshell @tempcmd, no_output
-- определяем список БД для архивации
declare @BaseListIncl table (name varchar(200))
declare @BaseListExcl table (name varchar(200))
if @IncludeBase=''
insert into @BaseListIncl select name from sys.databases
else
while len(@IncludeBase)>0
begin
if CHARINDEX (',',@IncludeBase)>0
begin
insert into @BaseListIncl select name from sys.databases where name = SUBSTRING(@IncludeBase,1, CHARINDEX (',',@IncludeBase)-1)
set @IncludeBase=LTRIM(RTRIM(SUBSTRING(@IncludeBase,CHARINDEX (',',@IncludeBase)+1, LEN(@IncludeBase))))
end
else
begin
insert into @BaseListIncl select name from sys.databases where name = @IncludeBase
set @IncludeBase=''
end
end
if @ExcludeBase=''
insert into @BaseListIncl select name from sys.databases
else
while len(@ExcludeBase)>0
begin
if CHARINDEX (',',@ExcludeBase)>0
begin
insert into @BaseListExcl select name from sys.databases where name = SUBSTRING(@ExcludeBase,1, CHARINDEX (',',@ExcludeBase)-1)
set @ExcludeBase=LTRIM(RTRIM(SUBSTRING(@ExcludeBase,CHARINDEX (',',@ExcludeBase)+1, LEN(@ExcludeBase))))
end
else
begin
insert into @BaseListExcl select name from sys.databases where name = @ExcludeBase
set @ExcludeBase=''
end
end
-- итоговый список БД для архивации
delete from @BaseListIncl
where name in (select name from @BaseListExcl)
declare BaseList cursor for
select name from @BaseListIncl
declare @BaseName varchar(500) =''
-- проверяем, какой сегодня нам создать архив
declare @type bit = 0
if CHARINDEX(CAST(DATEPART(weekday,getdate()) as varchar(1)),@FullDay)>0
set @type=1
open BaseList
fetch next from BaseList into @BaseName
while @@FETCH_STATUS = 0
begin
--создаем папку для БД
if @type=1 OR @BaseName='master' set @tempcmd= 'md '+@FullPath+'\' + @BaseName else set @tempcmd= 'md ' + @DiffPath + '\' + @BaseName
exec xp_cmdshell @tempcmd, no_output
if @type=1 OR @BaseName='master'
begin
-- full backup
set @tempname = @FullPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'.FULL'
backup database @BaseName to disk = @tempname
end
else
begin
-- diff backup
set @tempname = @DiffPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'.DIFF'
backup database @BaseName to disk = @tempname with differential
end
-- удаляем лишние бэкапы
declare @delpath varchar(500)=''
declare delbackup cursor for
select physical_device_name
from msdb..backupfile as bf join msdb..backupmediafamily as bmf on bf.backup_set_id=bmf.media_set_id
join msdb..backupset bs on bf.backup_set_id=bs.backup_set_id
where bf.file_type='D' and type = 'D' and database_name=@BaseName
and not bmf.media_set_id in (
select top (@MinFull) bmf.media_set_id
from msdb..backupfile as bf join msdb..backupmediafamily as bmf on bf.backup_set_id=bmf.media_set_id
join msdb..backupset bs on bf.backup_set_id=bs.backup_set_id
where bf.file_type='D' and type = 'D' and database_name=@BaseName
order by backup_finish_date desc
)
union all
select physical_device_name
from msdb..backupfile as bf join msdb..backupmediafamily as bmf on bf.backup_set_id=bmf.media_set_id
join msdb..backupset bs on bf.backup_set_id=bs.backup_set_id
where bf.file_type='D' and type = 'I' and database_name=@BaseName
and not bmf.media_set_id in (
select top (@MinDiff) bmf.media_set_id
from msdb..backupfile as bf join msdb..backupmediafamily as bmf on bf.backup_set_id=bmf.media_set_id
join msdb..backupset bs on bf.backup_set_id=bs.backup_set_id
where bf.file_type='D' and type = 'I' and database_name=@BaseName
order by backup_finish_date desc
)
open delbackup
fetch next from delbackup into @delpath
while @@FETCH_STATUS = 0
begin
set @tempcmd= 'del /f /q '+QUOTENAME(@delpath,'"')
exec xp_cmdshell @tempcmd, no_output
fetch next from delbackup into @delpath
end
close delbackup
deallocate delbackup
fetch next from BaseList into @BaseName
end
close BaseList
deallocate BaseList
-- чистим в MSDB информацию о старых архивах (свыше 120 дней)
declare @oldest DATETIME
SET @oldest = DATEADD(DAY, -120, GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest
Данный скрипт написан под конкретные задачи, обсуждать, что в продакшене так нельзя и прочее думаю не имеет смысла, публикуется с целью дать возможность неподготовленным людям получить готовое решение.
Добавлю из замечания пользователя ideatum, что процедура xp_cmdshell по умолчанию отключена в Microsoft SQL Server, по соображениям безопасности.
Комментарии (29)
vipwww
02.12.2016 07:28Это все в батнике прописывать?
Isiirk
02.12.2016 07:28Назначенное задание MS SQLAgent
vasyakrg
02.12.2016 07:37с версией Express Edition такое не получится, как я понимаю?
я для себя открыл SQLBackupAndFTP — вполне юзабельно, выполняет задание по времени, как сервис, можно и купить, что бы использовать удаленные хранилища, а меня устраивает и бесплатная возможность бакапить на FTPIsiirk
02.12.2016 07:54Для SQL Express можно использовать планировщик Windows и скрипт обернуть например в VBS
gotch
02.12.2016 10:00Вижу резервное копирование, а не архивацию.
И да, все это делается встроенным визардом без единой строчки кода.
akamap
02.12.2016 10:04+1Объясните несведущему, чем оно лучше планов обслуживания, если не требуется отправлять РК частями по электронке на другой континент?
Isiirk
02.12.2016 10:10Ну во первых удобнее работать, добавили базу, она «подхватилась»
Во вторых проще контролировать выполнение
В третьих сразу следить за достаточным кол-вом копий и удаляет уже не нужные архивы
В четвертых у вас одно задание для всего
Всё это можно сделать и в планах обслуживания, но для этого придется потрудится, я пояснял в статье, что данное решение именно для неподготовленных особо пользователей, работающее «из коробки», реализованное стандартными средствами и работающее пожалуй на всех версиях с 2000 SQL точно (если убрать сжатие)
Klaster
02.12.2016 19:25Всё это можно сделать и в планах обслуживания, но для этого придется потрудится
Не знаю, что сложнее, потыкать мышкой 20 минут и настроить бекапы, очистки, оповещения и все нужные вещи или курить скрипт из статьи.
ideatum
02.12.2016 10:54Наверное, имеет смысл отметить, что процедура xp_cmdshell по умолчанию отключена в Microsoft SQL Server, по соображениям безопасности.
xp_cmdshell Server Configuration Option
krasaval
05.12.2016 02:52А бэкап транзакционных логов?
Как я вижу примерный план:
1. По выходным полный бэкап баз, со сроком хранения Х-недель
2. Каждый рабочий день диффиринциальный бэкап по ночам, со сроком хранения не менее 7 дней
3. Каждый час (пол часа) бэкап транзакшен логов со сроком хранения не менее 7 дней
При этом желательно бэкапы разносить по разным местам.
mmaxm
05.12.2016 08:20Иногда использую скрипт ниже, создается CMDшник и вешается на планировщик windows
for /f «delims=.» %%i in ('wmic.exe OS get LocalDateTime ^| find "."') do set sDateTime=%%i
rem echo %sDateTime%
set DIR_BACKUP=D:\arcdb\
set NAME_DB=DataBaseName
set NAME_ARC_FILE=%NAME_DB%_%sDateTime%.bak
set NAME_PC=ASUS-PC\SQLEXPRESS
rem md %DIR_BACKUP%
osql -UUserName -PPassword -S %NAME_PC% -Q «BACKUP DATABASE %NAME_DB% TO DISK = '%DIR_BACKUP%\%NAME_ARC_FILE%' with init»
Pause
ildarz
Велосипед давно уже изобретен — https://ola.hallengren.com/sql-server-backup.html.
Isiirk
Этот велосипед надо собрать в кучу и настроить, а теперь возьмите и посмотрите на половине предприятий с серьезными продуктами администрирование находится в зачаточном состоянии
ildarz
В какую кучу? Что настроить? Скачали, запустили инсталяционный скрипт (подправив в нем нужные параметры типа пути для бэкапа, которые подробно описаны на странице), пользуемся. Попутно там решаются не менее важные задачи по обслуживанию БД. И не включается потенциальная дырища в безопасности по имени xp_cmdshell. В контексте «дать неподготовленным людям готовое решение» это просто небо и земля по сравнению с вашим вариантом.
> на половине предприятий с серьезными продуктами администрирование находится в зачаточном состоянии
То есть человек не осилил создание планов обслуживания в SSMS, или набрать в гугле ms sql backup script и найти фактически общеизвестное решение, зато прочитает вас на Хабре, возрадуется, и станет у него всё хорошо. Ну, логично.
Isiirk
По поводу вашей ссылки я честно не смотрел, спутал с другим решением, вполне юзабельно…
Кто вам сказал, что «xp_cmdshell» — это дыра в безопасности? Не буду говорить про других, но там где я работал рано или поздно в масштабных проектах эта процедура включается просто потому, что иначе дольше/сложнее/ и т.д.
Рад, что у вас своё мнение, вас не призывает ни кто использовать мой предложенное решение, те кто готов пойти дальше в итоге сделают как им захочется, ну а тем, кому не надо/не нужно, но служба обязывает…
Думаю понятно о чем я
ildarz
> Кто вам сказал, что «xp_cmdshell» — это дыра в безопасности?
Это шутка? Про риски для безопасности прямо в штатной документации написано, да и подумать можно, чем чревато исполнение произвольных команд ОС в контексте SQL-сервера. И погуглить можно, и найти рекомендации типа «Generally speaking, you must avoid using xp_cmdshell, and if possible, you should remove any dependency on it.» прямо на сайте MS.
> включается просто потому, что иначе дольше/сложнее/ и т.д.
Может, в каких-то проектах она и включается по необходимости для чего-то ещё. Но в этом конкретном случае вы лично иллюстрируете свои же слова о том, что «администрирование находится в зачаточном состоянии ». Вы используете мощный, но специфический и потенциально опасный инструмент абсолютно не по назначению.
> Думаю понятно о чем я
Мне понятно одно — интернет чем дальше, тем больше замусорен всякой фигнёй. Есть огромное количество примеров, когда люди вместо того, чтобы использовать широко известное правильно работающее решение, колхозят что-то свое. Само по себе это, разумеется, не плохо, сам так делаю (вполне вероятно, что в конкретном случае в собственной инфраструктуре решение будет лучше общедоступного, хотя у вас даже не этот случай), но когда этот колхоз выкладывается в интернет под соусом «готовое решение для неподготовленных людей», а те самые неподготовленные люди на это ведутся, это уже не просто плохо, а очень плохо.
Isiirk
Права серверу давайте соответсвующие и не будет «дыры»
Поверьте я знаю, что пишет об это MS, вы в реальности живете или на отдельно настроенной системе в вакууме, я не призываю использовать, но глупо пользоваться ножем, там где нужна пила
ildarz
>Права серверу давайте соответсвующие и не будет «дыры»
>Поверьте я знаю, что пишет об это MS
Извините, но второе предложение противоречит первому. Потенциальная опасность будет в любом случае, другое дело, что определенными мерами (отнюдь не только «права серверу дайте») степень опасности можно снизить, но вы об этих мерах ничего не рассказываете — просто включили, и всё.
> но глупо пользоваться ножем, там где нужна пила
Третий раз — ДЛЯ БЭКАПОВ эта процедура НЕ НУЖНА. Ни в вакууме, ни где-то ещё. Если вы считаете, что её надо включать для бэкапа — это просто безграмотность, а рекомендация делать так другим — ещё полная безответственность по отношению к людям, для которых вы якобы пишете.
Isiirk
Если, например, пользоваться сторонним софтом, ещё не известно, что в нем может быть, тут по крайней мере я знаю что делаю и представляю где и что меня может ждать