No vídeo anulação, mascaramento e criptografia, vimos como proteger as informações de clientes quando precisamos enviar nossos bancos de dados SQL para fornecedores ou parceiros.

Ainda que existam as técnicas e estratégias que discutimos, algumas empresas simplesmente não enviam bancos de dados para ninguém, a menos que o banco esteja completamente vazio.

Excluir dados de todas as tabelas do banco parece ser simples, mas geralmente descobrimos que não é logo nas primeiras tabelas que tentamos excluir, devido a restrições de integridade (constraints) como chaves primárias e estrangeiras (primary keys e foreign keys).

Por essa razão, hoje veremos 3 estratégias simples para excluir dados de todas as tabelas do banco SQL.

Assista aqui ao vídeo de demonstração:

DBCC CLONE DATABASE

Depois do SQL 2014, a estratégia mais fácil para excluir os dados de todas as tabelas do banco SQL é usar o DBCC CLONE DATABASE.

Na realidade, esse comando não exclui dado algum, mas ele cria uma cópia completa do seu banco SEM copiar os dados!

Originalmente criado pela Microsoft justamente para que as empresas possam enviar a eles a estrutura do banco para resolução de problemas, o DBCC CLONE DATABASE é um comando extremamente rápido e fácil de usar!

GERAR SCRIPTS

Em linha com o DBCC CLONE DATABASE (visto acima), ao invés de resolvermos as restrições de integridade para excluir dados de todas as tabelas do banco, podemos gerar scripts do banco SQL como forma de compartilha-lo sem dados com outras pessoas.

= Script SQL: A boa e velha forma de exportar um banco vazio ou fazer downgrade do seu database (ex: de SQL2016 para SQL2014) é gerando scripts através do menu: botão direito no database > Tasks > Generate Scripts.

= DACPAC: Mais recente, temos a alternativa de gerar o banco via dacpac, no menu: Botão direito no database > Tasks > Extract Data-tier Application.

msForEachTable

Caso as alternativas de exportação e clonagem da base não sejam efetivas, teremos de partir para a exclusão manual.

Existem várias formas de abordar essa estratégia, e hoje compartilhei com você uma das que acredito ser mais simples e com menos código.

Consiste em desativar constraints e triggers para todas as tabelas usando a sp_msforeachtable, e depois excluir todas as tabelas usando essa mesma procedure.

Conclusão

Hoje vimos 3 estratégias para excluir dados de todas as tabelas do banco.

Técnicas como essas podem parecer distantes do nosso no dia a dia, mas lhe asseguro que domina-las ajudará bastante no seu destaque profissional quando algo do tipo for necessário.

Digo isso porque dispor de cartas na manga e curingas que permitam executar rapidamente tarefas de apoio a resolução de problemas é fundamental para técnicos que ganham como diretor.

Abraço do seu amigo Josué

Script da aula

----------------------------------------------------------------------------------------------------------
-- 3 formas de excluir todos os dados do seu banco SQL
----------------------------------------------------------------------------------------------------------
/*
-- Para testar em sua máquina, baixe e restaure a base "Adventureworks LEVE" de 2019:
https://docs.microsoft.com/pt-br/sql/samples/adventureworks-install-configure

-- Script de restore (exemplo):
use [master]
go
restore database [AW] 
	from disk = 'C:\tmp\AdventureWorksLT2019.bak' with file = 1,  
	move 'AdventureWorksLT2012_Data' to 'c:\tmp\AW.mdf', 
	move 'AdventureWorksLT2012_Log' to 'c:\tmp\AW.ldf'
go


*/


-----------------------------------------------------
-- SQL2014+: DBCC
-----------------------------------------------------
-- #1: Clone a base:
dbcc clonedatabase (aw, aw_clone)
go

-- #2: Teste para verificar se os dados estão vazios:
select * from aw.SalesLT.Customer
select * from aw_clone.SalesLT.Customer
go

-- #3: Faça backup e envie ao destino...
backup database aw_clone to disk = 'c:\tmp\aw_clone.bak' with init
go


-----------------------------------------------------
-- SQL2014-: Gerar scripts
-----------------------------------------------------
/*
Alternativa #1: Script SQL
	#1: Exporte o banco: Botão direito no banco > Tasks > Generate scripts

	#2: Envie o script gerado ao destino, ou 
		Execute o script na origem, porém alterando o nome do banco
		Faça backup da base criada
		Envie o bkp ao destino


Alternativa #2: Script DACPAC
	#1: Exporte o banco: Botão direito no banco > Tasks > Extract Data-tier application

	#2: Envie ao destino o arquivo DACPAC, ou
		Importe o banco: Botão direito em Databases > Deploy Data-tier application
		Faça backup da base importada
		Envie o bkp ao destino

*/


-----------------------------------------------------
-- SQL2014-: Excluir dados com "msforeachtable + delete"
-----------------------------------------------------
-- #1: Clonar a base com dados:
drop database if exists aw_clone
go
backup database aw to disk = 'c:\tmp\aw.bak' with init
go
restore database AW_CLONE from disk = 'c:\tmp\aw.bak' with
	move 'AdventureWorksLT2012_Data' to 'c:\tmp\aw_clone.mdf', 
	move 'AdventureWorksLT2012_Log' to 'c:\tmp\aw_clone.ldf'
go


-- #2: Excluir os dados da base clonada
alter database aw_clone set recovery simple with no_wait
go
use aw_clone
go
execute sp_msforeachtable 'alter table ? nocheck constraint all'
execute sp_msforeachtable 'alter table ? disable trigger all'
execute sp_msforeachtable 'set quoted_identifier on; delete from ?'
execute sp_msforeachtable 'alter table ? check constraint all'
execute sp_msforeachtable 'alter table ? enable trigger all'
execute sp_msforeachtable 'if objectproperty(object_id("?"), "tablehasidentity") = 1 begin dbcc checkident ("?",reseed,0) end'
go


-- #3: Fazer backup e remeter ao destino
backup database aw_clone to disk = 'c:\tmp\aw_clone.bak' with init
go