Удобный регулярный бэкап баз sql 2000

i2akai1

Зацените люди :)может кому пригодитсяСделал процедурки для регулярного бэкапа БД архивирования и копирования на другой сервер.Процедуры запускаются через SQL server agentПроцедура бэкапа:1. Делает любой вид бэкапа (full diff tlog)2. Кладет бэкап в заданную папку, формируя имя как "дата-время.сервер.база.вид"3. Проверяет целостность бэкапа средствами SQL2000 (restore verifyonly)4. Вычисляет md5 получившегося файла бэкапа и кладет его рядом5. Архивирует раром в фоновом режиме бэкап и md56. Вычисляет md5 получившегося архива и кладет его рядомПри этом удаляет старые бэкапы чтобы осталось свежие бэкапы за указанное количество дней(чтобы они на всякий случай хранились локально на сервере)Удаляет файлы старых бэкапов только за указанные дни недели(например бэкапы делаются только в будние дни поэтому выходные пропускаются при определениифайлов которые нужно удалить или оставить)Саздает на время создания бэкапа и его архивирование lock файл чтобы процедура копированияслучайно не начала копировать незавершенный бэкапПроцедура копирования:Копирует в другую, возможно сетевую, папку архив бэкапа и его md5, который полностью готовИ удаляет за собой файлы архива и его md5Все это удобно запускать как jobыПроцедура бэкапа:
use master 
go

drop procedure _step_Бэкап_базы
go

/*
 $Date: 2009-05-21 19:54:48 +0400 $
 $Rev: 382 $
 $Author: i1 $

 Создает бэкап базы и архивирует его. 
*/

create procedure _step_Бэкап_базы
 @db nvarchar(<b>100</b>),
 @type nvarchar(<b>4</b>), --full diff tlog
 @bckproot nvarchar(<b>500</b>), --каталог куда складывать бэкапы
 @binpath nvarchar(<b>500</b>), --каталог с rar.exe и exf.exe
 @keepdays int, --сколько копий бэкапов не считая текущего хранить
 @weekdays *******, --маска дней недели в формате sp_add_jobschedule, бэкапы за которые будут удаляться
 @deldeepdays int --глубина удаления файлов старых бэкапов
as 
begin
 set nocount on
 declare 
 @date nvarchar(<b>10</b>),
 @datetime nvarchar(<b>19</b>),
 @deldate datetime,
 @cmd nvarchar(<b>1000</b>),
 @bckpfullname nvarchar(<b>1000</b>),
 @bckpfilename nvarchar(<b>1000</b>),
 @bckpname nvarchar(<b>1000</b>),
 @result int,
 @shelloutput nvarchar(<b>4000</b>), 
 @msg nvarchar(<b>4000</b>),
 @line nvarchar(<b>4000</b>)

 set @weekdays = isnull(@weekdays, <b>127</b>)
 if @weekdays & <b>127</b> = <b>0</b>
 begin
 raiserror('Должен быть задан хотя-бы 1 день недели.', <b>16</b>, <b>1</b>)
 return -<b>1</b>
 end

 set @type = isnull(@type, '')
 if @type not in ('full', 'diff', 'tlog')
 begin
 raiserror('@type должен быть full, diff или tlog.', <b>16</b>, <b>1</b>)
 return -<b>1</b>
 end

 set @binpath = isnull(ltrim(rtrim(@binpath)), '')
 if @binpath = ''
 begin
 raiserror('@binpath должен указывать на папку с rar.exe и exf.exe.', <b>16</b>, <b>1</b>)
 return -<b>1</b>
 end

 set @deldate = dateadd(d, -<b>1</b>, getdate())
 set @keepdays = isnull(@keepdays, <b>10</b>)
 set @deldeepdays = isnull(@deldeepdays, <b>1</b>)

 set @datetime = replace(replace(convert(varchar, getdate(), <b>120</b>), ' ', '_'), ':', '-')
 set @date = left(@datetime, <b>10</b>)
 set @bckproot = ltrim(rtrim(@bckproot))
 if right(@bckproot, <b>1</b>) <> '\' set @bckproot = @bckproot + '\'
 if right(@binpath, <b>1</b>) <> '\' set @binpath = @binpath + '\'
 
 set @bckpname = lower(@@servername) + '.' + lower(@db) + '.backup.' + @type
 set @bckpfilename = @datetime + '.' + @bckpname
 set @bckpfullname = @bckproot + @bckpfilename

 --Создаем блокировку от копирования на время создания бэкапа
 set @cmd = 'echo 1 > "' + @bckpfullname + '.lock"'
 exec @result = master.._step_help_Консольная_команда @cmd, @shelloutput output
 if @result <> <b>0</b> return -<b>1</b>

 if @type = 'full'
 begin
 backup database @db to disk = @bckpfullname
 if @@error <> <b>0</b> return -<b>1</b>
 restore verifyonly from disk = @bckpfullname
 if @@error <> <b>0</b> return -<b>1</b>
 end
 else if @type = 'diff'
 begin
 backup database @db to disk = @bckpfullname with differential
 if @@error <> <b>0</b> return -<b>1</b>
 restore verifyonly from disk = @bckpfullname
 if @@error <> <b>0</b> return -<b>1</b>
 end
 else
 begin
 backup log @db to disk = @bckpfullname
 if @@error <> <b>0</b> return -<b>1</b>
 restore verifyonly from disk = @bckpfullname
 if @@error <> <b>0</b> return -<b>1</b>
 end

 --Контрольная сумма файла бэкапа
 set @cmd = @binpath + 'exf.exe -md5 -d "' + @bckproot + '" -otf "' + @bckpfullname + '.md5" "' + @bckpfilename + '"'
 exec @result = master.._step_help_Консольная_команда @cmd, @shelloutput output
 if @result <> <b>0</b> return -<b>1</b>

 --Архивируем бэкап в фоновом режиме
 set @cmd = @binpath + 'rar.exe a -t -ep -idq -ri1 -rr3p -ilog"' + @bckpfullname + '.rarlog" "' 
 + @bckpfullname + '.rar" "' + @bckpfullname + '.md5" "' + @bckpfullname + '"'
 exec @result = master.._step_help_Консольная_команда @cmd, @shelloutput output
 if @result <> <b>0</b> return -<b>1</b>
 
 --Контрольная сумма архива файла бэкапа
 set @cmd = @binpath + 'exf.exe -md5 -d "' + @bckproot + '" -otf "' + @bckpfullname + '.rar.md5" "' + @bckpfilename + 

'.rar"'
 exec @result = master.._step_help_Консольная_команда @cmd, @shelloutput output
 if @result <> <b>0</b> return -<b>1</b>

 --Удаляем блокировку от копирования
 set @cmd = 'del "' + @bckpfullname + '.lock"'
 exec @result = master.._step_help_Консольная_команда @cmd, @shelloutput output
 if @result <> <b>0</b> return -<b>1</b>

 --Ищем первую дату, с которой начнем удалять старые бэкапы
 set datefirst <b>7</b>
 while @keepdays > <b>0</b>
 begin
 while power(<b>2</b>, datepart(dw, @deldate) - <b>1</b>) & @weekdays = <b>0</b> 
 set @deldate = dateadd(d, -<b>1</b>, @deldate)
 set @deldate = dateadd(d, -<b>1</b>, @deldate)
 set @keepdays = @keepdays - <b>1</b>
 end
 
 --Удаляем старые файлы бэкапов
 while @deldeepdays > <b>0</b> 
 begin
 set @date = left(convert(varchar, @deldate, <b>120</b>), <b>10</b>)
 set @cmd = 'del ' + @bckproot + @date + '*' + @bckpname + '*'
 exec @result = master.._step_help_Консольная_команда @cmd, @shelloutput output
 if @result <> <b>0</b> return -<b>1</b>

 set @deldeepdays = @deldeepdays - <b>1</b>
 set @deldate = dateadd(d, -<b>1</b>, @deldate)
 end

 return <b>0</b>
end
вспомогательная процедура консольной команды:
use master
go
drop procedure _step_help_Консольная_команда
go

use master
go
create procedure _step_help_Консольная_команда
 @cmd nvarchar(<b>4000</b>),
 @output nvarchar(<b>4000</b>) output
as
begin
 declare 
 @line nvarchar(<b>1000</b>),
 @msg nvarchar(<b>1000</b>),
 @result int
 
 if object_id('tempdb..#shelloutput') is not null drop table #shelloutput
 create table #shelloutput (id int identity(<b>1</b>,<b>1</b>), line nvarchar(<b>4000</b>))
 insert #shelloutput (line)
 exec @result = master..xp_cmdshell @cmd
 set @output = ''
 declare LC cursor local for 
 select line from #shelloutput order by id
 open LC
 fetch next from LC into @line
 while @@fetch_status = <b>0</b>
 begin
 set @output = @output + isnull(@line, '') + char(<b>13</b>) + char(<b>10</b>)
 fetch next from LC into @line
 end
 close LC
 deallocate LC

 if @result <> <b>0</b>
 begin
 set @msg = 'Команда завершилась с ошибкой:' + char(<b>13</b>) + char(<b>10</b>) 
 + @cmd + char(<b>13</b>) + char(<b>10</b>)
 + @output
 raiserror(@msg, <b>16</b>, <b>1</b>)
 end
 return @result
end
Процедура копирования:
use master 
go

drop procedure _step_Копирование_бэкапов
go

/*
 $Date: 2009-05-21 19:12:17 +0400 $
 $Rev: 381 $
 $Author: i1 $

 Копирует архивы бэкапов из одной папки в другую.
*/

create procedure _step_Копирование_бэкапов
 @src nvarchar(<b>500</b>), --Исходная папка
 @dst nvarchar(<b>500</b>) --Конечная папка
as 
begin
 set nocount on
 declare 
 @name varchar(<b>1000</b>),
 @cmd nvarchar(<b>1000</b>),
 @shelloutput nvarchar(<b>4000</b>),
 @result int,
 @msg nvarchar(<b>4000</b>)
 
 set @src = rtrim(ltrim(isnull(@src, '')))
 if @src = ''
 begin
 raiserror('Должна быть задана исходная папка.', <b>16</b>, <b>1</b>)
 return -<b>1</b>
 end
 if right(@src, <b>1</b>) <> '\' set @src = @src + '\'

 set @dst = ltrim(rtrim(isnull(@dst, '')))
 if @dst = ''
 begin
 raiserror('Должна быть задана конечная папка.', <b>16</b>, <b>1</b>)
 return -<b>1</b>
 end
 if right(@dst, <b>1</b>) <> '\' set @dst = @dst + '\'

 --Узнаем какие из бэкапов еще в процессе создания
 if object_id('tempdb..#bckp_copy_lock') is not null drop table #bckp_copy_lock
 create table #bckp_copy_lock(name varchar(<b>1000</b>))
 set @cmd = 'dir /b ' + @src + '*.lock'
 insert #bckp_copy_lock
 exec master..xp_cmdshell @cmd
 delete #bckp_copy_lock where name is null
 
 --Получаем список архивов и их сумм готовых к копированию
 if object_id('tempdb..#bckp_copy_file') is not null drop table #bckp_copy_file
 create table #bckp_copy_file(name varchar(<b>1000</b>))
 set @cmd = 'dir /b ' + @src + '*.rar*'
 insert #bckp_copy_file
 exec master..xp_cmdshell @cmd
 delete #bckp_copy_file where name is null
 
 delete #bckp_copy_file
 from #bckp_copy_file f
 join #bckp_copy_lock l on left(f.name, <b>19</b>) = left(l.name, <b>19</b>)
 
 delete #bckp_copy_file 
 where name like '%file not found%' or name like '%файл не найден%'
 
 --Переносим файлы в целевую папку
 declare CC cursor for
 select name from #bckp_copy_file order by name
 open CC
 fetch next from CC into @name
 while @@fetch_status = <b>0</b>
 begin
 set @cmd = 'xcopy /y /q /z ' + @src + @name + ' ' + @dst
 exec @result = master.._step_help_Консольная_команда @cmd, @shelloutput output
 if @result <> <b>0</b> return -<b>1</b>
 set @cmd = 'del ' + @src + @name
 exec @result = master.._step_help_Консольная_команда @cmd, @shelloutput output
 if @result <> <b>0</b> return -<b>1</b>
 fetch next from CC into @name
 end
 close CC
 deallocate CC

 return <b>0</b>
end
Примерный скрипт на создание jobа для бэкапа базы
/*
 $Date: 2009-05-21 20:30:32 +0400 $
 $Rev: 384 $
 $Author: i1 $
 
 Задание для регулярного запуска
 Бэкап базы
*/

begin transaction
 declare 
 @JobID binary(<b>16</b>), 
 @ReturnCode int,
 @crlf nvarchar(<b>2</b>),
 @job_category nvarchar(<b>100</b>),
 @job_name nvarchar(<b>256</b>),
 @job_descr nvarchar(<b>512</b>),
 @email_operator nvarchar(<b>100</b>),
 @owner_login_name nvarchar(<b>100</b>),
 @step_name nvarchar(<b>256</b>),
 @db_name nvarchar(<b>256</b>),
 @bckp_type nvarchar(<b>4</b>),
 @bckp_folder nvarchar(<b>256</b>),
 @bin_folder nvarchar(<b>256</b>),
 @keep_days int,
 @week_days *******,
 @del_deep_days int,
 @command nvarchar(<b>1024</b>),
 @msg nvarchar(<b>1024</b>)
 
 set @crlf = char(<b>13</b>) + char(<b>10</b>)
 set @job_category = N'Бэкап'
 set @job_name = N'Бэкап nevsky full'
 set @job_descr = N'Полный бэкап базы nevsky.'
 set @email_operator = N'i1'
 set @owner_login_name = N'NEVSKY\i1'
 set @bckp_type = N'full'
 set @bin_folder = N'f:\buhta\bin'
 set @keep_days = <b>5</b>
 set @week_days = <b>62</b>
 set @del_deep_days = <b>10</b>
 
 set @ReturnCode = <b>0</b> 
 if (select count(*) from msdb.dbo.syscategories where name = @job_category) < <b>1</b> 
 exec msdb.dbo.sp_add_category @name = @job_category

 -- Delete the job with the same name (if it exists)
 select @JobID = job_id 
 from msdb.dbo.sysjobs 
 where (name = @job_name) 
 if (@JobID is not null) 
 begin
 -- Check if the job is a multi-server job 
 if (exists (select * 
 from msdb.dbo.sysjobservers 
 where (job_id = @JobID) and (server_id <> <b>0</b>))) 
 begin 
 -- There is, so abort the script 
 set @msg = N'Unable to import job ''' + @job_name + ''' since there is already a multi-server job with this name.'
 raiserror (@msg, <b>16</b>, <b>1</b>) 
 goto QuitWithRollback 
 end 
 else 
 -- Delete the [local] job 
 exec msdb.dbo.sp_delete_job @job_name = @job_name 
 set @JobID = NULL
 end

begin 

 -- Add the job
 exec @ReturnCode = msdb.dbo.sp_add_job 
 @job_id = @JobID output, 
 @job_name = @job_name, 
 @owner_login_name = @owner_login_name, 
 @description = @job_descr, 
 @category_name = @job_category, 
 @enabled = <b>1</b>, 
 @notify_level_email = <b>2</b>, 
 @notify_level_page = <b>0</b>, 
 @notify_level_netsend = <b>0</b>, 
 @notify_level_eventlog = <b>2</b>, 
 @delete_level= <b>0</b>, 
 @notify_email_operator_name = @email_operator
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 -- Add the job steps
 set @step_name = N'Бэкап nevsky full'
 set @db_name = N'nevsky'
 set @bckp_folder = N'f:\buhta\bak\nevsky'

 set @command = 'exec master.._step_Бэкап_базы' + @crlf
 + ' @db = ''' + @db_name + ''',' + @crlf
 + ' @type = ''' + @bckp_type + ''',' + @crlf 
 + ' @bckproot = ''' + @bckp_folder + ''',' + @crlf
 + ' @binpath = ''' + @bin_folder + ''',' + @crlf
 + ' @keepdays = ' + cast(@keep_days as nvarchar) + ',' + @crlf
 + ' @weekdays = ' + cast(@week_days as nvarchar) + ',' + @crlf
 + ' @deldeepdays = ' + cast(@del_deep_days as nvarchar) + @crlf
 
 exec @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, 
 @step_id = <b>1</b>, @step_name = @step_name, @command = @command, 
 @database_name = N'master', @server = N'', @database_user_name = N'', 
 @subsystem = N'TSQL', @cmdexec_success_code = <b>0</b>, @flags = <b>0</b>, 
 @retry_attempts = <b>0</b>, @retry_interval = <b>1</b>, @output_file_name = N'', 
 @on_success_step_id = <b>0</b>, @on_success_action = <b>1</b>, @on_fail_step_id = <b>0</b>, 
 @on_fail_action = <b>2</b>
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 exec @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = <b>1</b> 
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 -- Add the job schedules
 exec @ReturnCode = msdb.dbo.sp_add_jobschedule 
 @job_id = @JobID, 
 @name = @job_name, 
 @enabled = <b>1</b>, 
 @freq_type = <b>8</b>, 
 @active_start_date = <b>20090509</b>, 
 @active_start_time = <b>30000</b>, 
 @freq_interval = @week_days, 
 @freq_subday_type = <b>1</b>, 
 @freq_subday_interval = <b>0</b>, 
 @freq_relative_interval = <b>0</b>, 
 @freq_recurrence_factor = <b>1</b>, 
 @active_end_date = <b>99991231</b>, 
 @active_end_time = <b>235959</b>
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 -- Add the Target Servers
 exec @ReturnCode = msdb.dbo.sp_add_jobserver 
 @job_id = @JobID, @server_name = N'(local)' 
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

end
commit transaction
goto EndSave 
QuitWithRollback:
 if (@@trancount > <b>0</b>) rollback transaction
EndSave:
Job регулярного копирования баз (тут копируются три базы каждые 2 часа):
/*
 $Date: 2009-05-21 20:04:45 +0400 $
 $Rev: 383 $
 $Author: i1 $
 
 Задание для регулярного запуска
 Бэкап базы
*/

begin transaction
 declare 
 @JobID binary(<b>16</b>), 
 @ReturnCode int,
 @crlf nvarchar(<b>2</b>),
 @job_category nvarchar(<b>100</b>),
 @job_name nvarchar(<b>256</b>),
 @job_descr nvarchar(<b>512</b>),
 @email_operator nvarchar(<b>100</b>),
 @owner_login_name nvarchar(<b>100</b>),
 @step_name nvarchar(<b>256</b>),
 @src nvarchar(<b>256</b>),
 @dst nvarchar(<b>256</b>),
 @command nvarchar(<b>1024</b>),
 @msg nvarchar(<b>1024</b>)
 
 set @crlf = char(<b>13</b>) + char(<b>10</b>)
 set @job_category = N'Бэкап'
 set @job_name = N'Копирование бэкапов'
 set @job_descr = N'Копирование бэкапов на другой сервер.'
 set @email_operator = N'i1'
 set @owner_login_name = N'NEVSKY\i1'
 
 set @ReturnCode = <b>0</b> 
 if (select count(*) from msdb.dbo.syscategories where name = @job_category) < <b>1</b> 
 exec msdb.dbo.sp_add_category @name = @job_category

 -- Delete the job with the same name (if it exists)
 select @JobID = job_id 
 from msdb.dbo.sysjobs 
 where (name = @job_name) 
 if (@JobID is not null) 
 begin
 -- Check if the job is a multi-server job 
 if (exists (select * 
 from msdb.dbo.sysjobservers 
 where (job_id = @JobID) and (server_id <> <b>0</b>))) 
 begin 
 -- There is, so abort the script 
 set @msg = N'Unable to import job ''' + @job_name + ''' since there is already a multi-server job with this name.'
 raiserror (@msg, <b>16</b>, <b>1</b>) 
 goto QuitWithRollback 
 end 
 else 
 -- Delete the [local] job 
 exec msdb.dbo.sp_delete_job @job_name = @job_name 
 set @JobID = NULL
 end

begin 

 -- Add the job
 exec @ReturnCode = msdb.dbo.sp_add_job 
 @job_id = @JobID output, 
 @job_name = @job_name, 
 @owner_login_name = @owner_login_name, 
 @description = @job_descr, 
 @category_name = @job_category, 
 @enabled = <b>1</b>, 
 @notify_level_email = <b>2</b>, 
 @notify_level_page = <b>0</b>, 
 @notify_level_netsend = <b>0</b>, 
 @notify_level_eventlog = <b>2</b>, 
 @delete_level= <b>0</b>, 
 @notify_email_operator_name = @email_operator
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 -- Add the job steps
 set @step_name = N'Копирование system'
 set @src = N'f:\buhta\bak\system'
 set @dst = N'\\spb01-srv003vmc\basa_neptun\system'
 
 set @command = 'exec master.._step_Копирование_бэкапов' + @crlf
 + ' @src = ''' + @src + ''',' + @crlf
 + ' @dst = ''' + @dst + '''' + @crlf 
 
 exec @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, 
 @step_id = <b>1</b>, @step_name = @step_name, @command = @command, 
 @database_name = N'master', @server = N'', @database_user_name = N'', 
 @subsystem = N'TSQL', @cmdexec_success_code = <b>0</b>, @flags = <b>0</b>, 
 @retry_attempts = <b>0</b>, @retry_interval = <b>1</b>, @output_file_name = N'', 
 @on_success_step_id = <b>0</b>, @on_success_action = <b>3</b>, @on_fail_step_id = <b>0</b>, 
 @on_fail_action = <b>2</b>
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 -- Add the job steps
 set @step_name = N'Копирование nevsky'
 set @src = N'f:\buhta\bak\nevsky'
 set @dst = N'\\spb01-srv003vmc\basa_neptun\nevsky'
 
 set @command = 'exec master.._step_Копирование_бэкапов' + @crlf
 + ' @src = ''' + @src + ''',' + @crlf
 + ' @dst = ''' + @dst + '''' + @crlf 
 
 exec @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, 
 @step_id = <b>2</b>, @step_name = @step_name, @command = @command, 
 @database_name = N'master', @server = N'', @database_user_name = N'', 
 @subsystem = N'TSQL', @cmdexec_success_code = <b>0</b>, @flags = <b>0</b>, 
 @retry_attempts = <b>0</b>, @retry_interval = <b>1</b>, @output_file_name = N'', 
 @on_success_step_id = <b>0</b>, @on_success_action = <b>3</b>, @on_fail_step_id = <b>0</b>, 
 @on_fail_action = <b>2</b>
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 -- Add the job steps
 set @step_name = N'Копирование tools'
 set @src = N'f:\buhta\bak\tools'
 set @dst = N'\\spb01-srv003vmc\basa_neptun\tools'
 
 set @command = 'exec master.._step_Копирование_бэкапов' + @crlf
 + ' @src = ''' + @src + ''',' + @crlf
 + ' @dst = ''' + @dst + '''' + @crlf 
 
 exec @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, 
 @step_id = <b>3</b>, @step_name = @step_name, @command = @command, 
 @database_name = N'master', @server = N'', @database_user_name = N'', 
 @subsystem = N'TSQL', @cmdexec_success_code = <b>0</b>, @flags = <b>0</b>, 
 @retry_attempts = <b>0</b>, @retry_interval = <b>1</b>, @output_file_name = N'', 
 @on_success_step_id = <b>0</b>, @on_success_action = <b>1</b>, @on_fail_step_id = <b>0</b>, 
 @on_fail_action = <b>2</b>
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 exec @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = <b>1</b> 
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 -- Add the job schedules
 exec @ReturnCode = msdb.dbo.sp_add_jobschedule 
 @job_id = @JobID, 
 @name = @job_name, 
 @enabled = <b>1</b>, 
 @freq_type = <b>4</b>, 
 @active_start_date = <b>20090509</b>, 
 @active_start_time = <b>00000</b>, 
 @freq_interval = <b>1</b>,
 @freq_subday_type = <b>8</b>, 
 @freq_subday_interval = <b>2</b>, 
 @freq_relative_interval = <b>0</b>, 
 @freq_recurrence_factor = <b>1</b>, 
 @active_end_date = <b>99991231</b>, 
 @active_end_time = <b>235959</b>
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

 -- Add the Target Servers
 exec @ReturnCode = msdb.dbo.sp_add_jobserver 
 @job_id = @JobID, @server_name = N'(local)' 
 if (@@error <> <b>0</b> or @ReturnCode <> <b>0</b>) goto QuitWithRollback 

end
commit transaction
goto EndSave 
QuitWithRollback:
 if (@@trancount > <b>0</b>) rollback transaction
EndSave:
Все это живет уже второй месяц и не падает :)вспомогательные программы - rar.exe из комплекта winraraи exf.exe с exactfile.comПри recovery model = full можно настроить так что хранится полная последовательность бэкаповНу и у SQLAgenta должны быть нужные права
19 ответов

i2akai1

i2akai1,А в чем проблема использовать стандартный maintenance plan? Он тоже все это позволяет делать. И, даже, затирать старые версии бэкапов...


i2akai1

Dan Khatskevich,Во-первых на сетевых дисках проблема с местом - нужно архивированиеВо-вторых мэйнтэнс план не умеет делать diff бэкапыВ-третьих в EM в списке joboв красиво отображается категория - 'Бэкап базы' :)


i2akai1

> 3. Проверяет целостность бэкапа средствами SQL2000 (restore verifyonly)под 2000 эта команда ничего не проверяет. только наличие самих файлов и загголовков в файлах. сами попробуйте - убейте почти весь файл - команда вернет ок. ваще по легенте проверяет просто комплектность "бакапсета"> 4. Вычисляет md5 получившегося файла бэкапа и кладет его рядом> 5. Архивирует раром в фоновом режиме бэкап и md5> 6. Вычисляет md5 получившегося архива и кладет его рядомзачем отдельно мд5? сам по себе архив это делает. имхо избыточно. а с применением лайтспида или с переходом на 2008 - теряет всякий смысл> При этом удаляет старые бэкапы чтобы осталось свежие бэкапы за указанное количество днейа вот тут огромадный вопрос в порядке действий> Саздает на время создания бэкапа и его архивирование lock файл чтобы процедура копированияслучайно не начала копировать незавершенный бэкапа копирование отвалится в таком случае со вполне вменяемым сообщением / кодом ошибки


i2akai1

i2akai1,вообще я бы с этим поспорил. Да не буду, каждому свое:))


i2akai1

> 3. Проверяет целостность бэкапа средствами SQL2000 (restore verifyonly)под 2000 эта команда ничего не проверяет. только наличие самих файлов и загголовков в файлах. сами попробуйте - убейте почти весь файл - команда вернет ок. ваще по легенте проверяет просто комплектность "бакапсета"
лишняя проверка даже если проверяется один заголовок не помешает имхо :)
зачем отдельно мд5? сам по себе архив это делает. имхо избыточно. а с применением лайтспида или с переходом на 2008 - теряет всякий смысл
просто :) можно не делать :) а 2008-го нет рабочий 2000-й и переход не планируется :)
> При этом удаляет старые бэкапы чтобы осталось свежие бэкапы за указанное количество днейа вот тут огромадный вопрос в порядке действий
что за вопрос?
> Саздает на время создания бэкапа и его архивирование lock файл чтобы процедура копированияслучайно не начала копировать незавершенный бэкапа копирование отвалится в таком случае со вполне вменяемым сообщением / кодом ошибки
xcopy у меня например начинает копировать создающийся архив и не прекращает пока не упрется внеожиданный конец файла если архивирование еще не завершеноа если архивы размером гига по 4 - 5 (сам фулл бэкап весит 16Г) то лишний раз гонять ******** сети бессмысленно


i2akai1

2 i2akai1у мну просто свой вариант. слегка легче и с поддержкой лайтспида и прочего :) так что вопросы имеют место> лишняя проверка даже если проверяется один заголовок не помешает имхо :)она дает "ок" на битый бакап. а наличие файла ты и так проверишь. так что лишнее телодвижение однозначно. мы даже кейс открывали по этому поводу>> а вот тут огромадный вопрос в порядке действий> что за вопрос? что сначала. удалять или бакапить. и однозначного ответа на самом деле нет> xcopy у меня напримервот поэтому я пользую copy :) xcopy, попробовав, отложил


i2akai1

> А в чем проблема использовать стандартный maintenance plan?он, к сожалению, становится все хуже и хуже от версии к версиик примеру, под 2005 получить протокол реиндексации невозможно, причину сбоя надо "выкапывать", при сбое реиндекса одной таблицы слетает все задание - куда это годится? с бакапами - схожие вопросы


i2akai1

у мну просто свой вариант. слегка легче и с поддержкой лайтспида и прочего :) так что вопросы имеют место
посмотрел сейчас про litespeed - видимо хорошая штука :)
она дает "ок" на битый бакап. а наличие файла ты и так проверишь. так что лишнее телодвижение однозначно. мы даже кейс открывали по этому поводу
хм... у меня просто не было на моей памяти битых бэкапов (тьфу-тьфу) так что не задумывался по этому поводу, и про "ок" не знал, но можно выкинуть проверку и все :)
что сначала. удалять или бакапить. и однозначного ответа на самом деле нет
по моему все просто - к тому времени как бэкап удаляется, его архивированная копия уже 5 днейкак лежит на сетевом диске :) можно поподробнее что за проблема (имхо надо сначала бэкапить а потом удалять старое)
вот поэтому я пользую copy :) xcopy, попробовав, отложил
во-первых (сейчас освежил в памяти) у copy нет ключа /q (можно конечно > nul но тогда я про это не подумал)во-вторых пустил сейчас архивироваться файл 10Г подождал пока архив до 70М вырастети запустил copy /z 1.test \\srv\sharecopy спокойно начала копировать и не остановилась до тех пор пока не дошла до 190% от размера файла... даже не написала ничего что мол ошибка :)а файл тем временем продолжал расти и сейчаc растет :)


i2akai1

> посмотрел сейчас про litespeed - видимо хорошая штука :)а времени-то экономит!> хм... у меня просто не было на моей памяти битых бэкапова тестировать надо именно на битых> по моему все просто - к тому времени как бэкап удаляется, его архивированная копия уже 5 днейэто если 5! а если места под 2? или под 1?> во-вторых пустил сейчас архивироваться файла вот на конфликты с архивацией - не тестил


i2akai1

Crimean,про место под 1 бэкап - это уже экстрим :)но и в этом случае - бэкап, копирование туда где есть место, на следующий день - удаление старого на машине с сиквелом и опять бэкап :)про лайтспид - своими руками для меня все-же интереснее да и времени не слишком много ушло :)


i2akai1

> про лайтспид - своими руками для меня все-же интереснее да и времени не слишком много ушлоя не про то. смысл лайтспида (и не только его, несколько продуктов есть аналогичных) в том, что на диск пишется уже "зажатый" бакап. в результате экономим и оч сильно и время и место. при ресторе не так разительно, но тоже лучше


i2akai1

Crimean,Прикольно :)


i2akai1

Crimean,у нас пока запас прочности (мощность сиквел серверов, объемы дисков) позволяет обходиться без дополнительной оптимизации интересно конечно как лайт спид это делает (использует стандартное апи сервера?заставляет сервер думать что он бэкапит на диск и подсовывает ему оперативку?)но это уже оффтоп :)


i2akai1

> интересно конечно как лайт спид это делаетя не думаю, что это уж совсем оффтоп. и решений было несколько, я думаю, можно найти по этому форуму и посравнивать при желаниив любом случае в 2008 уже эту тему "прикрыли" :)


i2akai1

в любом случае в 2008 уже эту тему "прикрыли" :)
В 2008 эту тему "прикрыли" только для ентерпрайз версии.Наше руководство отказывается платить такие баблищи только из-за сжатия бакапов :-(


i2akai1

В 2008 эту тему "прикрыли" только для ентерпрайз версии.Наше руководство отказывается платить такие баблищи только из-за сжатия бакапов :-(
ээ.. если посмотреть на отличия EE от обычной версии, то там в общем-то есть за что платить и кроме сжатия бакапов.. хотя при относительно "мелких" (по сравнению с доступным оборудованием) объемах, действительно, все это неактуально


i2akai1

Добавлю и эту процу раз уж написал ее
/*
 $Date: 2009-11-18 21:42:39 +0300 (РЎСЂ, 18 РЅРѕСЏ 2009) $
 $Rev: 409 $
 $Author: i1 $

 Удаление старых файлов бэкапов на сетевом диске
 сделанных процедурой _step_Бэкап_базы и скопированных туда
 процедурой _step_Копирование_бэкапов.
 
 Например:
 для базы nevsky делаются ежедневно:
 утром full бэкап
 в течение дня несколько diff и tlog бэкапов
 все эти файлы рано или поздно оказываются на сетевом диске
 стоит задача хранить на сетевом диске
 1. все типы бэкапов за последние 20 дней когда они делались
 (если они делались по будням то это будет за 4 недели)
 чтобы можно было восстановить на любую секунду за эти 20 дней
 2. ранее этих 20 дней хранить 8 полных бэкапов сделанных в начале недели
 то есть начиная с 5-й по 12-ю неделю в глубь истории можно восстановить
 базу на начало недели
 3. еще дальше в глубь истории хранить полные бэкапы на начало месяца
 в течение 12 месяцев
 4. остальные накапливающиеся бекапы удалять чтобы не тратить место
 на сетевом диске
 
 этого можно добиться если ежедневно делать 3 вызова этой процедуры
 при помощи SQL Server Agent:
 
	--удаление полных бэкапов
	exec master.._step_Удаление_старого
	 @srv = 'neptun',
	 @db = 'nevsky',
	 @type = 'full',
	 @path = '\\spb01\neptun\nevsky',
	 @daily = 20,
	 @weekly = 8,
	 @monthly = 12
	 
	--удаление дифференциальных бэкапов
	exec master.._step_Удаление_старого
	 @srv = 'neptun',
	 @db = 'nevsky',
	 @type = 'diff',
	 @path = '\\spb01\neptun\nevsky',
	 @daily = 20,
	 @weekly = 0,
	 @monthly = 0
	 
	--удаление бэкапов логов
	exec master.._step_Удаление_старого
	 @srv = 'neptun',
	 @db = 'nevsky',
	 @type = 'tlog',
	 @path = '\\spb01\neptun\nevsky',
	 @daily = 20,
	 @weekly = 0,
	 @monthly = 0
 
 ЕСЛИ ЗАПУСТИТЬ ПРОЦЕДУРУ С ПОСЛЕДНИМИ ПАРАМЕТРАМИ УСТАНОВЛЕННЫМИ В 0
 ТО МОЖНО ЛИШИТЬСЯ ВСЕХ БЭКАПОВ :)
*/

create procedure _step_Удаление_старого
 @srv nvarchar(<b>100</b>),
 @db nvarchar(<b>100</b>),
 @type nvarchar(<b>4</b>), --full diff tlog
 @path nvarchar(<b>500</b>), --откуда удалять
 @daily int, --сколько файлов последних ежедневных бэкапов не считая текущего хранить
 @weekly int, --после ежедневных в прошлое сколько недельных файлов бэкапов хранить
 @monthly int --после недельных в прошлое сколько месячных файлов бэкапов хранить
as 
begin
 set nocount on
 declare 
 @date datetime,
 @date_str nvarchar(<b>10</b>),
 @d1_str nvarchar(<b>10</b>),
 @d2_str nvarchar(<b>10</b>),
 @deldate datetime,
 @cmd nvarchar(<b>1000</b>),
 @bckpname nvarchar(<b>1000</b>),
 @cname nvarchar(<b>1000</b>),
 @result int,
 @shelloutput nvarchar(<b>4000</b>), 
 @msg nvarchar(<b>4000</b>),
 @line nvarchar(<b>4000</b>)

 set @type = isnull(@type, '')
 if @type not in ('full', 'diff', 'tlog')
 begin
 raiserror('@type должен быть full, diff или tlog.', <b>16</b>, <b>1</b>)
 return -<b>1</b>
 end

 if isnull(@srv, '') = '' set @srv = lower(@@servername)
 set @srv = replace(@srv, '\', '-')
 set @db = lower(@db)
 set @deldate = dateadd(d, -<b>1</b>, getdate())
 set @daily = isnull(@daily, <b>10</b>)
 set @weekly = isnull(@weekly, <b>8</b>)
 set @monthly = isnull(@monthly, <b>120</b>)

 set @date = getdate()
 set @date_str = left(convert(varchar, @date, <b>120</b>), <b>10</b>)
 set @path = ltrim(rtrim(@path))
 if right(@path, <b>1</b>) <> '\' set @path = @path + '\'
 
 set @bckpname = @srv + '.' + @db + '.backup.' + @type

 if object_id('tempdb..#dir') is not null drop table #dir
 create table #dir (name nvarchar(<b>1000</b>))
 if object_id('tempdb..#keep') is not null drop table #keep
 create table #keep (name nvarchar(<b>1000</b>))
 if object_id('tempdb..#del') is not null drop table #del
 create table #del (name nvarchar(<b>1000</b>))

 set @cmd = 'dir /b ' + @path + '*' + @bckpname + '.rar'
 insert #dir
 exec master..xp_cmdshell @cmd
 delete #dir 
 where name is null 
 or name like '%file not found%' 
 or name like '%файл не найден%'
 
 --Узнаем какие из бэкапов еще в процессе создания
 if object_id('tempdb..#lock') is not null drop table #lock
 create table #lock(name nvarchar(<b>1000</b>))
 set @cmd = 'dir /b ' + @path + '*' + @bckpname + '*.lock'
 insert #lock
 exec master..xp_cmdshell @cmd
 delete #lock 
 where name is null
 or name like '%file not found%' 
 or name like '%файл не найден%'

 delete #dir
 from #dir d
 join #lock l on left(d.name, <b>19</b>) = left(l.name, <b>19</b>)
 
 delete #dir
 where left(name, <b>10</b>) = @date_str
 
 declare C cursor for
 select distinct left(name, <b>10</b>) 
 from #dir order by left(name, <b>10</b>) desc
 open C
 fetch next from C into @cname
 while @@fetch_status = <b>0</b> and @daily > <b>0</b>
 begin
 set @daily = @daily - <b>1</b>
 insert #keep
 select name from #dir where left(name, <b>10</b>) = @cname
 fetch next from C into @cname
 end
 close C
 deallocate C
 
 if @@fetch_status <> <b>0</b> goto delfiles
 --если остановились в середине недели то до начала недели все сохраняем тоже
 set @date = convert(datetime, @cname, <b>120</b>)
 set datefirst <b>1</b>
 while datepart(dw, @date) <> <b>7</b>
 begin
 insert #keep
 select name from #dir 
 where left(name, <b>10</b>) = left(convert(nvarchar, @date, <b>120</b>), <b>10</b>)
 set @date = @date - <b>1</b>
 end
 
 --недельные бэкапы
 set @date = @date - datepart(dw, @date) + <b>1</b>
 
 while @weekly > <b>0</b> or (@weekly <= <b>0</b> and month(@date) = month(@date + <b>6</b>))
 begin
 set @d1_str = left(convert(nvarchar, @date, <b>120</b>), <b>10</b>)
 set @d2_str = left(convert(nvarchar, @date + <b>6</b>, <b>120</b>), <b>10</b>)
 set @cname = ''
 select top <b>1</b> @cname = left(name, <b>10</b>)
 from #dir
 where left(name, <b>10</b>) between @d1_str and @d2_str
 order by left(name, <b>10</b>) asc
 while @cname = '' and exists(select * from #dir where name < @d1_str)
 begin
 set @date = @date - <b>7</b>
 set @d1_str = left(convert(nvarchar, @date, <b>120</b>), <b>10</b>)
 set @d2_str = left(convert(nvarchar, @date + <b>6</b>, <b>120</b>), <b>10</b>)
 set @cname = ''
 select top <b>1</b> @cname = left(name, <b>10</b>)
 from #dir
 where left(name, <b>10</b>) between @d1_str and @d2_str
 order by left(name, <b>10</b>) asc
 end
 if @cname = '' goto delfiles
 insert #keep
 select name from #dir where left(name, <b>10</b>) = @cname
 set @date = @date - <b>7</b>
 set @weekly = @weekly - <b>1</b>
 end
 
 --бэкап на начало месяца в завершение недельных бэкапов
 set @d2_str = left(convert(nvarchar, @date + <b>6</b>, <b>120</b>), <b>10</b>)
 set @d1_str = left(@d2_str, <b>8</b>) + '01'
 set @cname = ''
 select top <b>1</b> @cname = left(name, <b>10</b>)
 from #dir
 where left(name, <b>10</b>) between @d1_str and @d2_str
 order by left(name, <b>10</b>) asc
 --print cast(@date as varchar) + ', ' + @d1_str + ', ' + @d2_str + ', ' + @cname
 if @cname <> ''
 insert #keep
 select name from #dir where left(name, <b>10</b>) = @cname
 
 
 --месячные бэкапы
 set @date = dateadd(m, -<b>1</b>, convert(nvarchar, @d1_str, <b>120</b>))
 while @monthly > <b>0</b>
 begin
 set @cname = ''
 select top <b>1</b> @cname = left(name, <b>10</b>)
 from #dir
 where name like left(convert(nvarchar, @date, <b>120</b>), <b>8</b>) + '%'
 order by left(name, <b>10</b>)
 if @cname = '' goto delfiles
 insert #keep
 select name from #dir where left(name, <b>10</b>) = @cname
 set @date = dateadd(m, -<b>1</b>, @date)
 set @monthly = @monthly - <b>1</b>
 end
 
 
 delfiles:
 
 declare D cursor for
 select d.name 
 from #dir d where d.name not in (select name from #keep)
 open D
 fetch next from D into @cname
 while @@fetch_status = <b>0</b>
 begin
 set @cmd = 'del ' + @path + @cname + '*'
 --print @cmd
 exec master..xp_cmdshell @cmd, no_output
 fetch next from D into @cname
 end
 close D
 deallocate D
 --select * from #keep
 return <b>0</b>
end
безошибочную работу не гарантирую, но по крайней мере у меня работает в течение второй недели на 3-х серверах, тобишь я ей доверяю :)


i2akai1

Автор,а если название базы с пробелом вылетает с ошибкой, где можно это поправить?


i2akai1

Автор,а если название базы с пробелом вылетает с ошибкой, где можно это поправить?
Заключить его в квадратные скобки.