Home

Stored Procedure Backup.

Estou postando uma store procedure de Backup.
Essa store procedure tem que ser salva na base master.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spBackup]    Script Date: 03/04/2008 10:23:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
--Para Executar a proc(Caminho,BASE,NºDias que vai armazear
ALTER PROCEDURE [dbo].[spBackup](@PATH VARCHAR(500), @DATA_BASE VARCHAR(100), @NDIAS INT)
--WITH ENCRYPTION
AS

SET NOCOUNT ON

--[ DECLARAÇÃO DAS VARIAVÉIS ]
DECLARE @FileName VARCHAR(500)
DECLARE @FileSystem INT
DECLARE @DATA_CRIAR VARCHAR(40)
DECLARE @DATA_BASE_DEVICE VARCHAR(70)
DECLARE @DATA_BASE_CAMINHO VARCHAR(150)
DECLARE @Contador int 
DECLARE @NumUltimoBkp VARCHAR(3)

--[ CRIA TABELA TEMPORARIA ]
CREATE TABLE #DIR(
 [id] [int] IDENTITY(1,1) NOT NULL,
 [filename] VARCHAR(200) NOT NULL,
 [depth] int NOT NULL,
 [file] int NOT NULL
)

INSERT #DIR EXEC  Master.dbo.xp_dirtree @PATH,1,1

Select @NumUltimoBkp = (Select IsNull(MAX(SUBSTRING([filename],1,2)),0) From #DIR)

If(@NumUltimoBkp = 0)
 Begin
  Set @NumUltimoBkp = 1
 End
else
 Begin
  Set @NumUltimoBkp = @NumUltimoBkp + 1
 End

--[ SETA UM VALAOR PARA AS VARIAVÉIS ]
set @NumUltimoBkp  = RTRIM(REPLICATE('0',2-LEN(@NumUltimoBkp)) + CONVERT(CHAR, @NumUltimoBkp))
SET @DATA_CRIAR = @NumUltimoBkp
SET @DATA_BASE_CAMINHO = @PATH+@DATA_CRIAR+'_'+@DATA_BASE+'.bak'
SET @DATA_BASE_DEVICE = @DATA_BASE+'_DEVICE'

BEGIN

 --[ VERIFICA SE JÁ EXISTE UM DEVICE PARA O BANCO, SE SIM APAGA ]
 IF EXISTS (SELECT NAME FROM master.dbo.sysDevices WHERE NAME = @DATA_BASE_DEVICE)
  EXEC  Master.dbo.SP_DROPDEVICE @DATA_BASE_DEVICE
 
 
 --[ CRIA UM DEVICE PARA O BANCO ]
  EXEC  Master.dbo.sp_addumpDevice 'DISK', @DATA_BASE_DEVICE, @DATA_BASE_CAMINHO
 
 
 --[ FAZ O BACKUP DO BANCO ]
  BACKUP DATABASE @DATA_BASE TO @DATA_BASE_DEVICE
 
 
 --[ APAGA O DEVICE DO BANCO ]
  EXEC  Master.dbo.sp_dropDevice @DATA_BASE_DEVICE

 --####################################################
 --[ APAGA OS BACKUPS ANTIGOS ]

  EXEC Master.dbo.sp_OACreate 'Scripting.FileSystemObject', @FileSystem OUT

  Select @Contador = (Select Count(id) From #DIR)

  IF @Contador >= @NDIAS
   Begin
    DECLARE ELIMINA CURSOR FOR
    select Top((@Contador + 1) - @NDIAS) [filename] from #dir where [file]=1 and [depth]=1
    and right([filename],3) = 'bak'
    --and id between 1 and @NDIAS
    Order by SUBSTRING([filename],1,2) asc

    OPEN ELIMINA
    FETCH NEXT FROM ELIMINA INTO @FileName
    WHILE @@FETCH_STATUS = 0
    BEGIN
      set  @FileName = @PATH+@FileName
      EXEC Master.dbo.sp_OAMethod @FileSystem, 'DeleteFile', NULL, @FileName

      FETCH NEXT FROM ELIMINA INTO @FileName
    END
    CLOSE ELIMINA
    DEALLOCATE ELIMINA
    EXEC Master.dbo.sp_OADestroy @FileSystem
   End
  DROP TABLE #DIR

 --#########################################

END

SET NOCOUNT ON

Imprimir

Enviar para um amigo

Dê sua opinião