Dica do Mês – Comando Restore Database Page – Restaurando páginas de dados de uma tabela no Microsoft SQL Server


Olá boa tarde, que surpresa te encontrar mais uma vez no meu blog, caso esta seja a sua primeira vez, fico mais feliz ainda, seja muito bem vindo.

Este é mais um post da sessão Dica do Mês, sessão dedicada a compartilhar bimestralmente dicas, novidades, curiosidades e demais assuntos, conteúdos e informações relacionadas ao Microsoft SQL Server, Banco de Dados e Tecnologias de Banco de Dados.

No post de hoje, quero compartilhar com vocês uma das funcionalidades adicionadas ao Microsoft SQL Server a partir da versão 2016 e que recentemente acabei conhecendo com um pouco mais. 

Funcionalidade que trouxe um grande salto de qualidade ao produto, ainda mais se levarmos em consideração sua praticidade e simplicidade de uso.

Como você já pode notar no título deste post, estou me referindo a nova capacidade de recuperação de dados através do comando Restore Database em conjunto com a opção Page.

Pois bem, sem mais delongas, vamos em frente, vou tentar mitigar a sua curiosidade e ao mesmo também satisfazer os meus objetivos. Sendo assim, seja bem vindo ao post – Dica do Mês – Comando Restore Database Page – Restaurando páginas de dados de uma tabela no Microsoft SQL Server.


Introdução

Umas das tarefas mais ingratas para qualquer profissional de tecnologia, principalmente aqueles que estão diretamente relacionadas as tarefas de administração, retenção e armazenamento de dados se relaciona ao momento em que nossos ambientes começam apresentam comportamentos fora do comum ou até mesmo instabilidades. 

Quem nunca se deparou com este tipo de situação! Eu por diversas vezes passei por isso nesta minha longa estrada da vida na área de tecnologia da informação.

Mas não somente isso é importante, algo muito maior e mais preocupante podemos enfrentar, o tão temido momento de restauração de um banco de dados o chamado Restore Database, imagina então você ter que recuperar uma parte específica de uma tabela ou índice que de uma hora para outra começou a apresentar falhas e simplesmente tornou-se inacessível.

Foi justamente com base neste tipo de cenário, que o time de engenheiros da Microsoft dedicados no desenvolvimento do Microsoft SQL Server adicionaram no comando Restore Database e também no interface gráfica do Management Studio a capacidade de verificar a integridade física e lógica de uma ou mais páginas de dados, como também, a possibilidade de realizar sua restauração.

Até aqui tranquilo, nada de novidade, vamos então seguir em frente e conhecer a opção Page existente no comando Restore Database.

Tabelas e Índices

As tabelas são o coração do Microsoft SQL Server e do modelo relacional em geral, pois é onde o dado é armazenado. Cada instância de um dado na tabela representa uma entidade simples ou registro (formalmente chamado de tupla). A maioria das tabelas serão relacionadas entre si. Por exemplo: A tabela Clientes possuí um identificador único CodigoCliente que é usado como chave estrangeira no relacionamento com a tabela Pedido.

As tabelas devem ser modeladas de acordo com a teoria de banco de dados relacionais, respeitando as formas normais.

Ao criarmos nossas tabelas e índices, estamos criando internamente estrutura responsáveis em armazenar em tempo real nossos dados em áreas físicas das unidades de armazenamento de dados.

Não vou me aprofundar nos conceitos relacionados a páginas de dados, pois este não é objetivo deste post, mas sim de destacar como a Restore Database Page é importante, sua finalidade e forma de uso.

Restore Database Page

Seu objetivo é possibilitar a restauração de uma página de dados danificada sem restaurar todo o banco de dados, muito menos provocar qualquer tipo de impacto ou instabilidade no acesso aos dados após sua resturaçao.

Normalmente, as páginas que são candidatos para restauração foram marcadas como “suspeita” devido a um erro que é encontrado ao acessar a página.

As páginas suspeitas são identificadas na tabela suspect_pages no banco de dados msdb.  

Avançando mais um pouco, neste momento, já temos uma noção dos elementos básicos: Tabelas e Índices, sabemos também da estrutura que as compõem chamada de páginas de dados e de que forma estas estruturas são controladas e gerenciadas, agora vamos construir nosso cenário de testes que justamente vai nos permitir ter a visão completa de toda esta estrutura e como poderemos realizar os procedimentos de sobrescrever uma página de dados e posteriormente realizar sua restauração.

Nosso ambiente

Como de costume vamos utilizar um ambiente isolado dos demais bancos de dados que você possa conter, desta maneira nosso cenário será constituído dos seguintes elementos:

  • Banco de Dados:  RestoreDatabasePage;
  • Database Recovery Model: Full;
  • Database Page_Verify: CheckSum;
  • Tabela: TabelaCorrompida; e
  • Índice Clusterizado: Ind_TabelaCorrompida_Codigo. 

Criando o ambiente

Através do Bloco de Código 1 apresentado abaixo, vamos realizar a criação dos respectivos elementos destacados anteriormente:

— Bloco de Código 1 – Criação do Ambiente —

— Criando o Banco de Dados —
Create Database RestoreDatabasePage
Go

— Acessando —
Use RestoreDatabasePage
Go

— Criando a TabelaCorrompida —
Create Table TabelaCorrompida
(Codigo Int Identity(0,2),
ValorGUID UniqueIdentifier,
ValorRandomico BigInt,
ColunaGrande Char(100) Default ‘TC’)
Go

— Criando o Índice Clusterizado na TabelaCorrompida —
Create Clustered Index Ind_TabelaCorrompida_Codigo On TabelaCorrompida(Codigo)
Go

Como nossa estrutura base pronta, chegou a hora de popular nossa tabela realizando o processo de inserção de uma aleatória massa de dados em nossa tabela, para tal, vamos utilizar o Bloco de Código 2 apresentado a seguir:

— Bloco de Código 2 – Populando a TabelaCorrompida —
— Desabilitando a contagem de linhas processadas —
Set NoCount On
Go

— Declarando a variável de controle @Contador —
Declare @Contador Int = 0

— Abrindo bloco de transação Trans1 —
Begin Transaction Trans1

While @Contador <= 132768
Begin

Insert Into TabelaCorrompida(ValorGUID, ValorRandomico)
Values (NewId(), ABS(CHECKSUM(Rand()* 200000000)))

Set @Contador += 2
End

— Confirmando e encerrando o bloco de transação Trans1 —
Commit Transaction Trans1
Go

Observação: Note que estou fazendo uso dos comandos Begin Transaction e Commit Transaction, como forma de controle e adoção de transações explícita, sendo assim, estou informando o Microsoft SQL Server quando a transação começa e deverá ser obrigatoriamente encerrada, além disso, estou evitando e isolando o processo de inserção de dados de qualquer possibilidade de bloqueio.

Neste momento, nossa tabela já esta populada “abastecida de dados”, com um total fixo de 66385 linhas de dados, denominados tecnicamente como registros lógicos.

Vamos caminhar mais um pouco, antes de realizarmos o processo de consultar a estrutura de nossas páginas de dados e posteriormente forçar sua reescrita, vamos realizar um procedimento de backup database de nosso banco de dados, procedimento importante para garantir e possibilitar a restauração das páginas, para tal utilizaremos o Bloco de Código 3 apresentado abaixo:

— Bloco de Código 3 – Backup Database —
Backup Database RestoreDatabasePage
To Disk = ‘S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Full.bak’  — Troque para sua                                                                                                                                              unidade de disco
With Compression,
NoFormat,
Init,
Stats=10
Go

Pronto, nosso backup já esta realizado, estamos prontos e preparados para começar a brincadeira, nosso próximo passo será obter a relação das páginas de dados que forma nossa TabelaCorrompida, para isso, vamos utilizar a não documentada function sys.fn_PhysLocFormatter, solicitando ao Microsoft SQL Server a apresentação das 100 primeiras páginas de dados da nossa tabela, conforme apresenta o Bloco de Código 4:

— Bloco de Código 4 – Obtenção a relação das páginas de dados da TabelaCorrompida —
Select TOP 100 sys.fn_PhysLocFormatter(%%physloc%%) PageId,
*
FROM TabelaCorrompida
Go

A Figura 1 apresentada a seguir ilustra o resultado obtido após a execução do Bloco de Código 4:
Figura 1 – Relação das páginas de dados e seus respectivos dados.

Legal, esta ficando interessante esta brincadeira, por enquanto sem nenhum perigo!

Para que possamos realizar o processo de reescrita de uma ou mais páginas de dados, vou selecionar duas páginas (256 e 258) e seus valores para utilizar em nosso cenário, conforme a Tabela 1 apresentada abaixo:

PageID Codigo ValorGuid
(1:256:10) 20 6460AAB3-AD12-47BB-B179-8C1930B1A287
(1:258:1) 120 AEF17F9D-D838-4FEF-B723-CA3658D03319

Tabela 1 – Relação de páginas de dados e valores que iremos utilizar.

Já sabemos com quais estruturas vamos fazer o processo de reescrever suas estruturas, devemos então preparar nosso banco de dados para que nos possibilite a realização desta tarefa, desta forma, utilizaremos o Bloco de Código 5, apresentado abaixo:

— Bloco de Código 5 — Alterando a forma de acesso do banco de dados RestoreDatabasePage —

— Preparando-se para corromper a estrutura de páginas —
Use Master
Go

— Limitando a conexão do Banco de Dados para Single_User —
Alter Database RestoreDatabasePage
Set Single_User
With Rollback Immediate
Go

Ótimo, acabamos de limitar o acesso físico e lógico do nossa banco de dados para Single_User, desta forma, nenhuma outra conexão ou solicitação de acesso será permitida ao mesmo, neste momento temos acesso único e exclusivo.

O passo seguinte, consiste na consulta da estrutura da página de dados 256 e posteriormente na procura do valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 armazenado no Slot 10, vamos então executar o Bloco de Código 6, apresentado abaixo:

— Bloco de Código 6 — Obtendo as informações sobre a página de dados 256 e pesquisando valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 —

Para que possamos obter as informações de retorno apresentadas pelos comandos DBCC – Database Command Console, precisamos fazer uso do comando Dbcc TraceOn ativando a Trace Flag 3604 que orienta e informa ao Microsoft SQL Server que o mesmo deverá apresentar logo após a execução dos comandos DBCCs seus respectivos resultados.

— Obtendo informações sobre os slots de alocação de dados —
Dbcc TraceOn (3604)
Go

Seguindo nossa caminhada, vamos utilizar o comando DBCC Page, comando que vai nos possibilitar obter o conjunto de informações internas que formam a estrutura da nossa tabela, neste caso, vamos buscar toda estrutura da página de dados de número 256.

— Procurando valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 e guardar slots —
Dbcc Page (‘RestoreDatabasePage’, 1, 256, 3);
Go

A Figura 2 apresentada abaixo, ilustra uma parte da estrutura interna da página de dados 256, apresentando sua área de buffer e page hearder:
Figura 2 – Estrutura interna da página de dados 256.

Pois bem, precisamos agora procurar o valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 dentro da área de dados desta mesma página, afim de encontramos o refiro Slot 10 que armazena este dado.

Para que possamos encontrar o referido valor clique na guia de mensagens do Management Studio e preciso posteriormente a tecla de atalho CTRL + F, informando o valor na campo de busca.

A Figura 3 ilustra o 6460AAB3-AD12-47BB-B179-8C1930B1A287 localizado na estrutura interna da página de dados 256:
Figura 3 – Valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 localizado.

O mesmo procedimento deverá ser feito para página 258 referente ao código 120 e ValorGuid AEF17F9D-D838-4FEF-B723-CA3658D03319.

Além disso, recomendo que você anote as informações referente OffSet e Length de dados valor pesquisado em sua referida página, pois ambos serão utilizado no procedimento de reescrita, mas como eu sou bonzinho, a Tabela 2 apresentada abaixo destaca estes valores:

Collumn Offset Length ValorGuid
2 0x8 16 6460aab3-ad12-47bb-b179-8c1930b1a287
2 0x8 16 AEF17F9D-D838-4FEF-B723-CA3658D03319

Tabela 2 – Informações sobre Offset e Length dos respectivos ValorGuid.

Agora chegou a tão esperada hora de suar o barraco (kkkk), não é bem assim, mas chegou o momento de reescrevermos a estrutura das páginas de dados: 256 e 258, através do comando DBCC WritePage declarado no Bloco de Código 7 apresentado na abaixo:

— Reescrevendo a página de dados 256 no OffSet 0x8 —
Dbcc WritePage (‘RestoreDatabasePage’, 1, 256, 8, 16, 0x00000000000000000000000000000001, 1)
Go

— Reescrevendo a página de dados 256 no OffSet 0x8 —
Dbcc WritePage (‘RestoreDatabasePage’, 1, 258, 8, 16, 0x00000000000000000000000000000001, 1)
Go

Se você conseguiu realizar o processamento destes dois comandos DBCC WritePage, isso significa que neste momento as páginas de dados 256 e 258 estão apresentando inconsistência em suas estruturas, algo que podemos comprovar através da execução do Bloco de Código 8, apresentado abaixo:

— Bloco de Código 8 – Verificando a Integridade da TabelaCorrompida —
— Alterando o acesso ao Banco de Dados para Multi_User —
Alter Database RestoreDatabasePage
Set Multi_User
Go

— Realizar testes de integridade consultando dados na TabelaCorrompida —
Use RestoreDatabasePage
Go

Select Count(Codigo) From TabelaCorrompida
Go

Ao realizarmos o comando Select Count() para tentarmos contar a quantidade de linhas de registros existentes na TabelaCorrompida, o Management Studio nos retorna a seguinte mensagem de erro:
Msg 824, Level 24, State 2, Line 162
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x4bd220eb; actual: 0xcb53a034). It occurred during a read of page (1:256) in database ID 11 at offset 0x00000000200000 in file ‘S:\MSSQL-2017\Data\RestoreDatabasePage.mdf’. Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Vamos avançar mais ainda, estamos nos aproximando do final deste post, agora que nosso ambiente esta danificado podemos fazer uso da opção Page existente no comando Restore Database que vai nos permitir restaurar a estrutura física e lógica da nossa tabela, sendo assim, vamos utilizar o Bloco de Código 9, apresentado abaixo:

— Bloco de Código 9 – Iniciando o processo de restauração e recuperação das páginas de dados —
— Realizando a Restauração das Páginas de Dados —
Use Master
Go

— Restore Database Page —
Restore Database RestoreDatabasePage
PAGE=’1:256, 1:258′ — Informando os números de páginas
From Disk = N’S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Full.bak’
With File = 1, — Especificando o arquivo de dados
NoRecovery, — Não liberando o banco para acesso
Stats = 10
Go

 

Perfeito, realizamos o procedimento se restauração das páginas de dados 256 e 258 sem restaurar toda estrutura do nosso banco, agora podemos realizar um novo teste e verificar se a a estrutura da nossa TabelaCorrompida encontra-se funcional, conforme apresenta o Bloco de Código 10 a seguir:

— Bloco de Código 10 — Realizando um novo teste de integridade consultando dados na TabelaCorrompida —
Use RestoreDatabasePage
Go

Select Count(Codigo) From TabelaCorrompida
Where Codigo Not Between 20 And 120
Go

E para nossa surpresa o Management Studio retornou mais uma vez outra mensagem de erro:
Msg 829, Level 21, State 1, Line 186
Database ID 11, Page (1:256) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.

Esta mensagem nos informa que não podemos realizar o acesso a TabelaCorrompida pois neste momento a página 256 esta marcado como pendente de restauração, este é um comportamento normal apresentado pelo SQL Server, pois o mesmo depende da realização de um backup de log e posteriormente da restauração (conhecido como Tail Log) para realizar a limpeza e desmarcar esta página de dados como pendente.

Para tal procedimento, utilizaremos o Bloco de Código 11, apresentado abaixo:

— Bloco de Código 11 — Realizando Backup Log e Restore Log (Tail Log) —
— Backupear o Log e Restaura para Liberar páginas marcadas como pendentes —
Use Master
Go

Backup Log RestoreDatabasePage
To Disk = ‘S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Log.bak’
With NoFormat,
Init,
Name = N’RestoreDatabasePage-Backup-Log’,
Stats=10
Go

— Restaurar Log —
Restore Log RestoreDatabasePage
From Disk = ‘S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Log.bak’
With Recovery,
Replace,
Stats = 10
Go

Acredito que o procedimento de Backup Log e Restore Log tenha ocorrido normalmente, basta agora realizar o último teste de acesso a TabelaCorrompida para poder consultar todos os dados armazenados na mesma, conforme apresenta o Bloco de Código 12:

— Bloco de Código 12 — Realizar último teste de integridade consultando dados na TabelaCorrompida —
Use RestoreDatabasePage
Go

A Figura 4 apresentada abaixo ilustra a massa de dados existente na TabelaCorrompida, após o procedimento de restauração e recuperação das páginas de dados: 256 e 258.
Figura 4 – Relação de dados existentes na TabelaCorrompida, recuperados após o procedimento de Restore Database Page.

— Obtendo a quantidade de registros armazenados na TabelaCorrompida —
Select Parcial=(Select Count(Codigo) From TabelaCorrompida Where Codigo Not In (20,120)),
Geral=(Select Count(Codigo) From TabelaCorrompida)
Go

Show de bola, muito bom, conseguimos, seguimos todos os passos desde a criação do nosso ambiente, inserção de dados, identificação das páginas e suas estrutura, reescrita na estrutura das páginas e o tão esperado procedimento de restauração.

Com isso chegamos ao final de mais um post da sessão Dica do Mês, antes de encerrarmos, gostaria de contar com a sua participação neste post, respondendo a enquete abaixo:


Referências

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

https://www.mssqltips.com/sqlservertip/1925/how-to-use-the-sql-server-sysfnphyslocformatter-undocumented-function/

https://blogs.msdn.microsoft.com/fcatae/2016/04/12/dbcc-page/

https://docs.microsoft.com/pt-br/sql/t-sql/database-console-commands/dbcc-transact-sql

http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-New-%28undocumented%29-physical-row-locator-function.aspx

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/12/13/more-undocumented-fun-dbcc-ind-dbcc-page-and-off-row-columns/

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

Posts Anteriores

https://pedrogalvaojunior.wordpress.com/2018/07/26/dica-do-mes-ocultando-uma-instancia-em-execucao-do-microsoft-sql-server/

https://pedrogalvaojunior.wordpress.com/2018/04/25/dica-do-mes-sql-operations-studio-view-as-chart/

https://pedrogalvaojunior.wordpress.com/2018/03/14/dica-do-mes-microsoft-sql-server-2017-sql-graph-databases/

https://pedrogalvaojunior.wordpress.com/2018/01/24/dicadomes-sqlservertoolsuiteintroduction/

Conclusão

Como já destaquei em outros posts, a cada nova versão, atualização e correção a Microsoft transforma o SQL Server em um produto surpreende, ainda mais na sua capacidade e versatilidade de permitir aos profissionais de tecnologia, administradores de bancos de dados, programadores, entre outros, utilizar recursos nativo e também os não documentados oficialmente como um elemento capaz de se superar e sobreviver a  inúmeras falhas ou situações de perdas de dados.

No post de hoje, mais uma vez este foi constatado, a possibilidade através do comando DBCC Page de se obter informações sobre as páginas de dados, o comando DBCC WritePage (muito cuidado com ele) sensacional na sua funcionalidade em permitir uma reescrita de dados na estrutura das páginas que formam uma tabela, e principalmente a não documentada function sys.fn_physLocFormatter que de forma simples, fácil e confiável nos apresenta a distribuição de páginas de dados que compõem nossas tabelas em conjunto com os respectivos slots que armazenam nosso dados.

Acredito que você tenha conseguido entender e observar como consultamos a estrutura de páginas, a forma que alteramos seu conteúdo forçando uma reescrita de dados e depois como conseguimos através do comando Restore Database Page recuperar estas áreas.

Este é o fantástico Microsoft SQL Server, produto tão fascinante que a cada dia eu não consigo deixar de querer estudar e conhecer mais ainda.

Agradecimentos

Agradeço a você por sua atenção e visita ao meu blog. Fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Nos encontramos no próximo post da sessão Dica do Mês a ser publicado no mês de dezembro.

Um forte abraço, sucesso, até mais…

Script Challenge – 14 – A resposta….


Boa tarde, pessoal…

Tudo bem?  Seja mais uma vez muito bem vindo ao meu blog, mais especificamente ao post que apresenta a resposta para o Script Challenge – 2018 – Post 14, publicado em junho de 2018, sendo este respectivamente o segundo post após o retorno desta desafiadora sessão em meu blog denominada Script Challenge (Script Desafiador ou Desafio do Script) como queiram traduzir.

Espero que você já tenha ouvido falar desta sessão ou acessado alguns dos posts publicados na mesma, caso ainda não tenha feito, fique tranquilo você vai encontrar no final deste post uma pequena relação contendo os últimos desafios lançados e seus respostas.

Vamos então falar um pouco mais sobre o último desafio, estou me referindo ao Script Challenge 14, desta forma, seja bem vindo a mais um post da sessão Script Challenge.


Script Challenge 14

Falando do desafio de número 14, o mesmo foi publicado no mês de junho de 2018, período de data em que o mundo todo praticamente direcionou os seus olhares para a Rússia, mais especificamente para os jogos de futebol que estavam ocorrendo no país naquele momento.

Pois bem, o Script Challenge 14 não tem nenhum relação com o mundo do futebol, muito menos com o esporte, e como diria aquele apresentador do programa que passa ao domingos: “Sabe o que isso significa? Nada…..”.

Na verdade não é bem assim, para todos aqueles que trabalham com tecnologia e são responsáveis em armazenar, compartilhar, gerenciar e manter dados armazenados em banco de dados, sabe muito bem o quanto temos que nos preocupar em estabelecer boas práticas de retenção de dados afim de podermos ter uma quem sabe vida tranquila ou momentos de lazer.

Continuando nossa história, quero lhe perguntar: E ai já matou a charada? Eu acredito que sim!

Mas para te ajudar mais um pouco vou apresentar a Figura 1 que contem todo código Transact-SQL utilizado neste desafio, contendo trechos ou partes de código ocultas, procedimento que realizei no post que contempla o lançamento deste desafio como forma de aumentar o nível de dificuldade:

Figura 1 – Código Transact-SQL apresentado no Script Challenge 14.

Bom chegou a hora de revelar o que exatamente este bloco de código esta fazendo, chegou o momento de revelar e desvendar este desafio, a seguir apresento a resposta para o Script Challenge 14 e o trecho de código disponível para você utilizar em seus ambientes de trabalho ou estudos.

A resposta

Tanto no post de lançamento do desafio, bem como, neste post que a resposta para o mesmo, eu deixei algumas pequenas dicas para tentar ajudar a identificar a resposta, dentre as quais a relação do script com uma das mais tradicionais atividades desempenhadas por um Administrador de Banco de Dados ou Profissional de tabela, mais diretamente falando a execução de uma operação de backup de banco de dados.

Mas se mesmo assim, você ainda não conseguiu adivinhar ou até mesmo esta se perguntando qual a relação do Script Challenge – 14 tem haver com um momento de lazer, a resposta é muito simples, para qualquer Administrador de Banco de Dados, Administrador de Servidores, Desenvolvedor, enfim um profissional de tecnologia, tudo o que fazemos basicamente em um computador é manipular dados (Criar, Atualizar, Excluir).

Tudo o que fazemos esta relacionado com esta palavrinha pequena mas de altíssima importância e pensando neste sentido a resposta para este desafio se relaciona a estimativa de crescimento de um arquivo de backup, e o quanto esta atividade tão importante e de alta complexidade pode impactar totalmente na vida daqueles que assim como eu um dia ou por diversos momentos teve que abrir mão do seu convívio familiar para se dedicar a acompanhar esta atividade.

Então a resposta para o Script Challenge 14 se relaciona com a possibilidade que o script apresenta em nos ajudar a identificar e estimar o quanto de espaço livre em disco em megabytes ainda teremos antes da execução do backup database levando-se em consideração o tamanho do arquivo de backup a ser criado.

Isso mesmo, esta é a resposta, e o script original que apresenta esta funcionalidade apresentada abaixo:

— Script Challenge 14 – A resposta – Identificando o total de espaço livre em disco antes da realização do backup database — 

— Criando a Stored Procedure —
USE AdventureWorksDW2016
Go

CREATE PROCEDURE dbo.dbo.EstimatedDriveFreeSpaceAndDBSize (
@drvLetter VARCHAR (5),
@enoughSpaceForBackupFlag BIT OUTPUT
)
AS
BEGIN
DECLARE @estimatedBackSizeMB INT,
@estimatedDriveFreeSpaceMB INT,
@dbCheckMessage varchar(80)

SET NOCOUNT ON

SET @dbCheckMessage = Concat (‘Checking database ‘, DB_NAME ())

SELECT @estimatedBackSizeMB = round (sum (a.total_pages) * 8192 / SQUARE (1024.0), 0)
FROM sys.partitions p JOIN sys.allocation_units a
                                            ON p.partition_id = a.container_id
                                           LEFT JOIN sys.internal_tables it
                                            ON p.object_id = it.object_id

CREATE TABLE #freespace

(drive VARCHAR (5),

MBFree DECIMAL (8, 2))

INSERT INTO #freespace (Drive, MBFree)
EXEC xp_fixeddrives

SELECT @estimatedDriveFreeSpaceMB = MBFree
FROM #freespace
WHERE drive = @drvLetter

IF @estimatedBackSizeMB * 1.15 < @estimatedDriveFreeSpaceMB
 SET @enoughSpaceForBackupFlag = 1
ELSE
 SET @enoughSpaceForBackupFlag = 0

SELECT DatabaseName = db_name(),
Estimated_Back_Size_MB = @estimatedBackSizeMB,
Estimated_Drive_Free_Space_MB = @estimatedDriveFreeSpaceMB,
EnoughSpaceForBackupFlag = @enoughSpaceForBackupFlag

DROP TABLE #freespace
SET NOCOUNT OFF
END
GO

Então, agora você deve ter gostado deste desafio, não é verdade? Poder estimar o espaço livre em disco e o tamanho ocupado pelo arquivo mesmo sem executar o Backup Database é realmente uma grande funcionalidade que o Microsoft SQL Server possui. 

Observações

  1. Estamos criando uma User Stored Procedure EstimatedDriveFreeSpaceAndDBSize;
  2. A mesma possui um parâmetros de entrada de valores: @drvLetter (utilizado para informar qual a letra da unidade de disco que iremos analisar); e
  3. Um parâmetro de saída @enoughSpaceForBackupFlag (utilizado no momento da execução da stored procedure como sinalizar responsável em apresentar uma mensagem ao usuário).

Para que você possa entender mais ainda sobre como podemos obter os resultados apresentados por este script, declaro a seguir uma possível maneira de executar o Script Challenge – 14:

— Executando o Script Challenge – 14 —

USE AdventureWorksDW2016
Go

DECLARE @enoughSpaceForBackupFlag bit

EXEC Master.dbo.EstimatedDriveFreeSpaceAndDBSize ‘S’, @enoughSpaceForBackupFlag OUTPUT

PRINT @enoughSpaceForBackupFlag
IF @enoughSpaceForBackupFlag = 1
PRINT ‘Continue to Backup…’
ELSE
PRINT ‘Drive Space Problem…’
GO

A Figura 2 apresentada abaixo, ilustra o conjunto de dados retornados após a execução do Script Challenge – 14:

Figura 2 – Informações relacionadas a estimativa de tamanho do arquivo de backup e espaço livre em disco em megabytes.

Muito bom, sensacional, conseguimos, chegamos ao final, esta é a resposta para o Script Challenge 14, fico extremamente feliz por ter conseguido compartilhar este conteúdo com vocês.

Espero que você tenha gostado deste novo post da sessão Script Challenge!


Sua Participação

No post de lançamento deste desafio, contei com a participação através de uma enquete contendo algumas opções de respostas que poderiam estar relacionadas com o Script Challenge 14. A seguir apresento o resultado desta enquete:

A opção mais votada com 77,78% dos votos é justamente a resposta correta para este desafio, o qual exibe retorna ao usuário informações relacionadas a estimativa de espaço em disco ocupado pelo arquivo de backup de banco de dados e o espaço livre disponível em disco após a conclusão do backup.

Referências

Agradecimentos

Obrigado por sua visita, espero que este conteúdo aqui apresentado como um possível “desafio” possa ser útil e ao mesmo tempo prover conhecimento, aprendizado ou mostrar recursos e problemas existentes no Microsoft SQL Server que as vezes parecem não ter uma resposta.

Um forte abraço nos encontramos em breve nas demais sessões e especialmente em fevereiro de 2019 em mais um post da sessão Script Challenge.

Até a próxima…

Microsoft SQL Server 2017 Cumulative Update 9 disponível


A Microsoft informou ontem dia 19/07 no blog SQL Server Release Services a disponibilidade da Atualização Cumulativa(Cumulative Update) 9 para o Microsoft SQL Server 2017.

Atualizações Cumulativas disponíveis para o Microsoft SQL Server 2017:

O artigo KB4341265 publicado no site de suporte da Microsoft, esta nova atualização do SQL Server 2017 traz todas as correções disponibilizadas desde o lançamento do novo SQL Server, incluindo também correções para problemas encontrados após o lançamento das atualizações cumulativas anteriores.

Hotfixes que estão incluídos neste pacote de atualização cumulativa:
Número do bug VSTS Número do artigo KB Descrição Área fixa Plataforma
12144190 4340069 CORREÇÃO: SQL Server 2017 no Linux é desligado inesperadamente durante a recuperação de um banco de dados OLTP de memória OLTP in-memory Linux
12041154 4340134 CORREÇÃO: Erro quando uma função é definida com uma coluna restrita é usada para executar uma consulta drill-through no SSAS Analysis Services Windows
12128861 4340747 CORRIGIR: SQLDUMPER. Despejos EXE iniciada podem levar muito tempo para concluir o processo de geração de despejo para 2017 do SQL Server no Linux Mecanismo SQL Linux
12168709 4010460 CORREÇÃO: Um erro do.NET Framework ocorreu quando você atualiza a tabela de referência de uma transformação Fuzzy Lookup no SSIS Integration Services Windows
12138685 4339613 CORREÇÃO: “Unclosed aspas após a sequência de caracteres” erro ocorre no explorer MDS quando você tentar adicionar um novo membro para uma entidade no SQL Server Data Quality Services (DQS) Windows
12107546 4338890 CORREÇÃO: Uma instância do SQL Server pode parecer não responder e em seguida, pode ocorrer um erro de “não respondendo no Agendador” no SQL Server 2016 Mecanismo SQL Windows
11922902 4316858 CORREÇÃO: “índice corrompido” mensagem e servidor desconexão quando uma consulta de estatísticas de atualização usa hash agregação no SQL Server Desempenho do SQL Todas
12149855 4341219 CORREÇÃO: Um cenário de cérebro divisão ocorre após um failover ao usar grupos de disponibilidade do AlwaysOn com a tecnologia de cluster externo no SQL Server 2017 Alta disponibilidade Todas
12111717 4340837 CORREÇÃO: Erro 3906 quando for aplicado um hotfix em um SQL Server que possui um banco de dados em um banco de dados de inscrição de recepção de instantâneo Mecanismo SQL Windows
11983925 4133164 CORREÇÃO: Erro quando um trabalho do SQL Server Agent executa um comando PowerShell enumere permissões do banco de dados Ferramentas de gerenciamento Windows
12121216 4339664 CORREÇÃO: O erro de exceção ocorre quando você tenta atualizar dados de uma tabela dinâmica no Excel no SSAS 2017 Analysis Services Windows
12123248 4340742 CORREÇÃO: Acesso ao SSAS usando HTTP falha no SQL Server Analysis Services Windows
12162067 4341264 Aperfeiçoamento: Permitir trabalhos do SQL Server Agent iniciar sem esperar que todos os bancos de dados obter recuperado no SQL Server 2017 no Linux Mecanismo SQL Linux
12186129 4101502 CORREÇÃO: Backup de banco de dados TDE habilitada com compactação causa corrupção de banco de dados no SQL Server Mecanismo SQL Todas
12129434 4134601 CORREÇÃO: “não foi possível carregar arquivo ou assembly ‘ Microsoft.AnalysisServices.AdomdClientUI” erro quando uma operação de “Processo total” é executada no SQL Server Analysis Services Windows
12162425 4341221 CORREÇÃO: Backup VSS Falha na réplica secundária de grupos básicos de disponibilidade no SQL Server 2016 e 2017 Mecanismo SQL Windows
12108225 4339858 CORREÇÃO: Redo paralelo não funciona após você desativar 3459 de sinalizador de rastreamento em uma instância do SQL Server Alta disponibilidade Todas
12061383 4341253 CORREÇÃO: Sys.dm_db_log_info e sys.dm_db_log_stats DMVs podem retornar valores incorretos para o último banco de dados da instância do SQL Server 2016 Mecanismo SQL Windows

Dentre os erros e falhas corrigidas neste cumulative update, as informações apresentadas no KB4341265 destacam uma correção relacionada comportamento apresentado por uma instância do SQL Server 2017 que aparentemente encontra-se travada e exibindo o erro “Non-yielding Scheduler“. 

Outra correção destacada no artigo, se relaciona ao erro “Could not load file or assembly ‘Microsoft.AnalysisServices.AdomdClientUI”.

Vale ressaltar que além de correções relacionadas a erros apresentados por comportamentos apresentadas pelas instância SQL Server 2017, a CU9 também possui correções para os bugs relacionados as DMVs sys.dm_db_log_stats e sys.dm_db_log_info may retornem valores incorretos em determinados momentos de consulta de dados relacionados aos arquivos de log existentes em bancos de dados.

Vale ressaltar que após a atualização desta nova atualização cumulativa, o número do build utilizado pelo Microsoft SQL Server 2017 RTM será alterado para compilação: 14.0.3030.27.

Para realizar o download clique na imagem abaixo:

Fontes e Direitos Autorais: SQL Server Release Services – 19/07/2018.

Material de Apoio – Junho 2018


Olá, boa tarde.

Tudo bem? E ai esta curtindo a Copa do Mundo de Futebol da Rússia? Posso dizer tranquilamente que eu estou curtindo muito todos os jogos e informações possíveis de serem acompanhadas.

Estou aqui mais uma vez procurando colaborar e compartilhar com a comunidade técnica em mais um post da sessão Material de Apoio dedicado exclusivamente ao meu blog.

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a torná-lo ainda melhor no decorrer do tempo com a sua participação.

O post de hoje

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o terceiro do ano de 2018 e de número 157 no total desta sessão.

Para aqueles que já acompanham o meu blog a um certo tempo, os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Hoje não será diferente, estou trazendo alguns dos mais recentes scripts  catalogados nos últimos meses, que atualmente estão compondo a minha galeria de códigos formada ao longo dos anos de trabalho como DBA e atualmente como Professor de Banco de Dados.

Neste post você vai encontrar arquivos relacionados com os seguintes temas:

  • Acessos de Usuário;
  • Backup;
  • CheckList;
  • Comando Create Procedure;
  • Comando Declare;
  • Database Backup;
  • Disponibilidade;
  • DMV sys.dm_db_index_usage_stats;
  • DMV Sys.dm_os_sys_info;
  • Dymanic Management View;
  • Free Disk Space;
  • Função DateAdd;
  • Função Month;
  • Heap Table;
  • Índices Clustered; Reinicialização de Servidores;
  • Índices;
  • Junção de Tabelas;
  • Leitura e Escrita; System Stored Procedure;
  • Logins;
  • Set DateFirst;
  • Set Language;
  • Set NoCount;
  • System Stored Procedure SP_MSForeachTable;
  • System Table sys.allocation_units; System Table sys.indexes;
  • System Table sys.partitions;
  • System Table sys.schemas;
  • System Table sys.tables;
  • Usuários; e
  • Variáveis.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .doc ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Material de Apoio

A seguir apresento a relação de arquivos  selecionados:

1 – Material de Apoio – Junho – 2018 – Determine Free Space Prior to SQL Server Backup.sql

2 – Material de Apoio – Junho – 2018 – DMV – Sys.dm_os_sys_info.sql – Identificando o último restart realizado na instância ou servidor.sql

3 – Material de Apoio – Junho – 2018 – Identificando a relação de Heap Tables.sql

4 – Material de Apoio – Junho – 2018 – Identificando a última segunda – feira e o último dia do mês.sql

5 – Material de Apoio – Junho – 2018 – Identificando o último usuário que acesso a tabela.sql

6 – Material de Apoio – Junho – 2018 – Obtendo a relação de últimos acessos de leitura e escrita por banco de dados.sql

7 – Material de Apoio – Junho – 2018 – SP_msforeachtable – Criando índices clustered em todas as tabelas através de uma coluna específica.sql

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

Nota: Todos os arquivos disponibilizados foram obtidos ou criados com autorização de seus autores, sendo estes, passíveis de direitos autorais.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo utilize os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2018/04/05/material-de-apoio-abril-2018/

https://pedrogalvaojunior.wordpress.com/2018/02/13/material-de-apoio-fevereiro-2018/

https://pedrogalvaojunior.wordpress.com/2017/11/04/material-de-apoio-novembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/08/08/material-de-apoio-agosto-2017/

Agradecimento

Quero agradecer imensamente a sua visita, sinto-me honrado e orgulhoso de contar com a sua presença.

Não deixe de acessar os outros posts das demais sessões, o próximo post desta sessão será publicado no mês de agosto, até lá continue curtindo sua vida, aproveite este grande momento de confraternização mundial que a Copa do Mundo de Futebol nos proporciona a cada quatro anos.

Um forte abraço, muita saúde, sucesso e vamos em frente…

Short Scripts – Fevereiro 2018 – Transaction Log


Olá, bom dia, mais uma semana começando….

E você já esta aqui acessando o meu blog, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 32 posts, sendo estes publicados trimestralmente.

Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Mas como este é o primeiro post desta sessão em 2018, farei algo um pouco diferente, você terá uma pequena surpresa.

Desejo que o conteúdo aqui compartilhado possa lhe ser útil, como também sirvo de referência e sugestões para novas formar de resolução de problemas e aprendizado.

Vamos então conhecer um pouco mais sobre este novo post….

O post de hoje

Como já destacado no início do post, ao invés de compartilhar os últimos scripts adicionados a  minha biblioteca, quero dividir com você um conteúdo dedicado especificamente a um assunto muito importante quando nos referimos ao Microsoft SQL Server, mais especificamente ao Transaction Log (Log de Transações), funcionalidade presente em todos os bancos de dados criados em qualquer versão e edição do SQL Server.

E ai que você achou desta surpresa, gostou? Eu gostei, não é fácil você conseguir encontrar em um único local um conteúdo focado exclusivamente a este assunto tão importante, que muitos profissionais que trabalham com banco de dados até hoje não conseguem entender o conceito e forma de atuação do Transaction-Log.

Seguindo em frente, a seguir apresento os códigos e exemplos selecionados para o Short Script – Fevereiro 2018 – Transaction Log. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Short Scripts

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos:

— Short Script 1 – Log Record —

— Altera o Recovery Model para SIMPLE
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Truncar o Transaction Log —
CHECKPOINT
Go

— Conteúdo do log – todas as colunas —
USE AdventureWorks2016
Select * from ::fn_dblog(null, null)
Go

–update
Begin Transaction

UPDATE dbo.Pessoa
SET nome = ‘XUXA’
Where ID=3

Rollback
Go

— Conteúdo armazenado no Log File —
Select [Current LSN],
Operation,
Context,
[Transaction ID],
[Log Record Length],
[Previous LSN],
AllocUnitName,
[Page ID],
[Slot ID],
[Checkpoint Begin],
[Checkpoint End],
[Minimum LSN],
SPID,
[Begin Time],
[Transaction Name],
[Parent Transaction ID],
[Lock Information],
Description,
[RowLog Contents 0],
[RowLog Contents 1],
[Log Record]
From ::fn_dblog(null, null)
Go

— DBCC SQLPERF —
DBCC SQLPERF(LOGSPACE)
Go

— Short Script 2 – CheckPoint —

— Criando a Base de Dados —
CREATE DATABASE DemoCheckpoint
ON PRIMARY
(NAME = ‘DemoCheckpoint_data’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_data.mdf’)
LOG ON
(Name = ‘DemoCheckpoint_Log’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_log.ldf’,
SIZE = 100MB,
FILEGROWTH = 10MB)
GO

— Alterando o Recovery Model —
ALTER DATABASE DemoCheckpoint
SET RECOVERY SIMPLE
Go

— Criando a Tabela Teste —
USE DemoCheckpoint
GO

CREATE TABLE Teste
(C1 varchar(50) NOT NULL,
C2 varchar(50) NOT NULL)
GO

— Forçando o Checkpoint —
CHECKPOINT
Go

— Abrir o Perfmon com os contadores

— em outra sessão
USE DemoCheckpoint
GO

WHILE 1=1
BEGIN

INSERT INTO dbo.teste
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Short Script 3 – Log Chain Simple —

— Iniciar nova sessão do Perfmon —
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Alterar Recovery Model para Simple —
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Abrir nova Query —
USE AdventureWorks2016
Go

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Alterar Recovery Model para Full —
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
GO

— Realizar Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backupcompress.bak’
WITH COMPRESSION,
DIFFERENTIAL
GO

— Short Script 4 – DBCC LogInfo —

— Criando uma nova base de dados —
CREATE DATABASE TestDB
ON PRIMARY
(NAME = ‘TestDB_data’,
FILENAME = ‘D:\MSSQL\TestDB_data.mdf’)
LOG ON
(Name = ‘TestDB_Log’,
FILENAME = ‘D:\MSSQL\TestDB_log.ldf’,
SIZE = 10MB,
FILEGROWTH = 10MB)
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

–Forçando o crescimento do Transact-Log manualmente em 20MB —
ALTER DATABASE TestDB
MODIFY FILE
(NAME = ‘TestDB_Log’,
SIZE = 20MB);
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

— Short Script 5 – Natureza Circular —

— Alterando Recovery Model FULL —
ALTER DATABASE TestDB
SET RECOVERY FULL;
Go

— Realizando Backup Database —
BACKUP DATABASE TestDB
TO DISK = ‘D:\TestDB.bak’
Go

— Forçando o encolhimento do Transaction – Log —
DBCC LOGINFO(TestDB)
Go

BACKUP LOG TestDB
TO DISK = ‘bkplogTestDB.trn’
Go

USE TestDB
Go

DBCC SHRINKFILE (TestDB_Log,1)
Go

DBCC LOGINFO(TestDB)
Go

— Criando uma nova Tabela —
USE TestDB
GO

CREATE TABLE dbo.pessoa
(ID int identity PRIMARY KEY NOT NULL,
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL,
Nascimento date NOT NULL,
Cargo varchar(50))
GO

— Abrir nova query —
USE TestDB
GO

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘Junior’, ‘Galvão’, ‘19800428’, ‘Database Administrator’)
END

— Monitorar o crescimento do log em tempo de execução —
DBCC LOGINFO(TestDB)

CHECKPOINT

SELECT name,
Log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘TestDB’
Go

— Realizar Backup do Arquivo de Log —
BACKUP LOG TestDB TO DISK = ‘d:\log.trn’
Go

— Alterando Recovery Model para Full
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
Go

— Realizando novo Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
WITH COMPRESSION
Go

— Short Script 6 – Backup and Transaction Log —

— Preparando a base – 1m10s se não preparada na demo 5
ALTER DATABASE AdventureWorks2016 SET RECOVERY FULL
GO
BACKUP DATABASE AdventureWorks2016 TO DISK = ‘d:\backup.bak’ WITH COMPRESSION
GO

— Realizando Backup do Arquivo de Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Encolhendo o Transaction Log —
USE AdventureWorks2016
Go

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Ajustando o tamanho do Transaction Log —
USE AdventureWorks2016
Go

— Encolhendo o Transaction Log —
DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Modificando o arquivo de Log —
ALTER DATABASE AdventureWorks2016
MODIFY FILE
(NAME = AdventureWorks2016_Log,
SIZE = 4MB)
Go

— Encolhendo o Transaction Log —
DBCC LOGINFO(AdventureWorks2016)

— Abrir nova query —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
Go

— Abrir nova query —
USE AdventureWorks2016
GO

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END
Go

— Forçando o Truncate do Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtedo informações do arquivo de log —
DBCC LOGINFO(AdventureWorks2016)
CHECKPOINT
SELECT name,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Ajustando o tamanho do TLog
USE AdventureWorks2016
GO

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
GO

DBCC LOGINFO(AdventureWorks2016)
Go

— Short Script 7 – File Growth —

— Habilitando Trace Flags para evidênciar mudanças no Log —
DBCC TRACEON (3004, 3605, -1);
Go

— Limpar o log do SQL Server —
sp_cycle_errorlog
Go

— Criar uma nova Base de Dados —
CREATE DATABASE TransactionLog
ON PRIMARY
(NAME = ‘TransactionLog_data’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_data.mdf’,
SIZE = 10240MB)
LOG ON
(Name = ‘TransactionLog_Log’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_log.ldf’,
SIZE = 1024MB,
FILEGROWTH = 1024MB)
GO

— Identificar o tempo decorrido para processamento relacionado somente ao Log —
xp_readerrorlog
Go

— Desabilitando Trace Flags —
DBCC TRACEOFF (3004, 3605, -1);
Go

— Short Script 8 – Open Transaction in Tracking —

— Identificando as Transações abertas e seu respectivo consumo do Transaction Log —
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions [s_tdt] Inner JOIN sys.dm_tran_session_transactions [s_tst]
ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
Inner JOIN sys.[dm_exec_sessions] [s_es]
ON [s_es].[session_id] = [s_tst].[session_id]
Inner JOIN sys.dm_exec_connections [s_ec]
ON [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN sys.dm_exec_requests [s_er]
ON [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY [Begin Time] ASC;
Go

É isso ai, missão cumprida! Mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.

Quero agradecer ao amigo Luiz Mercante que colaborou com a criação e uso destes scripts em algumas palestras realizadas nos últimos anos no evento SQL Saturday.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/09/16/short-scripts-setembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/06/08/short-scripts-junho-2017/

https://pedrogalvaojunior.wordpress.com/2017/03/31/short-scripts-marco-2017/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de maio de 2018.

Sucesso….

#15 – Para que serve


Bom dia, bom dia, bom dia!

Oi gente, tudo bem? Você que esta acessando mais um post do meu blog, pode estar se perguntando. Cara como pode um pessoa ás 6:30hrs de uma quarta – feira esta acordado escrevendo mais um post.

A resposta será bem simples, isso se chama profissionalismo e respeito aos seus compromissos, e escrever algo para o meu blog é mais que um compromisso é um grande prazer, por isso estou aqui ás 6:32hrs da manhã terminando este parágrafo (kkkkk).

Dando continuidade, este é o novo post da sessão Para que serve, sendo o post de número 15, muito bom, lentamente esta sessão esta ganhando corpo e força com os meus seguidores.

Nos últimos dias pesquisei novidades, recursos, comandos, enfim algo que poderia trazer para vocês hoje e sinceramente falando tive bastante dificuldade para encontrar algum conteúdo que fosse ao mesmo tempo interessante porém simples, e por incrível que pareça acabei me lembrando de algo lançado já faz um tempinho na versão 2014 do Microsoft SQL Server.

Poxa vida, versão 2014 do SQL Server sendo que já estamos na versão 2017 prestes a ser lançada, então não sempre algo que foi lançado a algum tempo pode ser considerado novo muito menos totalmente conhecido, sempre temos alguma coisa nova para conhecer, aprender e descobrir com produtos e suas versões mais antigas e foi justamente pensando nisso que estou trabalhando no conteúdo para este post.

Seguindo como a costumeira apresentação, vou destacar neste post um dos recursos mais importantes adicionados ao SQL Server a partir da versão 2014 conhecido como Native Backup Encryption ou Backup Nativo Encriptado, talvez você nunca tenha ouvido falar sobre ele ou não tenha até o presente momento a necessidade de usar, mas tenha a certeza um é um recurso de fácil utilização.

Então chegou a hora de conhecer um pouco mais sobre esta funcionalidade, sua forma de uso, características, importância, limitações, entre outros.

Desta forma, seja bem vindo ao #15 – Para que serve – Native Backup Encryption.

Introdução

Quando pensamos nas possibilidades de perda de dados ou informações, normalmente um dos recursos mais conhecidos e utilizados por todos é o bom e velho backup, capacidade que ao longo dos anos também evoluiu muito e hoje pode ser feito de maneira muito simples, tanto para um pen-drive como diretamente para um repositório disponibilidade de maneira on-line não tão falada e prosperada Cloud Computing.

Mas se fazer o backup é algo simples, imagine então o processo de restauração deste conteúdo que também se torna cada vez mais ágil, rápida e fácil. Você já pensou nisso? Não adianta fazer o backup e pensar “estou seguro, fiz o backup do meu banco de dados, quando eu precisar basta restaurar”, parece ser algo que nunca vai acontecer, mas não é o que atualmente estamos vendo.

Pensando neste sentido seu eu que pergunto: “E se por acaso o seu backup foi roubado, sequestrado, enfim alguém mal intencionado acabou se apoderando dos seus dados?” Isso parece ser bastante assustador e perigoso, foi justamente pensando nisso que a partir da versão CTP2 do Microsoft SQL Server 2014, o time de engenheiros, desenvolvedores e especialistas da Microsoft decidiram adicionar de forma nativa a capacidade de criarmos backups diretamente em uma instância ou servidor SQL Server fazendo uso de criptografia de dados através dos já conhecidos algoritmos, por mais simples que isso possa parecer até a versão 2012 do Microsoft SQL Server não tínhamos esta funcionalidade disponibilidade no produto de forma nativa e totalmente suportada para nossos bancos de dados, tínhamos a necessidade de utilizar ferramentas de terceiros para aplicar este tipo de recurso.

Native Backup Encryption

Através desta nova funcionalidade ao executar um procedimento ou rotina de backup de banco de dados, o Microsoft SQL Server sabendo da escolha deste recurso além de criar um arquivo contendo todo conteúdo estabelecido para o banco de dados selecionado, também realizará para o mesmo arquivo que esta sendo criado a aplicação de uma camada de criptografia de dados, onde de uma maneira direta o conteúdo armazenado neste arquivo de backup estará totalmente criptografado.

Dentre as principais características existentes para esta funcionalidade, para que esta capacidade de adicionar uma camada de criptografia diretamente para todo o backup, torna-se necessário o uso de alguns recursos adicionais em nosso banco de dados para que seja possível criarmos backups criptografados, estou me referindo ao uso de certificados e chaves assimétricas em conjunto com os algoritmos suportados pelo SQL Server sendo eles:

  • AES 128;
  • AES 192;
  • AES 256; e 
  • Triple DES.

Utilizando o Native Backup Encryption

Como já destacado anteriormente, antes de criarmos um backup criptografado de nosso banco de dados, temos a necessidade de criamos um certificado de segurança para garantir que todo conteúdo existente esta sendo validado e possui um mecanismo de segurança.

Para começarmos, vamos realizar o primeiro passo que consiste na criação do nosso Banco de Dados chamado NativeBackupEncryption, em seguida criaremos nossa chave assimétrica e na sequência o certificado denominado CertNativeBackupEncryption. Vale ressaltar, que tanto o certificado como também a chave assimétrica serão obrigatoriamente armazenadas na banco de dados de sistema Master. Para isso utilizaremos o Bloco de Código 1 apresentado a seguir:

— Bloco de Código 1 —
Create Database NativeBackupEncryption
Go

Use Master
Go

Create Master Key Encryption By Password = ‘Backup@@01’
Go

Create Certificate CertNativeBackupEncryption
With Subject = ‘Certificado para Criptografia de Backup’;
Go

Perfeito o primeiro passo já foi realizado e podemos observar nas árvores de recursos do nosso banco de dados que tanto o certificado como principalmente a chave assimétrica estão criadas, conforme ilustra a Figura 1 apresentada abaixo:

Figura 1 – Certificado CertNativeBackupEncryption criado.

Nosso segundo passo também é um dos mais importantes, para conseguirmos aplicar a criptografia em nosso backup de dados, consiste basicamente no procedimento de backup da nossa chave assimétrica em conjunto com o backup do certificado CertNativeBackupEncryption, para que posteriormente seja possível realizar o backup criptografado.

Vale ressaltar que se este procedimento não venha a ser realizado o Microsoft SQL Server durante o processo de Backup Database emitirá um alerta informando a necessidade que este procedimento venha a ser realizado.

Vamos então executar o segundo passo através do Bloco de Código 2 apresentado na sequência:

— Bloco de Código 2 —

Backup Certificate CertNativeBackupEncryption
To File = ‘S:\MSSQL-2016\Backup\Backup-Certificate-CertNativeBackupEncryption.cert’
With Private Key
(
File = ‘S:\MSSQL-2016\Backup\Backup-Master-Key-File.key’,
Encryption By Password = ‘Backup@@01’
)
Go

Legal, legal, conseguimos realizar o backup da nosso Certificado e também do nossa Chave Assimétrica, observe que no procedimento de backup do certificado estamos informando o uso do nossa chave assimétrica na instrução With Private Key, passando como parâmetros os mesmos valores informados para o backup da chave.

A Figura 2 ilustra o local de armazenamento dos arquivos gerados após o backup da chave assimétrica e do certificado:

Figura 2 – Arquivos de backup da chave e certificados criados e armazenados.

Importante: Por questões de facilidade os arquivos de backup foram criados no mesmo local, mas pensando em segurança e boas práticas é altamente recomendável que cada arquivo de backup seja criado e armazenado em locais distintos por questões óbvias de segurança.

Agora que os backups de chave assimétrica e certificados foram realizados, vamos executar nosso último passo que consiste justamente na realização do Backup do nosso banco de dados NativeBackupEncryption aplicando as técnicas de compressão de dados para economia de espaço em disco e principalmente o uso da opção Encrytpion que nos permite escolher o algoritmo de criptografia e qual certificado a nível de servidor vamos utilizar, sendo assim, podemos executar o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 —
Backup Database NativeBackupEncryption
To Disk = ‘S:\MSSQL-2016\Backup\Backup-NativeBackupEncryption.Bak’
With Compression,
Encryption
(Algorithm = AES_256,
Server Certificate = CertNativeBackupEncryption)
Go

Muito bem, como todo procedimento de backup, ao final da execução do comando Backup Database o Management Studio apresenta aquele tradicional conjunto de informações relacionadas ao nosso backup, algo que também não é diferente quando fazendo uso de um backup criptografado. A Figura 3 apresentado o arquivo de backup Backup-NativeBackupEncryption.Bak criado e armazenado após a conclusão da execução do comando Backup Database:

Figura 3 – Arquivo NativeBackupEncryption.Bak criado e armazenado em disco.

Estamos quase no final, continuando mais um pouco, vamos garantir e comprovar que realmente nosso backup foi criptografado. Você pode estar querendo ter a certeza que nosso backup esta criptografado, para realizarmos as conhecida prova dos nove, vamos fazer uso do tradicional comando Restore HeaderOnly, através do Bloco de Código 4 declarado abaixo:

— Bloco de Código 4 —

Restore HeaderOnly
From Disk = ‘S:\MSSQL-2016\Backup\Backup-NativeBackupEncryption.Bak’
Go

Para ilustrar o resultado obtido apos a execução do bloco de código 4, podemos observar os valores apresentados nas colunas: KeyAlgorithm, EncryptorThumbprint e EncryptorType, conforme apresenta a Figura 4.

Figura 4 – Informações referentes ao uso da criptografia no arquivo de backup.

Note que estão sendo apresentados para as respectivas colunas o algoritmo que utilizamos no procedimento de backup e seus respectivos encryptors, mecanismos utilizados para aplicar a criptografia.

Sensacional, conseguimos criar um backup com criptografia de seu conteúdo de forma nativa, sem ter a necessidade de utilizar ferramentas ou recursos de terceiros, fazendo uso total das funcionalidades e características existentes no Microsoft SQL Server. Mesmo assim, alguns pontos importantes devem ser destacados antes de concluirmos mais um post, a seguir destaco os benefícios e limitações do Native Backup Encryption.

Benefícios

  1. O uso deste tipo de recurso com certeza poderá trazer aos organizações e profissionais de banco de dados um grande benefício no que se relacionada as questões de segurança e armazenamento de dados após o processo de backup.
  2. Caso você esteja utilizando atualmente uma ferramenta de terceiros para backups criptografados, você pode comparar essa ferramenta com a funcionalidade e o desempenho de backups criptografados nativos e ver se isso preenche sua exigência.

Limitações

  1. O Native Backup Encryption não esta disponível nas edições Express e Web do Microsoft SQL Server.
  2. O processo de appending capacidade de abrir um arquivo de backup já existente e adicionar o novo conteúdo ao seu final não é suportado para backups criptografados.

Referências

https://blogs.technet.microsoft.com/dataplatforminsider/2013/10/17/sql-server-2014-ctp-2-now-available/

https://www.pythian.com/blog/sql-server-2014-ctp-2-native-backup-encryption/

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql

Links

Caso você ainda não tenha acessado os posts anteriores desta sessão, fique tranquilo é fácil e rápido, basta selecionar um dos links apresentados a seguir:

https://pedrogalvaojunior.wordpress.com/2017/04/30/14-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/03/25/13-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/01/23/12-para-que-serve/

Conclusão

Durante muito tempo este foi um dos recursos mais esperados e aguardos pelos profissionais do Microsoft SQL Server, principalmente pela necessidade até então da aquisição de ferramentas de terceiros, o que gerava custos, bem como, para realizar um procedimento simples trabalhar com dois produtos distintos ao mesmo tempo, o que para alguns pode parecer dificultoso.

Neste post fizemos uso do algoritmo AES_256 considerado por muitos profissionais um dos mais seguros, mas vale a pena fazer uso e comparação dos demais para justamente identificar suas diferenças de comportamento ainda mais se levarmos em consideração diferenças no tempo de execução de um backup criptografado com outro algoritmo.

Mas esse desafio e análise vou deixar para você!!!

Agradecimentos

Antes de finalizar, são 8:54hrs da manhã, estou terminando o post, mas com um lindo dia me esperando para estudar e trabalhar, faça você isso também aproveite a sua vida, pois ela passa muito rápido.

Espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.

Um forte abraço, até o próximo post da sessão Para que serve…..

Valeu.

Dica do Mês – Default Trace To Audit Events – Backup and Restore


Hello everybody!!!

Tudo bem? Final de mês chegando e o que isso significa para você?

Se fosse aquele apresentador do programa do fim de noite do domingo poderia dizer, NADA, mas no meu blog todo final de mês é sempre muito importante e aguardado, é a hora de mais um post dedicado a sessão dica do mês ser publicado, alias se você ainda não conheço ou acessou os dois primeiros posts desta nova sessão não perca seu tempo, utilize um dos links abaixo e conheça mais sobre esta na área do meu blog dedicada especialmente em apresentar como um recurso ou script pode se tornar uma dica extremamente útil:

Vamos em frente, no post de hoje vou destacar um velho e conhecido assunto existente no Microsoft SQL Server e bastante difundido a partir da versão 2005, denominado Default Trace File (Arquivo de rastreamento/monitoramento padrão) em conjunto com os Audit Events (Eventos de Auditoria) introduzidos a partir da versão 2008, ambos os recursos combinados se tornando uma fantástica ferramenta para consulta e monitoramento das atividades, tarefas e processos em execução no Microsoft SQL Server.

Como de costume, vou apresentar um pouco sobre estes recursos de forma breve e resumida, tentando ilustrar sua importância:

  • Default Trace File: Surguiu da necessidade que os profissionais de banco de dados em suas atividades monitoramento tinham em colocar dados sobre o que estava sendo executado em seu servidor ou instância SQL Server de uma maneira mais “aberta”, ou seja, diferente do que acontecia na versão 2000 onde o monitoramento era realizado de uma forma conhecida como “caixa preta”, sem possibilitar algum tipoo de interação.A partir da versão 2005 este recurso foi aprimorado, se tornando uma grande ferramenta na coleta de dados sobre o comportamento do SQL Server. Com esta evolução muitas respostas para problemas de performance, deadlock e troubleshotting começaram a ser respondidas e entendidas de forma mais rápida, fácil e dinâmica. Em diversos momentos o Default Trace File, foi considerado uma ferramenta valiosa e moderna para os profissionais de Banco de DAdos – DBA.

    Oferece uma riqueza de informações, enquanto minimamente, impactando o sistema. O Default Trace File é um recurso amplamente divulgado do SQL Server 2005, mas com o passar do tempo ganhou fama e evolução nas demais versões do produto, por oferece aos administradores a capacidade de obter informações detalhadas sobre:

    • Auditoria de eventos;
    • Eventos de banco de dados;
    • Eventos de erros;
    • Eventos de texto completo;
    • Criação do objeto;
    • Exclusão de objeto; e eventos de alteração do objeto.

 

  • Audit Events: Introduzido a partir da versão 2008, o Audit Events tornou-se uma verdadeira solução de auditoria dentro do Microsoft SQL Server, considerada por muitos um complemento aos recursos e funcionalidades desempenhadas pelo Default Trace File. Composto por um longo conjunto de eventos que a cada nova versão do SQL Server é incrementado com outros os eventos de auditoria possibilitam coletar e catalogar de maneira mais detalhada e repleta de dados todo comportamento de um servidor, banco de dados e objetos existentes em um servidor ou instância SQL Server.Outro fator importante quando falamos dos eventos de auditoria esta relacionado com os pilares de segurança do SQL Server, possibilitando todo controle e monitoramento de acesso a objetos e dados direcionados ao SQL Server, possibilitando detectar possíveis tentativas de acesso não autorizado ou acessões maliciosas por partes de usuários com acesso legítimo.

Após este breve resumo sobre estes dois recursos, você pode estar se perguntando como ou quando podemos fazer uso deste tipo de funcionalidade? A resposta é bem simples, de diversas forma, dentre elas apresento algumas:

  • Captura de eventos e ocorrência de eventos;
  • Captura da lista e detalhes de eventos;
  • Informações sobre crescimento de um banco de dados;
  • Informações sobre erros e alertas disparados pelo SQL Server;
  • Informações sobre alertadas de ordenação de dados;
  • Quando um procedimento de Shrink foi realizado em um banco de dados ou arquivo de log;
  • Quando um determinado comando DBCC foi executado;
  • Quando um Backup de Banco de Dados ou Restore de Banco de Dados foi realizado; e
  • Pesquisa de informações sobre auto estatísticas ou eventos de estatísticas.

Enfim sobre muita coisa relacionada ao SQL Server é possível fazer uso de um Default Trace File em conjunto com o Audit Events, desta maneira na dica de hoje escolhi uma das maneiras mais comuns porém não menos importante para obter informações sobre os eventos relacionados a execução de Backup Database e Restore Database através do dois exemplos apresentados a seguir:

 


 

— Exemplo 1 – Obtendo informações sobre a ocorrência de Backup Database —

Declare @path NVARCHAR(260)

Select @path=path From sys.traces Where is_default = 1

 

SELECT DatabaseName, TextData,

             Duration, StartTime,

             EndTime, SPID,

             ApplicationName, LoginName

FROM sys.fn_trace_gettable(@path, DEFAULT)

WHERE EventClass IN (115)

and EventSubClass=1

ORDER BY StartTime DESC

Go

 

 

— Exemplo 2 – Obtendo informações sobre a ocorrência de Restore Database —

Declare @path NVARCHAR(260)

Select @path=path From sys.traces Where is_default = 1

 

SELECT DatabaseName, TextData,

             Duration, StartTime,

             EndTime, SPID,

             ApplicationName, LoginName

FROM sys.fn_trace_gettable(@path, DEFAULT)

WHERE EventClass IN (115)

and EventSubClass=2

ORDER BY StartTime DESC

 

Espero que você tenha observado que ambos os códigos de exemplo são bem similares, o que determina qual conjunto de informações será apresentada de acordo com o evento é a coluna EventSubClass, onde:

  • EventSubClass = 1 — Representa as informações sobre a sub-classe de eventos relacionada a ocorrência de Backup;
  • EventSubClass = 2 — Representa as informações sobre a sub-classe de eventos relacionada a ocorrência de Restore; e
  • EventSubClass = 3 — Representa as informações sobre a sub-classe de eventos relacionada a ocorrência de Backup Log.

Caso você queira saber mais sobre os eventos de auditoria relacionadas a Backup/Restore acesse:
https://msdn.microsoft.com/en-us/library/ms175015.aspx ou https://msdn.microsoft.com/en-us/library/ms175481.aspx para obter mais sobre a classe de eventos existente no SQL Server.

sys.fn_trace_gettable e sys.traces existentes nas atuais versões do SQL Server 2012 e 2014, segundo a documentação oficial da Microsoft ambas catalog views serão removidas nas futuras versões do produto.

 


 

É isso ai galera, chegamaos ao final de mais uma dica de mês. Com certeza uma dica bastante diferente que forma como o SQL Server é composto por diversos recursos aparentemente independentes e isolados mas que podem ser utilizados em conjunto formando uma grande ferramenta de trabalho.

Espero que você tenha gostado, nos encontramos no final do mês de Abril com mais uma dica do mês.

Um grande abraço, obrigado por sua visita.

Até mais.

Microsoft SQL Server 2016 – Lista de Novidades – Final


Bom dia, bom dia, bom dia!!!!!

Meu deus que friooooo, neste momento 4.5º graus de temperatura em São Roque e região, para começar o dia esquentando nada como tentar escrever mais um post no meu blog, posso dizer que não é uma missão fácil, pois a chuva de conteúdo que esta na internet sobre o novo Microsoft SQL Server 2016 é algo fora do comum, isso sem falar do lançamento do Microsoft Windows 10 que esta bombando no mundo todo.

Mas deixando esta friozinho de lado e seguindo em frente, vou finalizar esta série de posts relacionados as principais novidades do SQL Server 2016, caso você não tenha acessado os outros dois, segue abaixo os links para sua diversão:

Para finalizar esta série, vou destacar na lista de principais novidades liberadas pela Microsoft as seguintes features:

  • Strech Database;
  • Row-Level Security; e
  • Dynamic Data Masking.

Analisando os nomes das features parece que esta se referindo a algo de outro mundo, mas na verdade não é bem assim, todas elas fazem parte de uma lista de solicitações de profissionais da área de banco de dados, que constantemente estão solicitando aos times dos mais diversos níveis de relacionamento com o SQL Server: http://blogs.technet.com/b/sqlserverbrasil/, http://blogs.msdn.com/b/pfebrasilsql/a introdução destes recursos.

Vamos lá:

Strech Database: Outra nova funcionalidade bastante esperada principalmente para os usuários do Azure, através do Strech Database, será possível armazenar porções (partes) de uma tabela no Azure SQL Database, você pode estar se perguntando, como assim partes de uma tabela em outro local e não no meu banco de dados, posso dizer que também fiquei surpreso, mas tudo tem uma explicação.

Através deste recurso, temos a capacidade de armazenar dados históricos contidos em uma tabela de forma segura e transparente diretamente na nuvem, ou melhor dizendo no Microsoft Azure. A partir do momento que este recurso é habilitado, de forma silenciosa os dados considerados históricos são migrados para um banco SQL Azure, tudo isso é feito pelo SQL Server sem exigir qualquer alteração de código em sua query ou aplicação.

Para saber mais sobre este recurso acesse:

 

Row-Level Security: Esta nova funcionalidade poderá ser considerada algo bastante revolucionário no que se dizer respeito a visibilidade e acesso aos dados de uma tabela. A Row-Level Security vai permitir aos DBAs e profissionais da área de banco de dados, realizar um controle de acesso aos dados que estão armazenados em determinadas tabelas, através do uso de funções conhecidas como Predicate, limitando assim que uma possível coluna e seu respectivo valor seja consultado.

Veja um exemplo abaixo que pode ser aplicado já na versão CTP 2.1 e CTP 2.2 do SQL Server 2016:

— Criando novas contas de usuários –

CREATE USER Manager WITHOUT LOGIN;

CREATE USER Sales1 WITHOUT LOGIN;

CREATE USER Sales2 WITHOUT LOGIN;

Go

 

— Criando a Tabela Sales —

CREATE TABLE Sales

(

OrderID int,

SalesRep sysname, — Este é um dos segredos para o RSL funcionar.

Product varchar(10),

Qty int);

Go

— Inserindo dados na tabela –

INSERT Sales VALUES

(1, ‘Sales1’, ‘Valve’, 5),

(2, ‘Sales1’, ‘Wheel’, 2),

(3, ‘Sales1’, ‘Valve’, 4),

(4, ‘Sales2’, ‘Bracket’, 2),

(5, ‘Sales2’, ‘Wheel’, 5),

(6, ‘Sales2’, ‘Seat’, 5);

Go

 

— Consultando os dados —

SELECT * FROM Sales;

Go

 

— Atribuíndo a permissão de Grant para cada usuários na tabela Sales –

GRANT SELECT ON Sales TO Manager;

GRANT SELECT ON Sales TO Sales1;

GRANT SELECT ON Sales TO Sales2;

Go

 

— Criando o Schema Security –

CREATE SCHEMA Security;

GO

 

— Criando a Função Predicate – Security.fn_securitypredicate –

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS fn_securitypredicate_result

WHERE @SalesRep = USER_NAME() OR USER_NAME() = ‘Manager’;

 

— Criando a Política de Segurança para filtrar e controlar o acesso aos Dados –

CREATE SECURITY POLICY SalesFilter

ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)

ON dbo.Sales

WITH (STATE = ON);

 

— Realizando o teste de acesso aos dados –

EXECUTE AS USER = ‘Sales1’;

SELECT * FROM Sales;

REVERT;

 

EXECUTE AS USER = ‘Sales2’;

SELECT * FROM Sales;

REVERT;

 

EXECUTE AS USER = ‘Manager’;

SELECT * FROM Sales;

REVERT;

 

Após executar este bloco de código você vai poder observar que o usuário Manager deverá ter conseguido consultar todos os dados da Tabela Sales, já os usuários Sales1 e Sales2 devem ter visto somente 3 cada respectivamente.

 

— Agora vamos desativar a política de segurança –

ALTER SECURITY POLICY SalesFilter

WITH (STATE = OFF);

Go

 

Com isso todos os usuários vão conseguir obter todos os dados da tabela Sales.

Dynamic Data Masking: Traduzindo ao pé da letra – Mascaramento de dados dinâmicos, poxa vida, dizer que o SQL Server é mascarado é brincadeir(kkkkk), na verdade este recurso possibilita que seja aplicado diretamente ao dado um máscara, isso mesmo, definir uma máscara para personalizar as informações que serão apresentadas para o usuário em colunas com dados sensitivos.

O Dynamic Data Masking, limita a exposição de dados confidenciais mascarandoo para usuários não-privilegiados. Mascaramento de dados dinâmico ajuda a evitar o acesso não autorizado a dados confidenciais, permitindo aos clientes designar o quanto os dados confidenciais para revelar com impacto mínimo na camada de aplicação. É uma característica de segurança que esconde os dados no conjunto de resultados de uma consulta sobre campos de banco de dados designado, enquanto os dados no banco de dados não são alterados. Considerado de fácil de utilização com aplicativos existentes, desde que as regras de mascaramento sejam aplicadas nos resultados da consulta. Muitos aplicativos podem mascarar dados confidenciais sem modificar consultas existentes.

Mascaramento de dados dinâmicos é complementar a outras características de segurança do SQL Server (auditoria, criptografia, segurança de nível de linha…) e é altamente recomendável usar esse recurso em conjunto com eles, além disso, a fim de melhor proteger os dados confidenciais no banco de dados.

Para conhecer mais sobre este novo recurso acesse:

Ufa, chegamos ao final, mais uma jornada vencida!!!

Agradeço a sua visita ao meu blog, espero que tenho gostado, nos próximos posts tentarei apresentar e exemplificar um pouco mais sobre algumas destas novas funcionalidades.

Um grande abraço.

Microsoft SQL Server 2016 – Lista de Novidades – Parte II


Bom dia, quinta – feira, começando!!!

Salve comunidade, estou retornando com a segunda parte da Lista de Novidades do Microsoft SQL Server 2016, nova versão do SQL Server que neste momento apresenta muitas especulações, comentários e informações nas Internet, principalmente nas redes sociais.

Por este motivo também não poderia ficar de fora, como já realizado na semana passada com a primeira parte desta lista de novidades, caso você não tenha acessado este é o link:
https://pedrogalvaojunior.wordpress.com/2015/07/10/microsoft-sql-server-2016-lista-de-novidades-parte-i/

Nesta segunda parte, vou destacar mais algumas das principais novidades que a Microsoft esta divulgado em seus Blogs e parceiros, hoje darei destaque para:

  • Multiple TempDB Files;
  • For JSON;
  • Always Encripted; e
  • Polybase.

A seguir destaco estas novidades, através de uma breve descrição:

  • Multiple TempDB Files – Funcionalidade muito aguardada a anos pelos profissionais da área de banco de dados, mais especificamente aqueles que trabalham com o SQL Server, onde a partir da versão 2016, teremos a possibilidade de durante a instalação do SQL Server configurar e definir a quantidade arquivos de dados que devem formar a estrutura do banco de sistema TempDB, onde o número de arquivos é definido com base no seu número de processadores que a instância 2016 estará sendo executada. Para saber mais sobre esta nova feature acesse: http://www.sqlservergeeks.com/tempdb-configuration-sql-server-2016-setup/

 

  • FOR JSON – Uma novidade bastante interessante que mostra o quanto a Microsoft esta se dedicando a acompnhar a evolução das tecnologias de Computação em Nuvem e BigData. A FOR JSON, consiste em uma claúsula da linguagem Transact-SQL criada para ajudar o SQL Server a possibilitar a apresentação e saída de dados no formata JSON de forma nativa, algo que vai muito além de apresentar os dados, mas sim ter a capacidade de formatar estes dados interpretados pelo JSON no formato desejado pelo usuário. Para saber mais sobre esta nova feature acesse: https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx#ForJson

 

  • Always Encrypted – Este é um recurso bastante interessante na minha opinião e algo que pode complementar o TDE(Transparent Data Encryption) recurso introduzido no Microsoft SQL Server 2008, com a finalidade de permitir criptografia nativa no nível de banco de dados. O Always Encrypted garantir ainda mais que seus dados estão armazenados de forma segura através deste recurso de criptografia, como também, durante os processos de manipulação dos mesmo. Sua principal característica é permitir que a possibilidade de criptografar dados dentro das aplicações que estão fazendo acesso ao SQL Server, tendo a capacidade de utilizar chaves de criptografia nunca reveladas dentro do processo que realização da criptografia do dado. Como resultado, o Always Encrypted fornece uma separação entre aqueles que possuem os dados (e pode visualizálo) e aqueles que gerenciar os dados (mas deve não têm acesso). Para saber mais sobre esta nova funcionalidade, acesse: https://msdn.microsoft.com/en-us/library/mt163865(v=sql.130).aspx e https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-Always-Encrypted

 

  • Polybase – Na minha opinião uma baita novidade, sinceramente algo que vai muito além de uma novo recurso, mas sim um novo horizonte para os profissionais, desenvolvedores e administradores de banco de dados, funcionalidade que vai permitir um avanço enorme no que se diz respeito a Interoperabilidade do SQL Server com outras tecnologias Non-SQL, como também, tecnologias de acesso e armazenamento de dados dentre elas o Hadoop. O PolyBase é uma nova tecnologia que integra o produto o Microsoft SQL Server Parallel Data Warehouse (PDW), com Hadoop. Ele é projetado para permitir consultas através de dados relacionais armazenados no PDW e dados não-relacionais armazenados no Hadoop de forma distribuída através do sistema arquivos Hadoop (HDFS), ignorando MapReduce distribuído, reconhecido como motor do Hadoop que normalmente é usado para ler dados do HDFS. Você pode criar uma tabela externa em PDW que referencia o Hadoop dados (como um servidor vinculado) e você pode consultar isso com SQL, em essência, adicionando estrutura para dados não-estruturados.

Untitled picturePara maiores informações sobre o Polybase e alguns cenários de uso, acesse: Books Online – Polybase, Polybase Explained, SQL Server 2016 and Polybase, Using Polybase in SQL Server 2016, Polybase in SQL Server 2016 CTP2

Muito bem pessoal, chegamos ao final desta segunda parte da lista de novidades do Microsoft SQL Server 2016, espero que você tenha gostado, na próxima semana chegaremos ao final desta lista, destacando as últimas novidades que Microsoft esta introduzindo no novo SQL Server 2016, dentre as quais chamo sua atenção para: Strech Database e Row-Level Security.

Mais uma vez obrigado por sua visita, fique a vontade para postar suas dúvidas, sugestões, críticas e comentários, sobre este ou qualquer outro post.

Boa semana e até mais.

Microsoft SQL Server 2016 – Lista de Novidades – Parte I


Olá pessoal, bom dia, feriado prolongado no estado de São Paulo.

Mesmo assim, a vida não pode parar e a dissiminação de conhecimento menos ainda, algo primordial em nossas vidas e obrigatória para evolução de todos nós.


Para manter esta tradição nada como trazer informações relevantes e atualizadas, sendo assim, gostaria de destacar um pouco sobre as principais novidades que a Microsoft esta anunciando para a nova versão do Microsoft SQL Server, isso mesmo nova versão, se você nos últimos 2 ou 3 meses não se antenou, já estamos se deparando e caminhando para a versão 2016 do Microsoft SQL Server.

Se você quiser saber mais sobre este preview, recomendo acessar os seguintes links:

http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Dentre as diversas novidades que aparecem na presentes na nova versão, gostaria de destacar algumas que realmente não existem nas atuais, dentre elas:

  • Live Query Statistics;
  • Query Store;
  • Temporal Tables;
  • Backup to Azure;
  • Managed Backup;
  • Multiple TempDB Files;
  • FOR JSON;
  • Always Encrypted;
  • Polybase;
  • Stretch Database;
  • Row-Level Security;
  • Dynamic Data Masking.

Outras funcionalidades também estão passando por mudanças e podem ser consideradas como possíveis novidades, é o caso:

  • AlwaysOn;
  • Columnstore Indexes; e
  • In-Memory OLTP.

Por outro lado, outras funcionalidades devem começar a fazer parte da lista de recursos e funcionalidades que descontinuadas ou futuramente descontinuadas, até o presente momento a Microsoft não disponiblizou nenhum lista oficial contendo este itens.

Mas voltando a destacar as novas funcionalidades, nem sempre todas estes itens considerados novos são realmente utilizados, em muito casos existe sempre uma insegurança, medo, relutância e até mesmo desconhecimento sobre como uma ou outra novidade pode ajudar, isso é muito comum de se deparar, as mudanças geram incertezas e inseguranças.

Para tentar ajudar você que esta lendo este post a saber um pouco mais sobre este recursos e de maneira eles podem fazer parte do seu projeto, vou fazer uma breve descrição de alguns deles, e conforme novas informações, exemplos e cenários forem sendo disponibilizados, com certeza, vou procurar compartilhar com você isso.

Vamos lá, vou começar por algo que eu gosto muito no SQL Server, estou falando de estatísticas e na versão 2016, temos a:

  • Live Query Statistics – Uma das funcionalidades que pode surpreender a vida dos DBAs e desenvolvedores, a Live Query Statistisc permite que você possa exibir as estatísticas de consulta ao vivo de uma consulta ativa, proporcionando insights em tempo real, isso é surpreende, saber ao vivo e a cores o que uma consulta que esta ativa, em funcionamento no SQL Server poderá nos propor de decisão.

 

  • Query Store – Outra fantástica funcionalidade que vai ajudar em muito os DBAs na sua longa jornada de análise de planos de execução, a Query Store vai possibilidade fazer a análise de uma plano de execução que possa estar apresentando problemas de desempenho através de uma “indicação” ou “orientação” por parte do SQL Server, poder escolher um plano de execução para processar uma query, olha algo revolucionário.

 

  • Temporal Tables – O nome desta funcionalidade realmente é um pouco estranho, pelo menos na sua leitural, mas com certeza a sua finalidade é bastante interessante e vejo como um grande diferencial. A temporal tables, consiste basicamente em uma tabela que fornece uma exibição de dados em um determinado momento de tempo, isso mesmo, você vai conseguir obter informações sobre os dados de uma tabela, através de uma visão histórica, como se você estivesse voltando ao passado, voltando no tempo a posição de dados daquela tabela.

 

  • Backup to AzureO Backup para Azure recurso é projetado para permitir que você tire um backup do seu banco de dados no local diretamente para o armazenamento de Azure blog, algo que hoje pode ser feito através de recursos e ferramentas de terceiros, mas estará totalmente integrado e funcionando de forma nativa no versão 2016.

 

  • Managed BackupO recurso relacionado com o Microsoft Azure e conhecido como “Backup gerenciado” foi projetado com a finalidade de automatizar backups para o armazenamento de Blob do Azure, muito legal esta funcionalidade, o seu grande diferencial esta justamente na forma que vamos poder gerenciar e administrar os backups de nossos bancos de dados armazenados na estrutura do Azure.

Muito legal estas novas funcionalidades, realmente a Microsoft esta trabalhando forte no SQL Server, na sua capacidade de integração e interoperabilidade.

Vou fazer um pequeno suspense e deixá-los com água na boca em relação as outras novidades, na próxima semana eu retorno com a Parte II desta lista de novidades, destacando como por exemplo: Polybase e Strech Database, ambas inovações presentes na versão 2016 que terão um papel fundamente para o  SQL Server na sua capacidade intregação e interoperabilidade com ferramentas de Bigdata e Hadoop.

Espero que você tenha gostado, até a próxima semana!!!

Abraços.

DBCC Writepage – Considerado o comando mais perigoso existente no Microsoft SQL Server.


Bom tarde, Comunidade e Amantes do Microsoft SQL Server.

Tudo bem? Espero que todos possam ter passado e aproveitado as festas de final de ano, pois realmente 2014 foi um ano passou de forma avassaladora, com tantos acontecimentos esportivos e políticos.

Mas agora é hora de voltar a luta, começar 2015 com muito trabalho e logicamente pensando sempre como podemos conhecer e aprender cada vez mais com o Microsoft SQL Server, através de seus segredos, mas principalmente com alguns dos principais nomes dentre eles: Paul Randal e Bob Ward, peço licença por citá-lo, pois sou um simples mortal e mero DBA perto destes “monstros”.

Seguindo nesta linha de raciocínio, onde me refiro a aprender, tenho a certeza que a cada dia todos nós temos a capacidade e o dever em buscar novos ensinamentos e desafios, procurando compartilhar as lições, aprendizados e até mesmo os riscos que muitas vezes encontramos. É com base nesses riscos e desafios que eu gostaria de destacar o objetivo deste artigo, apresentar para aqueles que ainda não conhecem o tão temido comando DBCC WritePage. Sendo este destacado por Paul Randal em sua frase: “DBCC WRITEPAGE – the most dangerous command you can use in SQL Server.extraída de um dos seus diversos artigos e posts.

 

Introdução

Conhecido por ser um dos comandos DBCC não-documentados pela Microsoft o DBCC Writepage é considerado por muitos, dentre eles Paul Randal, um dos principais segredos existente no SQL Server.

Mas qual seria o motivo de tanto segredo? Ou até mesmo preocupação?

Na verdade o DBCC Writepage esta presente no Microsoft SQL Server desde a versão 2000, mas efetivamente mantido em segredo ou até mesmo escondido de muitos da comunidade.

No entendo, o mesmo passou a ser mais procurado e conhecido a partir do momento em que a Microsoft introduziu na versão 2005 a Trace Flag 2588 que apresenta em conjunto com a relação tradicional de comandos DBCCs, aqueles possivelmente considerados não-documentados ou até mesmo não suportados. Por padrão até a versão 2005 era utilizada da Trace Flag 2520 que omitia justamente os comandos não documentados.

Como destacou Paul Randal, durante muitos anos ele procurou evitar escrever e até mesmo falar sobre este comando, inclusive em conversas com Bob Ward um dos Engenheiros do Time do SQL Server ele dizia: “I discussed this issue with the SQL product team and CSS last week, and they agree with me doing this. You won’t see a Microsoft post about this, but my great friend Bob Ward from CSS wanted me to quote him saying “Microsoft does not support the usage of DBCC WRITEPAGE and its use would invalidate the support for a customer database on which it is used.”

Outro fator importante que podemos analisar é a crescente necessidades dos Administradores de Bancos de Dados, Desenvolvedores e Profissionais de Tecnologia possuem em tentar de alguma forma simular situações, cenários de risco e aprender com este tipo de ambiente, algo que fez a curiosidade pelo DBCC Writepage aumentar.

Eu mesmo durante algum tempo venho brincando com os meus alunos em exercícios e trabalhos quando estou me referindo a Cenários de Desastre e Recuperação de Dados no SQL Server.

Mas afinal o que este comando faz?

A resposta para esta pergunta é bem simples:

O DBCC Writepage permite a alteração de qualquer byte, em qualquer página de dados, que em qualquer bando de dados, desde que o usuário possua privilégios sysadmin. Além disso, permite contornar completamente o pool de buffer, em outras palavras, você pode forçar falhas na estrutura de verificação de página conhecido tecnicamente como CheckSum. Levando-se em consideração esta capacidade podemos dizer que o DBCC Writepage pode ser analisado e entendido como um comando extremamente perigoso quando estamos se referindo a um banco de dados.

Comportamento

Por padrão o comando DBCC Writepage possibilidade ao usuário com poderes administrativos alterar todo conjunto de bytes que estão vinculados ou melhor dizendo compondo uma determinada página de dados. Comportamento que possibilidade atribuir a este comando a capacidade de edição em tempo real da estrutura física e lógica de armazenamento de um banco de dados.

 

Considerações e Preocupações

Por se tratar de um dos comandos não – documentados pela Microsoft, o mesmo não deverá ser utilizado em ambientes de produção, os possíveis riscos e perdas de dados que o Writepage pode proporcionar vão muito além da falha no acesso a uma porção de dados, mas sim, a possibilidade de perda por completa de toda estrutura de um banco de dados.

 

O comando DBCC Writepage foi desenvolvido com objetivo de ajudar as equipes de engenheiros a entender e analisar formas de demonstração e testes de corrupção da estrutura de um banco de dados. Possibilitando uma checagem de forma automatizada do comando DBCC CheckDB na validação e reparação de um banco de dados.

 

Desta forma, vale ressaltar que qualquer teste ou simulação deverá ser aplicada em cenários ou ambientes que não representem riscos, como também, após a execução e validação de um procedimento de backup.

 

Portanto não utilize este comando em ambientes de produção!!!

Onde utilizar

Pesquisando mais informações sobre o DBCC Writepage, encontrei vários posts que destacam qualidades e possíveis situações para uso, dizer que existem somente coisas ruins na utilização deste comando, é errado, existem sim possibilidades relacionadas a: Estudos da estrutura de um Banco de Dados, Backups, Recuperação de dados, técnicas que podemos muitas vezes ilustrar e apresentar em palestras, eventos ou grupos de estudos.

 

Sintaxe

Posso dizer que sua sintaxe pode ser totalmente comparável com seu risco, ou seja, o DBCC Writepage apresenta uma sintaxe composta por um conjunto de parâmetros configuráveis que requer a definição para que o mesmo possa ser executado, conforme apresento abaixo:

Dbcc WRITEPAGE ({‘dbname’ | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])

Parâmetros:

  1. ‘dbname’ | DBID: Nome do Banco de Dados ou Database ID;
  2. FileID: ID que representa o identificador do arquivo que terá sua página alterada;
  3. PageId: Número de página;
  4. offset: Deslocamento em bytes com base no início da página de dados;
  5. length: Número de bytes a serem alterados, entre os valores 1 a 8;
  6. data: Os novos dados para inserir (em hex, sob a forma de ‘0xABABCC’); e
  7. directORbufferpool: Se o buffer pool deve ser ignorado ou não, de acordo com o valor informando (0/1).

Uma consideração muito importante se relaciona com o parâmetro directORbufferpool, caso o valor informado seja 1, o Microsoft SQL Server mudará completamente o controle e forma de uso do Buffer Pool, realizando:

  • Executa pontos de verificação do banco de dados fora das páginas em buffer pool;
  • Quebra o vínculo do FCB (File Control Block) existente no arquivo de dados que forma e compõe o banco de dados;
  • Cria um novo FCB para o arquivo, totalmente independente do FCB já existente;
  • Realiza o direcionamento a leitura de páginas que estão em memória utilizadas naquele momento pelo comando DBCC;
  • Modificação direta de páginas;
  • Gravação direta de páginas de dados em disco, descartando qualquer proteção da página e CheckSum;
  • Ao final realiza novamente a correção dos File Control Block criados durante a execução do Writepage.

Onde o Buffer Pool não consegui identificar o que esta acontecendo, imaginando que possa estar ocorrendo uma possível falha de escrita ou leitura de dados, ou até mesmo a estrutura física tenha sido corrompida.

 

Utilizando o DBCC Writepage

Muito bem, chegamos até aqui e ainda estamos vivos, vamos então brincar um pouco com este recurso fantástico, super perigoso e desafiador!!!

Como de costume nosso cenário será o mais simples possível, para simularmos como o DBCC Writepage pode ser utilizado e principalmente o que ele pode fazer, vou criar um Banco de Dados chamado DatabaseCrazy e executar os demais passos apresentados.

Mãos na massa:

— Passo 1 – Criando o Banco de Dados DatabaseCrazy e a Tabela TB_Crazy –

Create Database DatabaseCrazy

Go

Use DatabaseCrazy

Go

 

Create Table TB_Crazy

(Id Int Identity(1,1) Primary Key,

Title NCHAR (4000) DEFAULT ‘Use the command DBCC Writepage can be crazy’)

Go

 

Insert into TB_Crazy Default Values

Go 10

 

Observe que criamos o nosso banco de dados, em seguida a tabela e inserimos 10 linhas de registro em sua estrutura, consequentemente nossas páginas de dados foram criadas e populadas. Nosso próximo passo será listar toda estrutura de páginas de dados que formam nossa tabela TB_Crazy, para isso, existem diversas possibilidades, mas como estamos falando de comandos não – documentados vamos então utilizar mais um conhecido como DBCC IND, caso queira saber mais sobre este comando acesse: DBCC IND

— Passo 2 – Consultando a estrutura e relação de Páginas de Dados da tabela TB_Crazy —

DBCC IND (N’DatabaseCrazy’, N’TB_Crazy’, -1);

Go

 

Após executarmos o comando DBCC Ind, o SQL Server nos apresentou a relação de páginas de dados que sustentam nossos dados na tabela TB_Crazy, podemos observar a presença de 12 páginas de dados, conforme apresenta a Figura 1, abaixo:

Writepage

Figura 1 – Relação de páginas de dados tabela TB_Crazy.

 

Loucura, loucura, loucura, como diria aquele apresentador, estamos quase lá, agora é a hora da verdade e ver o que o DBCC Writepage faz, vamos então de mãos dadas, executar o próximo passo que consiste na quebra da estrutura física e lógica da nossa tabela TB_Crazy e na sequência tentarmos fazer acesso a mesma.

— Passo 3 – Executando a quebra na página de dados relacionada com a tabela TB_Crazy —

Para que seja possível fazer uso do DBCC Writepage, temos que alterar a forma de acesso do nosso banco de dados para Single_User, para posteriormente fazer a quebra da página de dados.

Alter Database DatabaseCrazy

Set Single_User

Go

 

DBCC WRITEPAGE (N’DatabaseCrazy’, 1, 299, 1250, 1, 0xAA, 1);

Go

 

Ufa, fizemos a alteração em nosso banco de dados, e executamos o DBCC WritePage, configurando o mesmo para:

  • Alterar apágina de dados de número 299;
  • No deslocamento de bytes 1250;
  • Realizando a mudança de 1 byte;
  • Com o valor hexadecimal 0xAA; e mudando o controle do Buffer Pool ao especificar o valor 1 para o parâmetro directOrbufferpool.

Para finalizar vamos consultar nossa tabela TB_Crazy e ver se conseguimos ter acesso aos nossos dados, executando o Select baixo:

Select * from TB_Crazy

Go

 

Meu Deus, este SQL Server é fora do comum, é fantástico, ao executarmos o simples Select, foi possível verificar que conseguimos acessar os registros que não estão vinculados a página de dados 299, conforme apresenta a Figura 2 a seguir:

Writepage2

Figura 2 – Linhas de registros existentes na tabela TB_Crazy que não estão vinculados com a página de dados 299.

 

Em contra partida, o SQL Server nos retorna na guia Messages, uma mensagem de erro, conforme similar a esta:

Msg 824, Level 24, State 2, Line 25

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x015f2e60; actual: 0x415f2e71). It occurred during a read of page (1:299) in database ID 6 at offset 0x00000000256000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DatabaseCrazy.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail.

This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB).

This error can be caused by many factors; for more information, see SQL Server Books Online.

 

Palmas para o SQL Server, esse cara é demais!!!

 

Conclusão

Falar sobre um recurso existente no Microsoft SQL Server não é fácil, falar então sobre um recurso não-documentado e tratado como um grande segredo, posso dizer que foi um grande desafio, ainda mais sobre um recurso que Paul Randal, tratou durante anos como seu próprio filho.

Mas como eu destaquei no começo, os desafios estão presentes em nossas vidas a todos os momentos, temos que procurar vencê-los ou em último caso conviver com eles por algum tempo.

Desta forma, procurei apresentar o comando DBCC Writepage, destacando sua história, sua finalidade e objetivos, como podemos e devemos tratar esta funcionalidade que possui um grande poder e muitas vezes de destruição de todo um ambiente. Mesmo sabendo disso, conseguimos fazer uso, simular a quebra da estrutura física e lógica de uma página de dados, conhecer também de forma breve outro comando não – documentado que é o DBCC IND e principalmente ver que mesmo que nossa tabela possua sua estrutura danificada o SQL Server com todo seu poder e Inteligência conseguiu retornar uma parte destes dados.

Afirmar que o DBCC Writepage é perigoso fica fácil, o desafio é tentar encontrar uma forma de uso sem correr riscos.

——————————————————————————————————————————-

Mais uma vez agradeço a sua visita, espero que você tenha gostado e que este conteúdo possa te ajudar.

Um grande abraço.

Nos encontramos em breve.

Até mais.

Simulando – Desastre e Recuperação de Bancos de Dados – Microsoft SQL Server 2008 R2 e 2012 – Parte Final.


Galera, bom dia.

Tudo bem?

Como prometido estou retornando com mais um post da série referente à Simulação de Desastre e Recuperação de Banco de Dados no Microsoft SQL Server 2008, R2 e 2012.

Neste post final da série, vou destacar como podemos quebrar a estrutura física e lógica do Arquivo de Logs, existente no nosso Banco de Dados MyDatabaseDesastre.

Você vai aprender e entender como podemos realizar este tipo de procedimento, identificando como as informações ficam armazenadas dentro do Transact – Log, ilustrando como é possível forçar o processo de rompimento desta estrutura, bem como, quais os procedimentos devemos realizar para contornarmos este problema.

Vale ressaltar que este tipo de procedimento poderá apresentar riscos de perda de dados, sendo assim, recomenda-se a criação de uma ambiente de teste, utilizando como material de apoio os posts anteriores.

 

Para começar, vamos relembrar um pouco da estrutura do nosso banco de dados chamado MyDatabaseDesastre, sendo este, composto por uma tabela chamada Clientes, conforme apresenta a Figura 1 apresentada abaixo:Figura1

Figura 1 – Estrutura do Banco de Dados MyDatabaseDesastre.

Espero que você tenha se lembrado do nosso simples ambiente, podemos então continuar nossa simulação.

 

Preparando nosso ambiente de Recuperação

Como o objetivo deste post é demonstrar como podemos recuperar nosso banco de dados que apresenta seu arquivo de log danificado, vamos colocar em prática algumas técnicas para contingência do nosso ambiente, como elementos que permitam realizar a recuperação. Para isso, vamos fazer uso do recurso de Backup de Log, recurso que eu acredito que você já tenha ouviu falar, mas vou fazer uma breve descrição sobre o mesmo.

Você pode estar se perguntando por que vamos fazer um backup se estamos fazendo uma simulação. Pelo simples motivo que Backup é sempre uma boa prática e em qualquer cenário podemos sempre ter algum tipo de imprevisto ou surpresas.

 

Backup de Transact Log

O backup de log é composto pela parte do log de transações que estava ativa no momento da execução do comando Backup, ou seja, esta parte representa uma área utilizada pelo SQL Server para armazenar a seqüência de processamentos que ele realizou.

Para se trabalhar com este tipo de backup, nosso banco de dados deve estar configurado para o modelo de recuperação completa ou Bulk-Logged. Somente estes dois modelos de recuperação armazenam esta seqüência de processamento realizado sobre o banco de dados.

 

Aplicando o Backup de Transact Log

Como destacado anteriormente, vamos fazer uso da técnica de Backup do Transact Log para o nosso Banco de Dados MyDatabaseDesastre, para que possamos ter a capacidade de realizar este tipo de backup, devemos deixar nosso banco de dados configurado com o Modelo de Recuperação Completo ou Bulk-Logged, conforme destacado anteriormente neste post, como também nos outros posts desta série.

 

Para isso, vamos utilizar o bloco de Código 1, declarado abaixo:

— Código 1 – Realizando – Backup Completo e Backup de Log —

— Realizando Backup Database –

BACKUP DATABASE MyDatabaseDesastre

TO DISK = ‘C:\Bancos\MyDatabaseDesastre\Backup-Database-MyDatabaseDesastre.bak’

With Init,

Description = ‘Backup Database Full – MyDatabaseDesastre’,

Stats=10

Go

 

 

 

— Realizando Backup Transaction Log – –

BACKUP Log MyDatabaseDesastre

TO DISK = ‘C:\Bancos\MyDatabaseDesastre\Backup-Log-MyDatabaseDesastre.bak’

With Init,

Description = ‘Backup Log – MyDatabaseDesastre’,

Stats=10

Go

Legal, legal, nossos Backups, tanto o Completo como o de Log foram realizados, vale ressaltar que todo e qualquer Backup de Log só pode ser realizado a partir do momento em que identifica a realização anterior de um Backup Full, pois o log irá conter as diferenças entre o último backup full em relação ao momento em que o log esta sendo backupeado. A Figura 2 apresentada abaixo, ilustra o resultado do processo de backup.

Backup-2

 

Figura 2 – Informações apresentadas pelo SQL Server após a conclusão dos processos de backup.

 

Simulando – Quebra – Transact Log

Como forma de ilustrar algumas maneiras para se realizar este rompimento de um arquivo de log, desenvolvi dois cenários, sendo eles:

  • Cenário 1 – Realizando a quebra da integridade física do Transact Log – Simulando uma possível queda de Energia.

 

  • Cenário 2 – Realizando a quebra da integridade física do Transact Log – Manualmente.

 

 

Cenário 1 – Realizando a quebra da integridade física do Transact Log – Simulando uma possível queda de Energia.

 

Neste momento, já temos nosso ambiente de retaguarda preparado em caso de alguma falha no processo de simulação. Nosso próximo passo será justamente fazer o que queremos, quebra nosso arquivo de log.

Para isso, vamos utilizar o Bloco de Código 2, observe que estaremos adicionando uma nova tabela, em seguida através de uma CTE, criando uma ordenação numérica de valores e inserindo nesta tabela, conforme a seqüência apresentada a seguir:

 

— Código 2 – Realizando – Criando a Tabela Numeracao –

CREATE TABLE Numeracao

(

NumerodaLinha Int Primary Key Clustered,

ProximaLinha INT

)

WITH (DATA_COMPRESSION = PAGE);

Go

 

Observações:

  1. 1.      Note que estou fazendo uso da opção Data_Compression no nível de página, com objetivo de minimizar o impacto no uso de disco e memória por parte do SQL Server durante o processo de manipulação dos dados.

 

  1. 2.      Vamos para o próximo passo, inserir um volume de dados na Tabela Numeração, através do Bloco de Código 3, em paralelo a execução do deste bloco de código, será necessário seguir os passos do Bloco de Código 4, para que seja possível simular realmente a quebra do log.

 

— Código 3 – Inserindo  – Massa de Dados – Numeracao —

;WITH Dados AS

(

SELECT TOP (1000000)

rn = ROW_NUMBER() OVER (ORDER BY SO1.[object_id])

FROM sys.all_objects AS SO1 CROSS JOIN sys.all_objects AS SO2

ORDER BY SO2.[object_id]

)

 

INSERT dbo.Numeracao(NumerodaLinha, ProximaLinha)

SELECT rn, ROW_NUMBER() OVER (ORDER BY NEWID()) + 1000000

FROM MassadeDados;

Go

 

CheckPoint

Go

 

Observação: Um detalhe importante neste bloco de código está na declaração e uso do comando Ckeckpoint, existe no SQL Server, como mecanismo para garantir a escrita obrigatória dos dados em nossos arquivos de dados e por conseqüência, registrar esta transação em nosso Arquivo de Log.

O Bloco de Código 4 é o mais simples, porém o mais importante, que consiste justamente na quebra da escrita de dados no log, através do comando Shutdown. Ao utilizar o comando Shutdown, estaremos forçando a realização de um desligamento forçado, o que normalmente acontece quando temos alguma falha física ou quedas de energia.

 

Não se esqueça que este bloco de código deve ser executado em paralelo ao processamento do Bloco de Código 3.

 

Para isso, vamos executar os seguintes passos:

— Código 4 – Utilizando o comando Shutdown —

  1. Abra uma nova conexão dentro da sua instância SQL Server;
  2.  Execute o comando “SHUTDOWN WITH NOWAIT”.

 

Neste momento, nosso servidor SQL Server encontra-se fora do ar, nosso banco de dados, teve sua escrita e acesso interrompidos de forma brusca.

Vamos então, reinicializar o serviço do Servidor e verificar qual é status do nosso banco de dados neste momento. Vou deixar que você escolha a forma que você tem mais familiaridade para manipular os serviços do Windows.

Um detalhe importante é manter o Management Studio aberto para que possamos conseguir ver em tempo real o Status do nosso banco de dados, conforme ilustra a Figura 3, apresentada em seguida:

Backup-3

 

Figura 3 – Status do Banco de Dados MyDatabaseDesastre.

 

Perfeito, conseguimos forçar a quebra do arquivo de log, conforme apresenta a Figura 3 acima.

 

E agora o precisamos fazer para resolver isso?

Em situações normais não precisamos fazer nada, conforme destaco abaixo.

 

Importante:

  1. Você vai perceber que o serviço de Banco de Dados do SQL Server será interrompido imediatamente e isso vai forçar que todo processo de escrita de dados no log será perdida.
  2. 2.      O SQL Server para o serviço com o arquivo de dados atualizado pela transação que ficou em aberto, mantendo no arquivo de log o registro desta transação incompleta.
  3. 3.      Em uma situação normal, o SQL Server resolveria o problema ao iniciar o serviço do mecanismo de banco de dados, executando de forma automática um ROLLBACK da transação que ficou em aberto, utilizando as informações do Arquivo de Log.

 

Este tipo de cenário é muito comum de se deparar, mas em alguns casos nem tudo é perfeito, pode acontecer que o SQL Server não consiga reverter as transações, sendo assim, nosso banco será mantido com o Status de In Recovery.

Justamente nesta a situação que nosso backup é de extrema importância e poderá nos ajudar, através do processo de restauração do banco de dados, fazendo uso do Backup Full e Backup de Log. Caso realmente seja necessário fazer a restauração, execute o Bloco de Código 5.

 

— Código 5 – Restaurando o Backup Completo e Backup de Log –

— Recuperando o Banco de Dados por Completo – Backup Database —

Use master

Go

 

Restore Database MyDatabaseDesastre READ_WRITE_FILEGROUPS

From Disk = ‘C:\Bancos\MyDatabaseDesastre\Backup-Database-MyDatabaseDesastre.bak’

With File=1,

Replace,

NoRecovery

Go

 

–Restaurando do Log e liberando o Banco de Dados

Use master

Go

 

Restore Log MyDatabaseDesastre

From Disk = ‘C:\Bancos\MyDatabaseDesastre\Backup-Log-MyDatabaseDesastre.bak’

With Recovery

Go

 

— Acessando o Banco de Dados – MyDatabaseDesastre —

USE MyDatabaseDesastre

Go

 

Ao concluir a execução deste bloco de código, indica que tudo ocorreu normalmente você conseguiu realizar acesso ao banco de dados MyDatabaseDesastre, isso mostra que nosso ambiente esta corrigido e liberado para uso.

 

Mas este não é o nosso cenário, o que queremos realmente e quebrar esta estrutura, e realizarmos a recuperação de forma manual, desta forma, vamos continuar nossa caminhada, agora trabalhando com o Cenário 2.

 

Cenário 2 – Realizando a quebra da integridade física do Transact Log – Manualmente.

Este é um cenário bastante simples no que se diz respeito a quebra do Arquivo de Log. O que realmente vamos levar um pouco mais de tempo será no processo de recuperação do log de forma manual. Então, vamos seguir os seguintes passos:

  1. Para o serviço do Mecanismo de Banco de Dados do seu SQL Server;

 

  1. Localize a pasta em que seu banco de dados esta armazenado, no nosso caso será em: C:\Banco\MyDatabaseDesastre;

 

  1. Selecione o arquivo de log: MyDatabaseDesastre_Log.ldf ou MyDatabaseDesastre_1.ldf;

 

  1. Abra este arquivo diretamente no em qualquer Editor de Texto, pode ser o Notepad ou similar. No meu caso, vou utilizar o Hex Editor Neo, aplicativo que já utilizamos em outros posts desta séria.

 

  1. Realize a alteração do conteúdo deste arquivo, apagando alguns dados, ou trocando valores, salve o arquivo;

 

  1. Inicie o SQL Server;

 

  1. Tente realizar o acesso banco MyDatabaseDesastre.

Você vai observar que o serviço do SQL Server é inicializado aparentemente de forma normal, mas ao tentar acessar o Banco de Dados MyDatabaseDesastre, receberemos a seguinte mensagem de erro:

 

Msg 945, Level 14, State 2, Line 1

Database ‘MyDatabaseDesastre’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

 

Ao pesquisarmos nos Logs do Serviço (Management Studio – Object Explorer – Management – SQL Server Logs) veremos as mensagens, conforme ilustra a Figura 4:

Backup-4

 

Figura 4 – Mensagem de falha na ativação do arquivo de log, armazenada nos logs de inicialização do SQL Server.

 

Pois bem, nosso banco de dados esta com seu arquivo de log corrompido, é muito importante ressaltar que em nenhum momento quebramos a integridade do arquivo de dados, sendo assim, nossos dados estão totalmente íntegros.

 

Mas qual é Status que este Banco de Dados esta apresentando neste momento para o SQL Server?

Para responder esta e qualquer outra questão, vamos utilizar o Bloco de Código 6:

 

— Código 6 – Consultando o Status e demais informações do Banco de Dados –

 

SELECT DATABASEPROPERTYEX(‘MyDatabaseDesastre’, ‘Status’) As Status,

DATABASEPROPERTYEX(‘MyDatabaseDesastre’, ‘Recovery’) As ‘Modelo de Recuperação’,

DATABASEPROPERTYEX(‘MyDatabaseDesastre’, ‘UserAccess’) As ‘Forma de Acesso’,

DATABASEPROPERTYEX(‘MyDatabaseDesastre’, ‘Version’) As ‘Versão’

 

Após executar este bloco de código, o SQL Server retornará as informações apresentadas na Figura 5:

Backup-5

 

Figura 5 – Status do Banco de Dados – MyDatabaseDesastre.

 

Observe que nosso banco de dados esta apresentando neste momento o Status de SUSPECT(Suspeito), isso indica para o SQL Server que o mesmo, esta neste momento conectado de forma lógica ao SQL Server, mas sua estrutura não esta totalmente confiável para ser utilizada e em muitos casos não é possível realizar o acesso.

 

Agora temos que começar a pensar como poderemos resolver este problema, temos algumas possíveis alternativas, dentre elas, destaco:

  1. 1.      Realizarmos o processo de Detach e Attach;
  2. 2.      Realizarmos o processo de Detach e Attach forçando a reconstrução do Log;
  3. 3.      Realizar o processo de Detach e Attach sem utilizar o arquivo de Log;
  4. 4.      Recuperando a Estrutura Física e Lógica do Banco de Dados – MyDatabaseDesastre; e
  5. 5.      Restaurar o Backup Completo e Backup de Log. (Esta alternativa já foi apresentada anteriormente).

 

Muito bom, já conhecemos algumas possíveis formas para tentarmos recuperar nosso ambiente, vou apresentar cada uma delas, você vai poder acompanhar e identificar qual realmente será uma possível solução.

 

Alternativa 1 – Realizando o Detach e Attach:

Antes de realizar o processo de Detach, é importante informar que somente Bancos de Dados com Status de On-Line, Off-Line ou Emergency podem ser desanexados, neste caso, nosso banco de dados esta apresentando o Status de Suspect, vamos ter que realizar a alteração do seu Status em seguida fazer o Detach. Para isso, utilizaremos o Bloco de Código 7:

 

— Código 7 – Alterando o Status do Banco de Dados e realizando o Detach —

Alter Database MyDatabaseDesastre

Set Emergency

Go

 

EXEC SP_Detach_db ‘MyDatabaseDesastre’
Go

 

Nosso próximo passo é realizar o processo de Attach(Anexo) desta banco de dados novamente em nossa Instância, sendo assim, utilizaremos o Bloco de Código 8:

 

— Código 8 – Realizando o Attach —

EXEC sp_attach_db @dbname = ‘MyDatabaseDesastre’,

@filename1 = ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre.mdf’,

@filename2 = ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre_1.ldf’

 

Ao tentarmos executar este bloco de código, o SQL Server nos apresenta rapidamente a seguinte mensagem de erro:

 

Msg 5172, Level 16, State 15, Line 1

The header for file ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre_1.ldf’ is not a valid database file header. The FILE SIZE property is incorrect.

 

 

Isso mostra que nossa alternativa de número 1 esta descartada e não vai nos ajudar, pois nosso arquivo de log esta corrompido e o processo de Attach que estamos utilizando ainda faz uso deste mesmo arquivo.

 

Alternativa 2 – Realizando o Detach e Attach forçando a reconstrução do Log:

Não vou novamente descreve cada passo, vou diretamente apresentar o que podemos fazer, através do Bloco de Código 9:

 

— Código 9 – Alterando o Status do Banco de Dados e realizando o Detach —

Alter Database MyDatabaseDesastre

Set Emergency

Go

 

EXEC SP_Detach_db ‘MyDatabaseDesastre’
Go

 

— Criando o Banco de Dados e forçando a reconstrução do Log —

CREATE DATABASE MyDatabaseDesastre

ON

(NAME = MyDatabaseDesastre,

FILENAME = ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre.mdf’)

FOR ATTACH_REBUILD_LOG

Mais uma vez não obtivemos sucesso no processo de recuperação de nosso banco de dados, mesmo forçando o processo de Attach com a reconstrução do arquivo de Log, conforme apresenta a mensagem de erro abaixo:

File activation failure. The physical file name “C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre_log.LDF” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 1

Could not open new database ‘MyDatabaseDesastre’. CREATE DATABASE is aborted.

Msg 829, Level 21, State 1, Line 1

Database ID 7, Page (2:0) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.

 

Da mesma forma que a alternativa 1 não resolveu o nosso problema esta alternativa também não a solução, vamos então para a Alternativa 3.

 

Alternativa 3 – Realizando o Detach e Attach sem utilizar o arquivo de Log:

Nesta alternativa estaremos realizando novamente o processo de Attach(Anexo) do banco de dados, mas descartando totalmente o uso do arquivo de log existente, sendo assim, o SQL Server tentar criar um novo arquivo de log.

Vale ressaltar que a System Stored Procedure SP_ATTACH_SINGLE_FILE_DB, introduzida a partir do Microsoft SQL Server 2005 e posteriormente substituída no Microsoft SQL Server 2008 pelo comando Create Database For Attach_Rebuild_Log, tem a capacidade de realizar o processo de Attach Database, somente para Bancos de Dados que possuem um único arquivo de dados.

Para demonstrar a forma de uso e tentarmos mais uma vez recuperar nosso banco de dados execute o Bloco de Código 10:

 

— Código 10 – Alterando o Status do Banco de Dados e realizando o Detach —

Alter Database MyDatabaseDesastre

Set Emergency

Go

 

EXEC SP_Detach_db ‘MyDatabaseDesastre’
Go

 

— Utilizando a System Stored Procedure – SP_Attach_Single_File_DB –

 

Exec sp_attach_single_file_db ‘MyDatabaseName’,

@physname = ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre.mdf’

 

Após a execução de mais esta alternativa, para nossa surpresa não conseguimos realizar a recuperação do Banco de Dados, conforme apresenta a mensagem de erro abaixo:

 

File activation failure. The physical file name “C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre_1.ldf” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 1

Could not open new database ‘MyDatabaseName’. CREATE DATABASE is aborted.

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: unable to decrypt page due to missing DEK. It occurred during a read of page (2:0) in database ID 7 at offset 0000000000000000 in file ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre_1.ldf’.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

E agora, esgotamos mais uma possível alternativa e não conseguimos recuperar o Banco de Dados MyDatabaseDesastre.

Começamos a nos deparar com algumas questões:

  1. 1.      O que esta acontecendo?
  2. 2.      Será que fizemos algo de errado?
  3. 3.      Vamos conseguir recuperar nosso ambiente de forma manual?

 

A resposta é muito simples e direta. “SIM”, vamos conseguir recuperar e estamos fazendo algo de errado “TAMBÉM”. Nosso erro esta justamente no procedimento de alterar o Status do Banco de Dados de Suspect para Emergency e depois realizar o Detach.

Como destaca Paul S. Randal em um dos seus mais fantásticos posts: “Nunca, Nunca, Never…. never detach a suspect database.” Ou seja, um banco de dados que está apresentando o seu Status de Suspect não deve ser desanexado(Dettach), pois sua estrutura não esta totalmente confiável, o que requer que alguns procedimentos sejam feitos para colocar este banco novamente em uso.

 

Este é o nosso grande erro, realizar o Detach de uma base de dados Suspect.

 

Com isso, do conjunto de cinco possíveis alternativas apresentadas em nosso cenário para tentarmos recuperar nosso ambiente, somente as alternativas de número 4 e 5 podem resolver de fato, sendo que, a alternativa de número 5 já foi apresentada anteriormente.

Vou então apresentar a alternativa de número 4 e demonstrar como os procedimentos apresentadas nesta opção, vão resolver o nosso problema.

 

Alternativa 4 – Reconstruindo a Estrutura Física e Lógica do Banco de Dados – MyDatabaseDesastre

Para esta alternativa, vamos utilizar o Bloco de Código 11:

— Código 11 – Procedimentos para recuperar a estrutura do banco de dados —

ALTER DATABASE MyDatabaseDesastre SET EMERGENCY

ALTER DATABASE MyDatabaseDesastre SET SINGLE_USER

Go

 

DBCC CHECKDB (MyDatabaseDesastre, REPAIR_ALLOW_DATA_LOSS)

WITH NO_INFOMSGS, ALL_ERRORMSGS

Go

 

ALTER DATABASE MyDatabaseDesastre SET read_write

ALTER DATABASE MyDatabaseDesastre SET multi_user

Go

Bom agora é hora de ver o que vai acontecer, vamos mandar executar, rezar e aguardar, tomará que tudo de certo.

 

Ufa, show de bola, até que enfim, conseguimos, conseguimos, nosso banco de dados, esta novamente operacional, ou seja, esta Online e acessível, conforme apresenta a Figura 6:

Backup-6

 

Figura 6 – Estrutura de Banco de Dados MyDatabaseDesastre recuperada.

 

Muito bem, ótimo, foi uma longa caminhada, mas com certeza de muito aprendizado e conhecimento. Agora você pode fazer acesso a este banco e continuar a brincar com esta estrutura.

 

Tenha sempre em mente que o bom e velho backup é a melhor solução, pois mesmo tendo recursos que o SQL Server oferece ele poderá ser o seu grande amigo, nestas horas de desespero.

 

Vou encerrar este post aqui, deve totalmente cumprida e feliz por ter conseguido. Posso dizer que este foi o maior post que já escrevi, como também, é a série que mais me trouxe prazer em compartilhar.

 

Espero que você tenha gostado deste post, como também, de toda a série dedicada exclusivamente ao Processo de Simulação e Recuperação de um Banco de Dados, seus componentes físicos e lógicos. Você acompanhou durante a série diversas maneiras, técnicas e procedimentos que estão presentes no Microsoft SQL Server.

 

Mais uma vez obrigado por sua atenção e visita, já estou pensando nos próximos temas que podemos trabalhar.

Até mais.

 

Dica – Espelhando Backup de Banco de Dados e Backup de Log de Banco de Dados no Microsoft SQL Server 2008 e R2.


Salve comunidade, bom dia para todos.

Hoje vou postar uma simples dica, mas que realmente é algo que poderá ajudar em muito nos procedimentos de backup e até mesmo distribuição de dados em uma rede.

Você sabia que é possível realizar um backup de banco de dados e durante este processo de backup espelhar o arquivo para outro disco, fita ou até mesmo servidor?

Se a resposta for SIM, parabéns, você conhece a opção Mirror disponível para o comando Transact – SQL Backup Database e Backup Log.

Agora se a resposta for NÃO, sem problemas, esta dica vai lhe ajudar a conhecer e entender esta funcionalidade.

Entendendo o espelhamento de Backup de Banco de Dados ou Log

A capacidade do Microsoft SQL Server em possibilitar ao administrador de backup de dados manter o seu ambiente a cada dia mais seguro vem evoluindo de versão em versão do produto.

Principalmente a partir da versão 2005 a Microsoft foi introduzindo ferramentas, recursos e funcionalidades que permitem garantir ainda mais este cenário. Respeitando os conceitos de Segurança da Informação, com base, nos seus quatro pilares:

  • Confidencialidade;
  • Integridade
  • Disponibilidade; e
  • Autenticidade.

Com base no conceito de Disponibilidade de dados, os comandos Backup Database e Backup Log receberam alguns melhoramentos neste quesito, com a possibilidade de gerar cópias distribuídas localmente ou remotamente de um arquivo de Backup gerado de forma automática pelo Microsoft SQL Server, fazendo uso da opção Mirror em conjunto com estas instruções.

Vantagens e Desvantagens em se Espelhar Backup de Banco de Dados ou Log

Como todo e qualquer novo recurso sempre nos deparou com considerações que podem nos fazer pensar, analisar e até mesmo validar o seu uso, o que posteriormente poderá ser classificado como uma vantagem ou desvantagem o seu uso. A seguir eu destaco com base, na minha análise o que pode ou não ser uma possível vantagem ou desvantagem.

Vantagens:

  • Aumento na disponibilidade e distribuição dos dados;
  • Aumento na segurança dos dados;
  • Diminuição na possibilidade de perda de dados;
  • Facilidade no uso;
  • Possibilidade de realizar espelhamentos locais ou remotos;
  • Maior aumento do Nível de Segurança dos Dados;
  • Não requer um conhecimento avançado ou específico para este de funcionalidade; e
  • Não requer a utilização de ferramentas de terceiros ou produtos específicos.

Desvantagens:

  • Aumento no espaço ocupado em disco, devido à duplicidade de dados;
  • Aumento na necessidade de gerenciamento e controle dos dados;
  • Aumento no tempo de execução e encerramento do procedimento de Backup;
  • Força o uso da opção Format em conjunto com a opção Mirror para realização do Backup;
  • Funcionalidade presente somente nas edições Enterprise do Microsoft SQL Server 2005, 2008 ou R2; e
  • Possibilidade de Espelhamento de Backup em fita removida em versões futuras.

Após elencar um pouco das possíveis vantagens e desvantagens, vou agora apresentar algumas formas de uso da opção Mirror em conjunto com o comando Backup Database.

Utilizando a opção Mirror em Backup Database:

1. Espelhamento Backup de Banco Dados em unidades de disco diferentes:

BACKUP DATABASE MEUBANCO

TO DISK = ‘C:\BANCOS\MEUBANCO-BACKUP.BAK’

MIRROR TO DISK = ‘D:\Banco\MeuBanco-Backup-Mirror.bak’

With Init,

Format,

Stats=10,

Description = ‘Backup de Banco de Dados, com espelhamento para o arquivo MeuBanco-Backup-Mirror’

Go

2. Espelhamento de Backup de Dados em pastas diferentes, na mesma unidade de disco:

BACKUP DATABASE MEUBANCO

TO DISK = ‘C:\BANCOS\MEUBANCO-BACKUP.BAK’

MIRROR TO DISK = ‘C:\Backup-Banco\MeuBanco-Backup-Mirror.bak’

With Init,

Format,

Stats=10,

Description = ‘Backup de Banco de Dados, com espelhamento para o arquivo MeuBanco-Backup-Mirror’

Go

3. Espelhamento de Backup de Dados em unidades locais e remotas:

BACKUP DATABASE MEUBANCO

TO DISK = ‘C:\BANCOS\MEUBANCO-BACKUP.BAK’

MIRROR TO DISK = ‘\\Servidor\Backup\SQLServer\MeuBanco-backup-Mirror.bak’

With Init,

Format,

Stats=10,

Description = ‘Backup de Banco de Dados, com espelhamento para o arquivo MeuBanco-Backup-Mirror’

Go

Utilizando a opção Mirror em Backup Log:

1. Espelhamento Backup de Banco Dados em unidades de disco diferentes:

BACKUP Log MEUBANCO

TO DISK = ‘C:\BANCOS\MEUBANCO-BACKUP-Log.BAK’

MIRROR TO DISK = ‘D:\Banco\MeuBanco-Backup-Mirror-Log.bak’

With Init,

Format,

Stats=10,

Description = ‘Backup de Banco de Dados, com espelhamento para o arquivo MeuBanco-Backup-Mirror-Log’

Go

2. Espelhamento de Backup de Dados em pastas diferentes, na mesma unidade de disco:

BACKUP Log MEUBANCO

TO DISK = ‘C:\BANCOS\MEUBANCO-BACKUP-Log.BAK’

MIRROR TO DISK = ‘C:\Backup-Banco\MeuBanco-Backup-Mirror-Log.bak’

With Init,

Format,

Stats=10,

Description = ‘Backup de Banco de Dados, com espelhamento para o arquivo MeuBanco-Backup-Mirror-Log’

Go

3. Espelhamento de Backup de Dados em unidades locais e remotas:

BACKUP Log MEUBANCO

TO DISK = ‘C:\BANCOS\MEUBANCO-BACKUP-Log.BAK’

MIRROR TO DISK = ‘\\Servidor\Backup\SQLServer\MeuBanco-backup-Mirror-Log.bak’

With Init,

Format,

Stats=10,

Description = ‘Backup de Banco de Dados, com espelhamento para o arquivo MeuBanco-Backup-Mirror-Log’

Go

Observação: Podemos notar em todos os exemplos, que tivemos a necessidade de declarar a opção Format. Esta necessidade se faz necessária e obrigatória por estarmos trabalhando com no mínimo dois arquivos de backup, algo que para o SQL Server representa um conjunto de mídias de backup, forçando que seja escrito no cabeçalho do arquivo de backup o conjunto de arquivos físicos que compõem e formam estas mídias de backup.

Bom pessoal vou encerrar mais esta dica por aqui, acredito mais uma vez ter consegui apresentar algo que possa ser simples, mas muito útil, importante, fácil e acima de tudo que demonstre ainda mais como o Microsoft SQL Server esta se tornando um produto formidável.

Um grande abraço agradeço mais uma vez a sua visita.

Nos encontramos brevemente.

Até mais.

Tempo restante para finalizar execução do Backup no Microsoft SQL Server 2008 e R2.


Pessoal, bom dia.

Quais as novidades?

Estou de volta com mais uma dica para vocês. Hoje vou falar um pouco sobre o tempo de execução de um processo de Backup.

Introdução

Uma das principais atividades de qualquer profissional da área de TI é manter seu ambiente em funcionamento, pois bem, para que isso possa acontecer nada como realizar o bom e velho processo de backup.

Acredito que você leitor já deve ter passado pela necessidade de realizar um backup de seu ambiente, máquina, banco de dados, arquivos, etc.

Existem diversos mitos sobre as técnicas de backup, mas na verdade o problema normalmente não esta relacionado ao processo de backup, muito pelo contrário, o problema na verdade é o processo de Restore(Restauração ou Recuperação dos Dados). Sendo o processo de restauração, atividade crucial para funcionamento e disponibilidade de todo e qualquer tipo de ambiente.

Para Microsoft esta preocupação é a mesma de todos, manter sempre o ambiente seguro, o administrador tranqüilo e o usuário feliz. Pensando nisso, a Microsoft introduziu a partir do SQL Server 2008 alguns atributos que possibilitam consultar e obter informações sobre o tempo de execução de um backup, como também, seu indicador de execução e até mesmo o tempo restante para sua conclusão.

Como obter informações sobre o Backup

O processo de backup normalmente apresenta um conjunto específico de informações que nos ajudam a identificar o quanto esta atividade demanda de processamento de hardware, como também, a sua importância.

Para que possamos obter estas informações durante o seu tempo de execução, foi introduzido a partir do Microsoft SQL Server 2008, a DMV(Dynamic Management View / Visão de Gerenciamento Dinâmico) chamada: sys.dm_exec_requests.

É com base nesta nova DMV, que temos a possibilidade de encontrar as informações todas as transações em execução em nosso Servidor ou Instância SQL Server. Esta DMV possui um conjunto específico de dados utilizados em tempo real e de forma dinâmica, o que possibilitam a sua obtenção de informação no tempo de execução da transação.

A seguir apresento algumas das colunas existentes na sys.dm_exec_requests, utilizadas para retorno de dados sobre o Backup:

  • Command: Apresenta o time do comando transacional em execução;
  • Estimated_Completion_Time: Apresenta uma estimativa de tempo para conclusão da transação;
  • Start_Time: Apresenta a data e hora de inicialização da transação; e
  • Percent_Complete: Apresenta a porcentagem de execução concluída para esta transação.

Através da combinação de uso destas quatro colunas, foi possível desenvolver o código de permite identificar e apresentar as informações sobre o tempo de execução e conclusão do Backup.

A seguir apresento o Código 1, utilizado como método de transação para obtenção de dados sobre a execução de um backup:

— Exemplo: Código 1 – Obtendo informações sobre o Backup —

SELECT command,

               ‘EstimatedEndTime’ = Dateadd(ms,estimated_completion_time,Getdate()),

               ‘EstimatedSecondsToEnd’ = estimated_completion_time / 1000,

               ‘EstimatedMinutesToEnd’ = estimated_completion_time / 1000 / 60,

               ‘BackupStartTime’ = start_time,

               ‘TimeExecution’=  DateDiff(minute,Getdate(),Start_time),

               ‘PercentComplete’ = percent_complete

FROM sys.dm_exec_requests

 WHERE session_id = @@SPID

De posse deste código, agora fica mais fácil conseguir encontrar as informações sobre o tempo de execução, estimativas de encerramento e porcentagem já concluída do nosso backup.

 Espero que todos tenham gostado nos encontramos em breve.

 Até mais.