Backup de database

O gerenciamento de backup é uma das tarefas mais importantes de um DBA, pois a disponibilidade do ambiente dependerá da existência ou não desse valioso recurso. Existem 5 tipos de backups: full, incremental, diferential, copy e diary.

Backup Full: O backup full é um backup completo de dados. Durante o backup, os arquivos tem seu atributo de archive alterado, informando que os mesmos sofreram backup.

Backup Incremental: O backup incremental copia todo o conteúdo alterado ou criado desde o último backup full, alterando o atributo de archive dos dados.

Backup Diferential: O backup diferential copia todo o conteúdo alterado ou criado desde o último backup full, porém não altera o atributo de archive dos dados.

Backup do tipo Copy: O backup copy é um backup full, porém ele não altera o atributo de archive, permitindo que não haja interrupção na sequencia de backups.

Backup do tipo Diary: Backup do tipo diary copia todos os arquivos que foram modificados no dia da execução, e não altera o atributo de archive dos dados.

A diferença entre backup diferential e incremental é no momento de realizar o restore dos dados. Para combinação de backup full e diferential, precisaremos restaurar primeiro o backup full e depois o último backup diferential, já para uma operação de restore com um backup full e incremental, precisaremos restaurar primeiro o backup full e depois todos os backups incrementais, desde o último backup  full. Isso nos mostra que em um ambiente com backup full e diferential, teremos um processo de backup mais demorado, porém o processo de restore será mais rápido, ao contrário do backup full e incremental, onde o processo de backup dos dados é mais rápido (pois apenas os arquivos que foram alterados sofrem backup), porém o processo de restore é mais demorado, pois será necessário várias fitas para restauração completa dos dados, dependendo do volume a ser restaurado. O esquema de backup a ser utilizado vai depender da necessidade de cada empresa.

Para o SQL Server, não há backups incrementais ou diary, apenas full, copy, diferential e transaction log. As regras são as mesmas para backup full e diferential, porém para backups de transaction logs é necessário que o database esteja com o recovery model configurado para Full (falarei de recovery model em outro artigo).

ATENÇÃO: backups criados em uma versão do SQL Server não podem ser restaurados em versões anteriores.

ATENÇÃO 2: a compressão de dados em backups é suportada apenas no SQL Server 2008 e 2012.

Listando queries com alto consumo de CPU

Um problema recorrente na vida de qualquer DBA é quando o SGBD começa a “devorar” os recursos do servidor (memória, disco ou CPU). Irei mostrar nesse post como detectar e tratar situações que o processo do SQL Server faz alta utilização de CPU.

Abra a console do SQL Server Management studio, abra o query editor e execute a seguinte instrução:

SELECT TOP 20
GETDATE() AS “Collection Date”,
qs.execution_count AS “Execution Count”,
SUBSTRING(qt.text,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset
)/2
) AS “Query Text”,
DB_NAME(qt.dbid) AS “DB Name”,
qs.total_worker_time AS “Total CPU Time”,
qs.total_worker_time/qs.execution_count AS “Avg CPU Time (ms)”,
qs.total_physical_reads AS “Total Physical Reads”,
qs.total_physical_reads/qs.execution_count AS “Avg Physical Reads”,
qs.total_logical_reads AS “Total Logical Reads”,
qs.total_logical_reads/qs.execution_count AS “Avg Logical Reads”,
qs.total_logical_writes AS “Total Logical Writes”,
qs.total_logical_writes/qs.execution_count AS “Avg Logical Writes”,
qs.total_elapsed_time AS “Total Duration”,
qs.total_elapsed_time/qs.execution_count AS “Avg Duration (ms)”,
qp.query_plan AS “Plan”
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
qs.execution_count > 50 OR
qs.total_worker_time/qs.execution_count > 100 OR
qs.total_physical_reads/qs.execution_count > 1000 OR
qs.total_logical_reads/qs.execution_count > 1000 OR
qs.total_logical_writes/qs.execution_count > 1000 OR
qs.total_elapsed_time/qs.execution_count > 1000
ORDER BY
qs.execution_count DESC,
qs.total_elapsed_time/qs.execution_count DESC,
qs.total_worker_time/qs.execution_count DESC,
qs.total_physical_reads/qs.execution_count DESC,
qs.total_logical_reads/qs.execution_count DESC,
qs.total_logical_writes/qs.execution_count DESC

O resultado dessa querie irá listar as 20 instruções sendo executadas no servidor, consumindo mais recursos de CPU, como mostra a imagem abaixo:

Outro detalhe a ser percebido nesse resultado é o valor da linha 6, coluna “Total CPU Time“, expresso em microssegundos, e quando convertemos para minutos, identificamos que a querie em questão tomou exatos 17.8 minutos para executar !

É claro que apenas a identificação dessas queries não resolvem o problema, mas pelo menos já sabemos onde devemos atuar para otimizar o desempenho do SQL Server.

Vídeos sobre SQL Server 2008 R2

Para quem deseja conhecer o SQL Server 2008 R2, o Technet Brasil oferece uma série de vídeos, em português, sobre as várias funcionalidades do SQL. Os vídeos estão divididos por categorias (novas funcionalidades, implementação e avançado), dando uma visão clara e prática da ferramenta. Os vídeos são apresentados pelos MVP’s Gustavo Aguiar e Fabiano Amorim, entre outros.

O conteúdo pode ser acessado através do endereço do Centro de treinamento SQL Server 2008 R2.

Como Adicionar Ou Remover Instâncias do SQL Server 2008 / 2008R2

No Windows 7, ir a Programs and Features e escolher Microsoft SQL Server 2008 R2 (64-bit) (ou qualquer que seja a versão de SQL Server de que se pretende remover a instância).

Escolher Remove (ou Add, se se pretende adicionar uma instância).

Seguir os passos e escolher a instância que se pretende remover.

Selecionar as funcionalidades que se pretende remover (selecionar todas as funcionalidades para remover a instância). 

Nota: Não remover as funcionalidades partilhadas se não se pretendem remover outras instâncias.

Dica – Copiar grid + cabeçalhos - SQLServer

Você está utilizando o Management Studio e acaba de executar uma consulta; agora precisa copiar o resultado para uma planilha Excel.

Sua consulta gerou um resultado com 25 colunas e ao colar o conteúdo na planilha você nota que só vieram os dados da consulta; os nomes das 25 colunas você terá que preencher a mão.

Alterar isso é bem simples. Vá no menu Options, expanda a opção Query Results e escolha a subopção Results to Grid. Agora é só marcar a opção “Include column headers when copying or saving the results” (Inclui cabeçalho das colunas quando copiar ou salvar resultados).

Passos para realizar o IMPORT no oracle - Windows

1.  Ir ao diretório que se encontra o dump, através do prompt, setar o SET ORACLE_SID=NOMEDOBANCO;

2.  Apartir do diretório faça o import com os parâmetros:  
    imp USUARIO file=arquivo.dmp fromuser=USUARIO_ORIGEM touser=USUARIO_DESTINO

Backup de uma única tabela no SQL Server usando SSMS

1. Botão direito do mouse no banco de dados (note não da tabela)

• Tasks
• Generate scripts

3. No painel “Choose Objects”, selecione a tabela que você quer fazer o backup.

• Next

4. No conjunto de painel “Opções de Scripting – SET SCRIPT OPTIONS ”

• Escolha o caminho onde será gravado o arquivo
• Clique em Avançado.

5. Na tela de ADVANCED STRIPTING OPTIONS

• Aba General(ultima opção da aba)
• Escolha Schema and data (escolha de esquema e de dados)

(Se você também quiser índices ou outras opções,  certifique-se que também serão escolhidos)

6. Next / FINISH

Criando um BD - SQLServer

create database banconovo;

Renomeando:

exec sp_rename @objname = ‘Categoria’,  @newname = ‘categoria’

Aqui são criados 2 arquivos:

banconovo.MDF – armazena os dados

banconovo.LDF – armazena os logs de transações para executar comandos SQL, você precisa estar trabalhando no banco específico onde estarão as tabelas, para isso certifique que está no BD certo:

use banconovo;

Para saber a versão do seu SQL Server:

select @@version;

ou

SELECT

SERVERPROPERTY(‘productversion’),

SERVERPROPERTY (‘productlevel’),

SERVERPROPERTY (‘edition’)

Script de backup - Postgresql

#!/bin/sh

DATA=`/bin/date +%Y%m%d_%H%M%S`

VARIAVEIS DO POSTGRESQL - (IP:10.0.0.0)

# -U: usuario
# -i: ignora versao
# -h: host

#HOST="10.0.0.0"
#USER="postgres"
#DB="NOME_DB"

COMANDO

BKP="/backup/BKP-$DATA.sql"
pg_dump -h $HOST $DB -U $USER -i | gzip > $BKP.gz

Comando RTRIM - SQLSERVER 2008

RTRIM (string) = remove os espaços em branco à direita de uma string.

update TABELA set CAMPO_A = RTRIM(CAMPO_A);

DICAS - SQLSERVER 2008R2

Como fazer para visualizar os usuários conectados no banco SQLServer 2008?

• Exec sp_who

Como derrubar a conexão de um determinado usuário via comando no SQL-SERVER 2008?

• Procure o spid do usuário através da instrução:
• SELECT session_id FROM sys.dm_exec_sessions where login_name = ‘usuario_desejado'

Matando o spid do usuário desejado

• kill do spid

SQL Server Management Studio

SQL Server 2008 Management Studio

SQL Server Management Studio é um ambiente integrado para acessar, configurar, gerenciar, administrar e desenvolver todos os componentes do SQL Server. O SQL Server Management Studio combina um amplo grupo de ferramentas gráficas com editores de scripts ricos para fornecer acesso ao SQL Server para desenvolvedores e administradores de todos os níveis.

Para fazer o download ou conhecer mais sobre essa plataforma acesse: SQL Server 2008 Management Studio

Renomeando bases do SQL 2000/2005/2008/2012

Acredite, em algum momento da sua vida você vai precisar renomear uma base. Veja como fazer isso no SQL 2000, 2005, 2008 e 2012.

SQL 2000:

EXEC sp_renamedb 'antigoNome', 'novoNome'

SQL 2005/2008/2012:

ALTER DATABASE antigoNome MODIFY NAME = novoNome

Também pode ser feito através de detach/attach:

EXEC sp_detach_db 'antigoNome', 'true'
EXEC sp_attach_db @dbname = N'novoNome', @filename1 = N'c:\caminho\arquivo_dados.mdf', @filename2 = N'c:\caminho\arquivo_tlogs.ldf'

Se preferir, exceto no SQL 2000, tem como fazer através da interface gráfica do SSMS (Management Studio). Clique com o direito sobre a base que deseja renomear e clique em "rename".

Consumo de CPU por base

Este script é uma baita de uma mão na roda:

WITH DB_CPU_Stats

AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

Resultado:

Verifica quem está logado e quantas sessões possui

Este pequeno select mostra quais os usuários ou logins conectados à instância e quantas sessões abertas possuem:

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);

Resultado:

Quando esta instância foi instalada?

Esta instalação do SQL é nova ou é velha? De quando é?

SELECT createdate AS [SQL Server Install Date]

FROM sys.syslogins
WHERE [sid] = 0x010100000000000512000000;

Resultado:

Com este pequeno select da tabela sys.syslogins você consegue saber quando o SQL foi instalado no servidor, informação útil muitas vezes.

Informações gerais da instância

Para realizar uma análise precisamos levantar uma série de informações que nos guiarão durante todo o processo até a conclusão. Algumas informações básicas como nome do servidor, da instância, se está em cluster, etc. são fundamentais para o início da análise e podem ser obtidas com o simples select abaixo:

SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName],
SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel],
SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly];

Resultado:

Agora já temos informações básicas para começar a análise ou para fazer uma documentação.

Obtendo informações importantes de todas as bases

Hoje vamos obter informações importantes como Recovery Model, Log Reuse, Log Size, Log Used, Compatibility Level, Page Verify, Auto Shrink entre algumas outras.

Vamos utilizar o script abaixo para obter estas informações que podem ser utilizadas para documentação, checagem, análise, ou seja, são realmente úteis.

SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],db.log_reuse_wait_desc AS [Log Reuse Wait Description],ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],db.[compatibility_level] AS [DB Compatibility Level],db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,db.is_auto_update_stats_async_on, db.is_parameterization_forced,db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,db.is_auto_close_on, db.is_auto_shrink_onFROM sys.databases AS dbINNER JOIN sys.dm_os_performance_counters AS luON db.name = lu.instance_nameINNER JOIN sys.dm_os_performance_counters AS lsON db.name = ls.instance_nameWHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'AND ls.counter_name LIKE N'Log File(s) Size (KB)%'AND ls.cntr_value > 0 OPTION (RECOMPILE);

Vamos ver o resultado disso:

Na primeira parte do resultado, podemos observar informações relacionadas a utilização do Transaction Log (que depende do Recovery Model) de todas as bases.

Arraste a barra inferior para a direita para vermos o restante das informações:

Agora sabemos se as estatísticas estão sendo criadas e atualizadas automaticamente, se é síncrona ou assíncrona, se há parametrização, snapshot isolation, se o auto close está habilitado e se o auto shrink está sendo realizado.

Estas informações são bem úteis na hora de conferir suas configurações ou conhecer um novo ambiente, além de ficar bem prático para documentar. 

Atalho para sp_who

Muitas vezes precisamos executar o sp_who para vermos quem está conectado não é verdade? Ao invés de abrir uma nova query e digitar sp_who, experimente apenas pressionar Ctrl+1 e veja o resultado:

Fácil né? Testei no SQL 2008 R2 e funciona perfeitamente.

Dicas rápidas MySQL

Mostrando tabelas criadas na db

SHOW TABLES;

Mostrando db criadas

SHOW DATABASES;

Mostrando detalhes da tabela

DESCRIBE tabela;

Mostrando somente as colunas

SHOW COLUMNS FROM tabela;

Ordenando a consulta

De forma crescente

SELECT * FROM showsvistos ORDER BY campo;

De forma decrescente (de z para a, de 9 para 0)

SELECT * FROM showsvistos ORDER BY campo DESC;

Otimizar tabela

OPTIMIZE table tabela;

Reparar tabela

REPAIR table tabela;

Dar permissão a USUÁRIO

GRANT ALL PRIVILEGES ON nomedabase.* to usuario@ip_de_onde_vai_acessar IDENTIFIED by ‘senha_do_caboco’;

Você  pode ser mais cuidadoso e falar o que e onde o cara vai poder mexer:

GRANT insert,delete ON nomedabase.tabela to usuario@ip_de_onde_vai_acessar IDENTIFIED by ‘senha_do_caboco’;