This stored proc performs transaction dumps, or database dumps for a
specified database. It is used in the script below.
use master
go
create proc sp_syb_maint (@dbname varchar(30),
@fpath varchar(50),
@mode varchar(15)) as
declare @fname1 varchar(50),
@fname2 varchar(50),
@fdate varchar(12),
@fdate1 varchar(12),
@fdate2 varchar(12),
@date1 datetime,
@msg varchar(80),
@char1 char(1),
@dbprefix char(3)
if (@mode = 'dbcc')
return
select @fpath = rtrim(@fpath)
select @char1 = right(@fpath,1)
if (@char1 != char(47))
select @fpath = @fpath + char(47)
select @date1 = getdate()
select @fdate1 = convert(varchar(12),@date1,112),
@fdate2 = convert(varchar(12),@date1,108)
select @fdate =
substring(@fdate1,5,4) +
substring(@fdate2,1,2) +
substring(@fdate2,4,2)
select @dbprefix = substring(@dbname,1,3)
select @fname1 = @fpath + 'log_' + @dbprefix + @fdate + '.dmp'
select @fname2 = @fpath + 'db_' + @dbprefix + @fdate + '.dmp'
if ((@mode = 'dump') or (@mode = 'tran_only')) and
charindex(@dbname,'master-model-tempdb-sybsystemprocs')=0
begin
select @msg = '*** Dumping transaction log to ' + @fname1
print @msg
dump tran @dbname to @fname1
end
if (@mode = 'dump') and
charindex(@dbname,'model-tempdb-sybsystemprocs')=0
begin
select @msg = '*** Dumping database to ' + @fname2
print @msg
dump database @dbname to @fname2
end
return
|
|
