Архивация баз данных Microsoft SQL Server

По долгу службы сталкиваюсь с серверами баз данных MSSQL. Часто необходимо быстро настроить архивацию БД, на тестовых серверах, да и в продакшене. При этом в сети можно найти много разрозненных односложных источников, как надо или не надо архивировать, но нигде нет каких то более или менее универсальных готовых решений. На новом месте работы опять столкнулся с данной проблемой. В силу определенных причин все БД в компании (пока) находятся в режиме простой модели восстановления, потому решение, приведенное в тексте является не полным, но судя по вопросам на форумах, начинающим и просто разработчикам и администраторам, далеким от данных задач, вполне подойдет как решение, ну а в процессе каждый может дополнить его сам.

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

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

Указываем пути, где будем хранить наши архивы, в момент запуска архивации в данных папках создадутся подпапки по каждой архивируемой БД:

-- пути до бэкапов
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) = 'model, tempdb'

-- дни недели для полных бэкапов
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 where state_desc='ONLINE' 
	else
		while len(@IncludeBase)>0
		begin
			if CHARINDEX (',',@IncludeBase)>0
				begin
					insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and 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 state_desc='ONLINE' and name = @IncludeBase
					set @IncludeBase=''
				end
		 end

if @ExcludeBase='' 
		insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' 
	else
		while len(@ExcludeBase)>0
		begin
			if CHARINDEX (',',@ExcludeBase)>0
				begin
					insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and 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 state_desc='ONLINE' and 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
declare @notexistfull 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 EXISTS (SELECT * FROM msdb.dbo.backupset s
			INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
			WHERE s.database_name=@BaseName and s.TYPE='D' )
			set @notexistfull=0 else set @notexistfull=1



		--создаем папку для БД
		if @type=1 OR @BaseName='master' OR @notexistfull=1 set @tempcmd= 'md '+@FullPath+'\'+@BaseName else set @tempcmd= 'md '+@DiffPath+'\'+@BaseName
		exec xp_cmdshell @tempcmd, no_output

		
		if @type=1 OR @BaseName='master' OR @notexistfull=1
			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 mf.physical_device_name
				FROM   msdb.dbo.backupset s
				INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
			WHERE s.database_name=@BaseName and s.TYPE='D'
					and not s.backup_set_id in 
						(
							SELECT TOP (@MinFull) s.backup_set_id FROM   msdb.dbo.backupset s
								INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
								WHERE s.database_name=@BaseName and s.TYPE='D' 
								ORDER BY s.backup_finish_date desc
						) 
		union all
		SELECT mf.physical_device_name
				FROM   msdb.dbo.backupset s
				INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
			WHERE s.database_name=@BaseName and s.TYPE='I' 
					and not s.backup_set_id in 
						(
							SELECT TOP (@MinDiff) s.backup_set_id FROM   msdb.dbo.backupset s
								INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
								WHERE s.database_name=@BaseName and s.TYPE='I'
								ORDER BY s.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, по соображениям безопасности.

UPDATE
Скрип доработан для создания полного бэкапа для БД при запуске, если он отсутсвует, даже если сегодня не день для его создания

UPDATE2
Добавлена проверка на состояние БД (ONLINE)
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 29

    +1
    Велосипед давно уже изобретен — https://ola.hallengren.com/sql-server-backup.html.
      0
      Этот велосипед надо собрать в кучу и настроить, а теперь возьмите и посмотрите на половине предприятий с серьезными продуктами администрирование находится в зачаточном состоянии
        0
        В какую кучу? Что настроить? Скачали, запустили инсталяционный скрипт (подправив в нем нужные параметры типа пути для бэкапа, которые подробно описаны на странице), пользуемся. Попутно там решаются не менее важные задачи по обслуживанию БД. И не включается потенциальная дырища в безопасности по имени xp_cmdshell. В контексте «дать неподготовленным людям готовое решение» это просто небо и земля по сравнению с вашим вариантом.

        > на половине предприятий с серьезными продуктами администрирование находится в зачаточном состоянии

        То есть человек не осилил создание планов обслуживания в SSMS, или набрать в гугле ms sql backup script и найти фактически общеизвестное решение, зато прочитает вас на Хабре, возрадуется, и станет у него всё хорошо. Ну, логично.
          0
          По поводу вашей ссылки я честно не смотрел, спутал с другим решением, вполне юзабельно…

          Кто вам сказал, что «xp_cmdshell» — это дыра в безопасности? Не буду говорить про других, но там где я работал рано или поздно в масштабных проектах эта процедура включается просто потому, что иначе дольше/сложнее/ и т.д.

          Рад, что у вас своё мнение, вас не призывает ни кто использовать мой предложенное решение, те кто готов пойти дальше в итоге сделают как им захочется, ну а тем, кому не надо/не нужно, но служба обязывает…
          Думаю понятно о чем я
            +1
            > Кто вам сказал, что «xp_cmdshell» — это дыра в безопасности?

            Это шутка? Про риски для безопасности прямо в штатной документации написано, да и подумать можно, чем чревато исполнение произвольных команд ОС в контексте SQL-сервера. И погуглить можно, и найти рекомендации типа «Generally speaking, you must avoid using xp_cmdshell, and if possible, you should remove any dependency on it.» прямо на сайте MS.

            > включается просто потому, что иначе дольше/сложнее/ и т.д.

            Может, в каких-то проектах она и включается по необходимости для чего-то ещё. Но в этом конкретном случае вы лично иллюстрируете свои же слова о том, что «администрирование находится в зачаточном состоянии ». Вы используете мощный, но специфический и потенциально опасный инструмент абсолютно не по назначению.

            > Думаю понятно о чем я

            Мне понятно одно — интернет чем дальше, тем больше замусорен всякой фигнёй. Есть огромное количество примеров, когда люди вместо того, чтобы использовать широко известное правильно работающее решение, колхозят что-то свое. Само по себе это, разумеется, не плохо, сам так делаю (вполне вероятно, что в конкретном случае в собственной инфраструктуре решение будет лучше общедоступного, хотя у вас даже не этот случай), но когда этот колхоз выкладывается в интернет под соусом «готовое решение для неподготовленных людей», а те самые неподготовленные люди на это ведутся, это уже не просто плохо, а очень плохо.
              0
              Права серверу давайте соответсвующие и не будет «дыры»
              Поверьте я знаю, что пишет об это MS, вы в реальности живете или на отдельно настроенной системе в вакууме, я не призываю использовать, но глупо пользоваться ножем, там где нужна пила
                0
                >Права серверу давайте соответсвующие и не будет «дыры»
                >Поверьте я знаю, что пишет об это MS

                Извините, но второе предложение противоречит первому. Потенциальная опасность будет в любом случае, другое дело, что определенными мерами (отнюдь не только «права серверу дайте») степень опасности можно снизить, но вы об этих мерах ничего не рассказываете — просто включили, и всё.

                > но глупо пользоваться ножем, там где нужна пила

                Третий раз — ДЛЯ БЭКАПОВ эта процедура НЕ НУЖНА. Ни в вакууме, ни где-то ещё. Если вы считаете, что её надо включать для бэкапа — это просто безграмотность, а рекомендация делать так другим — ещё полная безответственность по отношению к людям, для которых вы якобы пишете.
                  0
                  Если, например, пользоваться сторонним софтом, ещё не известно, что в нем может быть, тут по крайней мере я знаю что делаю и представляю где и что меня может ждать
      +1
      Я еще в конце сжимаю rar кусками по 20Mb и через FTP отправляю на другой континент. И так лет 5 уже.
      Так как не знаю где будет нанесёс первый удар.
        0
        Геораспределенное хранение это хорошо, поддерживаю, но не всегда целесообразно
        0
        Это все в батнике прописывать?
          0
          Назначенное задание MS SQLAgent
            0
            с версией Express Edition такое не получится, как я понимаю?
            я для себя открыл SQLBackupAndFTP — вполне юзабельно, выполняет задание по времени, как сервис, можно и купить, что бы использовать удаленные хранилища, а меня устраивает и бесплатная возможность бакапить на FTP
              0
              Для SQL Express можно использовать планировщик Windows и скрипт обернуть например в VBS
                0
                TaskScheduler есть такой
            0
            Вижу резервное копирование, а не архивацию.
            И да, все это делается встроенным визардом без единой строчки кода.
              +1
              Объясните несведущему, чем оно лучше планов обслуживания, если не требуется отправлять РК частями по электронке на другой континент?
                0
                Ну во первых удобнее работать, добавили базу, она «подхватилась»
                Во вторых проще контролировать выполнение
                В третьих сразу следить за достаточным кол-вом копий и удаляет уже не нужные архивы
                В четвертых у вас одно задание для всего

                Всё это можно сделать и в планах обслуживания, но для этого придется потрудится, я пояснял в статье, что данное решение именно для неподготовленных особо пользователей, работающее «из коробки», реализованное стандартными средствами и работающее пожалуй на всех версиях с 2000 SQL точно (если убрать сжатие)
                  0
                  Всё это можно сделать и в планах обслуживания, но для этого придется потрудится

                  Не знаю, что сложнее, потыкать мышкой 20 минут и настроить бекапы, очистки, оповещения и все нужные вещи или курить скрипт из статьи.
                  0
                  А чем встроенные средства бекапирования не подходят? Версию express в рассмотрение не беру.
                    0
                    Вы про Планы обслуживания? На часть вопросов ответил выше ну и SSIS нужен для их работы
                      0
                      Планы обслуживания прекрасно работают без установленного SSIS, а с SSIS можно просто творить чудеса
                        0
                        Как у вас пакеты исполняются без подсистемы SSIS? Встроенные мастера для планов обслуживания генерируют пакеты SSIS
                      0
                      Наверное, имеет смысл отметить, что процедура xp_cmdshell по умолчанию отключена в Microsoft SQL Server, по соображениям безопасности.

                      xp_cmdshell Server Configuration Option
                        0
                        Да это так и есть, добавил в статью, спасибо
                        0
                        А чем не нравится PackDb (инструкция тут)? Написать простейший скрипт для архивирования сразу в сжатый архив и засунуть его в Task Scheduler, будет работать со всеми версиями MS SQL
                          0
                          А бэкап транзакционных логов?
                          Как я вижу примерный план:
                          1. По выходным полный бэкап баз, со сроком хранения Х-недель
                          2. Каждый рабочий день диффиринциальный бэкап по ночам, со сроком хранения не менее 7 дней
                          3. Каждый час (пол часа) бэкап транзакшен логов со сроком хранения не менее 7 дней

                          При этом желательно бэкапы разносить по разным местам.
                            0
                            Про логи я написал в статье, доделать ничего не мешает
                            0
                            Иногда использую скрипт ниже, создается 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

                            Only users with full accounts can post comments. Log in, please.