USE [Operations]
GO
/****** Object: StoredProcedure [dbo].[db_backups] Script Date: 04/05/2011 15:13:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec db_backups 'model'
CREATE procedure [dbo].[db_backups]
@db_name varchar(50)
AS
-->declare working variables
DECLARE
@db nvarchar(255),
@cleanup varchar(4000),
@device nvarchar(4000),
@ddevice nvarchar(4000),
@backup varchar(4000),
@bkp_path varchar(1000),
@bkp_device varchar(1000),
@bkp_l_device varchar(2000),
@bkp_sql nvarchar(4000)
-->@bkp_l_device default setting
set @bkp_l_device=null
-->bkp file cleaner
declare cleaner cursor for select bkp_partition_path from operations.dbo.ADMIN_db_bkp_meta BM
inner join operations.dbo.ADMIN_db_bkp_partitions BP on BM.bkp_partition_id=BP.bkp_partition_id
where [db_name] =(''+@db_name+'')
order by 1
OPEN cleaner
FETCH NEXT FROM cleaner INTO @bkp_path
WHILE @@FETCH_STATUS = 0
BEGIN
-->purge old backup files. full purge, no dynamic bkp file names
SET @cleanup='del /Q ' + '"'+@bkp_path+''+ @db_name + '*.bak"'
PRINT @cleanup
exec master..xp_cmdshell @cleanup
FETCH NEXT FROM cleaner INTO @bkp_path
END
CLOSE cleaner
DEALLOCATE cleaner
-->build backup device(s)
declare devices cursor for select BP.bkp_partition_id from operations.dbo.ADMIN_db_bkp_meta BM
inner join operations.dbo.ADMIN_db_bkp_partitions BP on BM.bkp_partition_id=BP.bkp_partition_id
where BM.[db_name] =(''+@db_name+'')
order by 1
OPEN devices
FETCH NEXT FROM devices INTO @bkp_device
WHILE @@FETCH_STATUS = 0
BEGIN
-->build bkp devices
set @bkp_path=(select bkp_partition_path from operations.dbo.ADMIN_db_bkp_partitions BP
where bkp_partition_id=@bkp_device)
SET @device='exec master..sp_addumpdevice ''disk'', ''bkp_'+@db_name+'_device_'+@bkp_device+''', '''+@bkp_path+''+@db_name++@bkp_device+'.bak'''--+@bkp_device+
print @device
SET @bkp_l_device = --N'EXECUTE ' + @proc_name
CASE WHEN @bkp_l_device IS NOT NULL
THEN @bkp_l_device + ',' + 'bkp_'+@db_name+'_device_'+@bkp_device+''
ELSE 'bkp_'+@db_name+'_device_'+@bkp_device+''
END
exec sp_executesql @device
FETCH NEXT FROM devices INTO @bkp_device
END
CLOSE devices
DEALLOCATE devices
print @bkp_l_device
-->backup the database
set @bkp_sql='BACKUP DATABASE ['+@db_name+'] TO
'+@bkp_l_device+' WITH NOFORMAT, INIT,
NAME = N'''+@db_name+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
print @bkp_sql
exec sp_executesql @bkp_sql
-->drop backup device(s)
declare ddevices cursor for select BP.bkp_partition_id from operations.dbo.ADMIN_db_bkp_meta BM
inner join operations.dbo.ADMIN_db_bkp_partitions BP on BM.bkp_partition_id=BP.bkp_partition_id
where BM.[db_name] =(''+@db_name+'')
order by 1
OPEN ddevices
FETCH NEXT FROM ddevices INTO @bkp_device
WHILE @@FETCH_STATUS = 0
BEGIN
-->drop bkp devices
SET @ddevice='exec sp_dropdevice bkp_'+@db_name+'_device_'+@bkp_device+''
print @ddevice
exec sp_executesql @ddevice
FETCH NEXT FROM ddevices INTO @bkp_device
END
CLOSE ddevices
DEALLOCATE ddevices
/*scripts for meta tables
CREATE TABLE operations.[dbo].[ADMIN_db_bkp_meta](
[db_name] [varchar](50) NOT NULL,
[bkp_partition_id] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE operations.[dbo].[ADMIN_db_bkp_partitions](
[bkp_partition_id] [int] NOT NULL,
[bkp_partition_path] [varchar](max) NOT NULL
) ON [PRIMARY]
insert ADMIN_db_bkp_partitions
(bkp_partition_id,bkp_partition_path)
values(1,
'\\HQFINSQL90\E$\backup\')
insert ADMIN_db_bkp_meta
(db_name,bkp_partition_id)
values ('model','1')
*/
GO
