db backups

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