BACKUP CUSTOMIZADO

2 de dezembro de 2008 Emanuel Peixoto Deixe um comentário

Devido a existência de dúvidas constantes com procedimentos de backup no fórum MSDN e TECHNET, então, resolvi criar uma rotina customizada de backup para compartilhar com nossa comunidade de SQL Server.

O código, abaixo, avalia os arquivos existentes no local de armazenamento do backup e, de acordo com o período de retenção(permanência) dos backups, os arquivos que estiverem fora do escopo de retenção serão removidos e, em seguida, é iniciada a rotina de backup, sendo possível definir que tipo de backup será realizado (full, diferencial, log).

O exemplo, abaixo, reproduz uma das formas de realização de backups, podendo ser melhorada ou apenas servir de base para novas procedimentos.

Para usar o exemplo, basta executar o script abaixo para criar a procedure "PR_BACKUP" e chamá-lo por um job. No final do script existe um exemplo de como executar a procedure para a realização de backups.

/*#### CRIA PROCEDURE DE BACKUP CUSTOMIZADO ####*/

use

master

exists(select 1 from sysobjects where name = ‘pr_backup’)

drop

procedure pr_backup

create

proc pr_backup

@database

varchar(50), — Informe o database para backup

@localbackup

varchar(500) = ‘C:’ , — Caminho default de backup, caso não seja declarado na chamada da procedure.

@tipoBackup

INT = 1, — Seleciona o tipo de backup. 1 = FULL , 2 = DIFERENCIAL , 3 = LOG

@retencao

int = 30

/* DECLARA VARIÁVEIS DA ROTINA */

create

table #backups (arquivos varchar(500))

declare

@data varchar(12)

declare

@cmd varchar(500) — Número de dias que serão ignorados os backups anteriores

declare

@tipoBackup_name varchar(15)

declare

@dataremocao varchar(12)

set

@dataremocao=CONVERT(varchar(12),dateadd(day,- @retencao,getdate()),112)

@tipoBackup = 1

begin

SET @tipoBackup_name = ‘Full’

END

@tipoBackup = 2

begin

SET @tipoBackup_name = ‘Diferencial’

END

@tipoBackup = 3

begin

SET @tipoBackup_name = ‘Log’

END

— ROTINA REMOVE BACKUP MAIOR QUE X DIAS

set

@cmd

= ‘exec xp_cmdshell ‘+ ””+‘dir ‘+@localbackup+@database+‘-‘ +@tipoBackup_name+‘*.bkp’+””

insert

into

#backups

exec

(

@cmd)

declare

@arq

varchar(100)

declare

arq

cursor for

select

substring(substring(arquivos,charindex(@database,arquivos)+LEN(@database)+LEN(@tipoBackup_name)+1,(LEN(arquivos)-(charindex(@database,arquivos)+LEN(@tipoBackup_name)))),1,12) as arquivos from #backups

where

(

charindex(@database,arquivos)<>0 and charindex(@database,arquivos) is not null)

and

convert

(datetime,substring(substring(arquivos,charindex(@database,arquivos)+LEN(@database)+LEN(@tipoBackup_name)+1,(LEN(arquivos)-(charindex(@database,arquivos)+LEN(@tipoBackup_name)))),1,8)) <= @dataremocao

open

arq

fetch

next from arq into @arq

while

@@FETCH_STATUS = 0

begin

set

@cmd=‘xp_cmdshell ”del ‘+@localbackup+ @database+‘-‘+@tipoBackup_name+@arq+‘.bkp”’

EXEC

(@cmd)

fetch

next from arq into @arq

end

arq

deallocate

arq

/* ROTINA REALIZA BACKUP */

@tipoBackup = 1

begin

SET @tipoBackup_name = ‘Full’

set @data=CONVERT(varchar(14),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),‘:’,”)

set @cmd = ‘Backup database ‘+@database + ‘ to disk = ‘+””+@localbackup+”+@database+‘-‘+ @tipoBackup_name +@data+‘.bkp’+””

exec (@cmd)

print ”

print ‘Backup do database ‘+ @database + ‘Realizado em ‘+@localbackup+”+@database+‘-‘+@tipoBackup_name +@data+‘.bkp’

END

@tipoBackup = 2

begin

SET @tipoBackup_name = ‘Diferencial’

set @data=CONVERT(varchar(14),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),‘:’,”)

set @cmd = ‘Backup database ‘+@database + ‘ to disk = ‘+””+@localbackup +” +@database+‘-‘+ @tipoBackup_name +@data+‘.bkp’+”’ WITH DIFFERENTIAL’

exec (@cmd)

print ”

print ‘Backup do database ‘+ @database + ‘Realizado em ‘+@localbackup+” +@database+‘-‘+@tipoBackup_name +@data+‘.bkp’

END

@tipoBackup = 3

begin

SET @tipoBackup_name = ‘Log’

set @data=CONVERT(varchar(14),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),‘:’,”)

set @cmd = ‘Backup LOG ‘+@database + ‘ to disk = ‘+””+@localbackup+” +@database+‘-‘+ @tipoBackup_name +@data+‘.bkp’+””

exec (@cmd)

print ”

print ‘Backup do LOG do database ‘+ @database + ‘ Realizado em ‘+@localbackup+” +@database+‘-‘+@tipoBackup_name +@data+‘.bkp’

END

/*#### EXEMPLO DE COMO EXECUTAR PROCEDURE DE BACKUP ####*/

exec

pr_backup

@database = ‘teste’, — Nome do database

@localbackup = ‘D:’, –Caso não seja declarado, o local do backup será em "C:"

@tipoBackup = 1, — 1 = Full, 2 = Diferencial, 3 = Log

@retencao = 7 –Dias de retenção de backups, caso esse parâmetro seja omitido o padrão é 30 dias.

Observações:

Para backups diferenciais e de log, é necessário configurar o recovery model do banco para bulk-logged ou full.

Até a próxima….

Emanuel Peixoto.

Emanuel Peixoto

* Capitão do Exército Brasileiro (QCO/Informática/2011)
* Formado em Sistemas de Informação.
* Criador do blog “Rumo à EsFCEx”
* Chefe da Seção de Informática do Hospital Militar de Área de Recife
* Profissional com mais de 15 anos de experiência na área de TI,atuando como Engenheiro de Sistemas e DBA
* Microsoft Certified – MCDBA | MCSE | MCSA | MCITP | MCTS
* IBM Certified Developer – Cognos 8 BI Data Warehouses
* ITIL® V2 e V3
* Green IT Citizen
* ISO/IEC 27002

http://www.mycertprofile.com/Profile/1915602619
http://www.mycertprofile.com/Profile/1915602619

DIVERSOS

DOCUMENTAÇÃO DE PROCEDURES E TRIGGERS

29 de novembro de 2008 Emanuel Peixoto Deixe um comentário

Deparei-me com o seguinte desafio no Fórum do MSDN: backup da base de dados SQL Server em um Web Hosting.

Como a única opção fornecida pelo fornecedor do serviço era uma exportação de tabelas, os objetos como procedures, triggers, functions views, dentre outros, não poderiam ser exportados, então sugeri a criação de uma tabela de documentação desses objetos.

Existe também o "Database Publishing Wizard", que pode gerar um script de todo o schema do banco e dar suporte a web hosting.

Segue script.

/* ##### CRIA TABELA DE CONTROLE (DOCUMENTAÇÃO) #####*/

if not exists(select 1 from sysobjects where name = ‘documentacao’ )
create table documentacao
(objetoid int primary key,
nome_objecto varchar(250),
Objeto_referencia varchar(250),
tipo char(1),
dt_criacao datetime,
script text)
go

/* ##### CRIA PROCEDURE COM INSTRUÇÕES PARA DOCUMENTAÇAO #####*/

create proc pr_documentacao
as
set nocount on
declare @cmd varchar(1000),@objeto varchar(250),@objeto_referencia varchar(250),@criacao datetime,@id int,@tipo char(1),@script varchar(8000)
declare obj cursor for
select o.id,o.name,object_name(o.parent_obj),o.crdate,o.xtype,c.text from sysobjects o, syscomments c where c.id=o.id
open obj
fetch next from obj into @id,@objeto,@objeto_referencia,@criacao,@tipo,@script
while @@fetch_status=0
begin
insert into documentacao(objetoid,nome_objecto,Objeto_referencia,dt_criacao,tipo,script)
select @id,@objeto,@objeto_referencia,@criacao,@tipo,@script where @id not in(select objetoid from documentacao )

fetch next from obj into @id,@objeto,@objeto_referencia,@criacao,@tipo,@script
end
close obj
deallocate obj
go

/* ##### Executa procedure para documentar objetos #####*/

exec pr_documentacao

Até mais,

Emanuel Peixoto.

Emanuel Peixoto

http://www.mycertprofile.com/Profile/1915602619
http://www.mycertprofile.com/Profile/1915602619

DIVERSOS

TRIGGER DE AUTO-INCREMENTO

28 de novembro de 2008 Emanuel Peixoto Deixe um comentário

create table tel_pac
(cod_pac int not null,
tel int not null)
go
alter table tel_pac add constraint pk_codpac primary key(cod_pac)
go
create trigger auto_num
on tel_pac
instead of insert
as
declare @cod int,@tel int
select @cod = case when max(cod_pac) is null then 1 else max(cod_pac)+1 end from tel_pac(xlock)
select @tel=i.tel from inserted i
insert into tel_pac(cod_pac,tel) values (@cod,@tel)

Emanuel Peixoto

http://www.mycertprofile.com/Profile/1915602619
http://www.mycertprofile.com/Profile/1915602619

DIVERSOS

VISUALISAR SALDO

28 de novembro de 2008 Emanuel Peixoto 1 Comentário

Bom, estou postando aqui, um exemplo de como realizar uma consulta de saldo.

Existem diversas formas de se realizar essa consulta, mas segue aqui um pequeno exemplo.

create

table mov_data

(

codigo int identity,

codigo_conta

int not null,

tipo

char(1),

data

datetime,

valor

float)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘C’,’10/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘C’,’10/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘D’,’11/10/2008′,50)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘C’,’12/10/2008′,300)

/* ##### Visualiza saldo #####*/

select

a.codigo,a.codigo_conta,a.tipo,a.data,a.valor,

(select sum(b.valor)

from (select codigo,codigo_conta,case when tipo = ‘C’ then valor else –valor end valor from mov_data) b

where a.codigo>=b.codigo and a.codigo_conta=b.codigo_conta) as Saldo

from

mov_data a

order

by a.codigo

Resultados do select:
Cod   CC   Tipo Data     Valor   Saldo
1     1    C    20081010 100     100
2     1    C    20081010 100     200
3     1    D    20081110 50      150
4     1    D    20081210 100     50
5     2    D    20081210 100     -100
6     2    D    20081210 100     -200
7     2    D    20081210 100     -300
8     2    C    20081210 300      0

Emanuel Peixoto

http://www.mycertprofile.com/Profile/1915602619
http://www.mycertprofile.com/Profile/1915602619

DIVERSOS

REMOVER ACENTUAÇÃO USANDO REPLACE

27 de novembro de 2008 Emanuel Peixoto Deixe um comentário

— Cria Tabela de Exemplo

Create

Table #Textos (Indice int identity(1,1), Texto VarChar(1000))

–insere caracteres com acentos

Insert

Into #Textos (Texto) Values (‘teste teste é, teste teste á’)

Insert

Into #Textos (Texto) Values (‘teste teste é, ãõâêî teste á’)

Insert

Into #Textos (Texto) Values (‘teste ãõâêî é, teste teste á’)

Insert

Into #Textos (Texto) Values (‘teste teste á, ãõâêî’)

Insert

Into #Textos (Texto) Values (‘teste teste’)

— Inicio da Rotina

create

table #TabelaAcentos (Indice Int Identity(1,1), Acento char(01), SemAcento Char(01))

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘á’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘é’,‘e’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘í’,‘i’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ó’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ú’,‘u’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘à’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘è’,‘e’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ì’,‘i’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ò’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ù’,‘u’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ã’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘õ’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘â’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ê’,‘e’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘î’,‘i’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ô’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘û’,‘u’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ç’,‘c’)

declare

@id int,@id_acento int,@old_char char(1), @new_char char(1),@texto varchar(1000)

set

@id=1

while

@id <= (select COUNT(1) from #Textos)

begin

select

@texto=Texto from #Textos where indice=@id

set @id_acento=1

while @id_acento <= (select count(1) from #TabelaAcentos)

begin

select @old_char=Acento, @new_char=SemAcento from #TabelaAcentos where Indice=@id_acento

set @texto=REPLACE(@Texto,@old_char,@new_char)

set @id_acento=@id_acento+1

end

select @texto

set

@id=@id+1

end

Emanuel Peixoto

http://www.mycertprofile.com/Profile/1915602619
http://www.mycertprofile.com/Profile/1915602619

Nome de usuário
Senha

	Lembrar-me Esqueceu sua senha? Registrar