Salve comunidade, bom dia.
Tudo bem? Como vocês estão?
Estou de volta no final deste mês de Julho, com mais uma dica que pode ajudar em muito a vida do Administrador de Banco de Dados, estou falando de como obter o Histórico de Backup de Banco de Dados criado pelo Microsoft SQL Server.
Um das atividades mais importantes para o Administrador de Banco de Dados ou Profisisonal que trabalha com Banco de Dados esta relacionada com a forma de garantir que os dados estão sendo armazenados de forma correta, mas também, como estes dados pode ser guardados ao longo do tempo e caso seja necessário como podemos recuperar estes dados.
A dica de hoje tem como objetivo demonstrar como podemos através da Linguagem Transact – SQL obter informações sobre o Histórico de Realização de Backups de Bancos de Dados ao longo do tempo no SQL Server.
Como toda e qualquer empresa o volume de dados cresce diariamente de forma assustada, sua importância é vital para garantir a continuidade dos negócios, e em caso de alguma falha e necessidade o processo de recuperação destes dados é ainda mais importante.
Mas como podemos saber se o nosso processo de backup esta sendo realizado?
Para responder esta pergunta o Microsoft SQL Server oferece além da ferramenta gráfica para exibição do Histórico, algumas Tabelas de Sistema que nos ajudam a implementar via Linguagem Transact – SQL, scripts que podem retornar este conjunto de informações. Neste caso, estou me referindo as Tabelas de Sistema BackupMediaFamily e BackupSet localizadas no Banco de Dados de Sistema MSDB. A seguir apresento um breve descrição de cada tabela:
- backupmediafamily: Contém uma linha para cada família de backup.
- backupset: Contém uma linha para cada conjunto de backup.
Com o uso destas duas tabelas de sistema, temos a possibilidade de obter este histórico de informações, utilizando o seu conjunto de colunas que apresentam um leque muito grande de informações detalhadas, deste o Nome do Banco de Dados até o Nível de Compatibilidade, Modelo de Recuperação, entre outras informações.
A seguir apresento o conjunto de colunas existentes na Tabela de Sistema BackupSet:
| Nome da coluna |
Tipo de dados |
Descrição |
| backup_set_id |
int |
Número de identificação exclusivo de conjunto de backup que o identifica. Identidade, chave primária. |
| backup_set_uuid |
uniqueidentifier |
Número de identificação exclusivo de conjunto de backup que o identifica. |
| media_set_id |
int |
Número de identificação exclusivo de conjunto de mídia que identifica o conjunto de mídia contendo o conjunto de backup. Referencia backupmediaset(media_set_id). |
| first_family_number |
tinyint |
Número de família da mídia em que conjunto de backup é iniciado. Pode ser NULL. |
| first_media_number |
smallint |
Número de mídia da mídia em que conjunto de backup é iniciado. Pode ser NULL. |
| last_family_number |
tinyint |
Número de família da mídia em que conjunto de backup é encerrado. Pode ser NULL. |
| last_media_number |
smallint |
Número de mídia da mídia em que conjunto de backup é encerrado. Pode ser NULL. |
| catalog_family_ number |
tinyint |
Número de família da mídia que contém o início do diretório de conjunto de backup. Pode ser NULL. |
| catalog_media_number |
smallint |
Número de mídia da mídia que contém o início do diretório de conjunto de backup. Pode ser NULL. |
| position |
int |
Posição de backup usada na operação de restauração para localizar o conjunto de backup e arquivos apropriados. Pode ser NULL. Para obter mais informações, consulte FILE em BACKUP (Transact-SQL). |
| expiration_date |
datetime |
Data e hora de vencimento do conjunto de backup. Pode ser NULL. |
| software_vendor_id |
int |
Número de identificação do fornecedor de software que escreve o cabeçalho de mídia de backup. Pode ser NULL. |
| name |
nvarchar(128) |
Nome do conjunto de backup. Pode ser NULL. |
| description |
nvarchar(255) |
Descrição do conjunto de backup. Pode ser NULL. |
| user_name |
nvarchar(128) |
Nome do usuário que realizou a operação de backup. Pode ser NULL. |
| software_major_version |
tinyint |
Número de versão principal do Microsoft SQL Server. Pode ser NULL. |
| software_minor_ version |
tinyint |
Número de versão secundário do SQL Server. Pode ser NULL. |
| software_build_version |
smallint |
Número de compilação do SQL Server. Pode ser NULL. |
| time_zone |
smallint |
Diferença entre a hora local (onde a operação de backup está acontecendo) e o UTC (Tempo Universal Coordenado) em intervalos de 15 minutos. Os valores podem ser de -48 a +48, inclusive. Um valor de 127 indica que é desconhecido. Por exemplo, -20 é Hora Padrão do Leste dos EUA ou cinco horas após o UTC. Pode ser NULL. |
| mtf_minor_version |
tinyint |
Microsoft Número de versão secundário de formato de fita. Pode ser NULL. |
| first_lsn |
numeric(25,0) |
Número de seqüência de log do primeiro ou mais antigo registro de log no conjunto de backup. Pode ser NULL. |
| last_lsn |
numeric(25,0) |
Número de seqüência do primeiro registro de log feito após o conjunto de backup. Pode ser NULL. |
| checkpoint_lsn |
numeric(25,0) |
Número de seqüência de log do registro de log em que a operação de refazer deve ser iniciada. Pode ser NULL. |
| database_backup_lsn |
numeric(25,0) |
Número de seqüência de log do backup de banco de dados completo mais recente. Pode ser NULL.
database_backup_lsn é o “início do ponto de verificação” disparado quando o backup é iniciado. Esse LSN coincidirá com first_lsn se o backup for usado quando o banco de dados estiver ocioso e nenhuma replicação for configurada. |
| database_creation_date |
datetime |
Data e hora em que o banco de dados foi originalmente criado. Pode ser NULL. |
| backup_start_date |
datetime |
Data e hora em que a operação de backup foi iniciada. Pode ser NULL. |
| backup_finish_date |
datetime |
Data e hora em que a operação de backup foi concluída. Pode ser NULL. |
| type |
char(1) |
Tipo de backup. Pode ser:
D = Banco de dados
I = Banco de dados diferencial
L = Log
G = Arquivo ou grupo de arquivos
G = Arquivo diferencial
P = Parcial
Q = Parcial diferencial
Pode ser NULL. |
| sort_order |
smallint |
Ordem de classificação do servidor que está executando a operação de backup. Pode ser NULL. Para obter mais informações sobre ordens de classificação e agrupamentos, consulte Trabalhando com agrupamentos. |
| code_page |
smallint |
Página de código do servidor que está executando a operação de backup. Pode ser NULL. Para obter mais informações sobre códigos de página, consulte Trabalhando com agrupamentos. |
| compatibility_level |
tinyint |
Configuração de nível de compatibilidade para o banco de dados. Pode ser:
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008
Pode ser NULL.
Para obter mais informações sobre níveis de compatibilidade, consulte sp_dbcmptlevel (Transact-SQL). |
| database_version |
int |
Número de versão de banco de dados. Pode ser NULL. |
| backup_size |
numeric(20,0) |
Tamanho do conjunto de backup, em bytes. Pode ser NULL. |
| database_name |
nvarchar(128) |
Nome do banco de dados envolvido na operação de backup. Pode ser NULL. |
| server_name |
nvarchar(128) |
Nome do servidor que está executando a operação de backup do SQL Server. Pode ser NULL. |
| machine_name |
nvarchar(128) |
Nome do computador que está executando o SQL Server. Pode ser NULL. |
| flags |
int |
No SQL Server, a coluna de sinalizadores foi preterida e substituída pelas seguintes colunas de bit:
- has_bulk_logged_data
- is_snapshot
- is_readonly
- is_single_user
- has_backup_checksums
- is_damaged
- begins_log_chain
- has_incomplete_metadata
- is_force_offline
- is_copy_only
Pode ser NULL.
Em conjuntos de backup de versões anteriores do SQL Server, os bits de sinalizador:
1 = Backup contém dados registrados minimamente.
2 = WITH SNAPSHOT foi usado.
4 = Banco de dados era somente leitura na hora do backup.
8 = Banco de dados estava no modo de usuário único na hora do backup. |
| unicode_locale |
int |
Localidade Unicode. Pode ser NULL. |
| unicode_compare_style |
int |
Estilo de comparação Unicode. Pode ser NULL. |
| collation_name |
nvarchar(128) |
Nome do agrupamento. Pode ser NULL. |
| Is_password_protected |
bit |
É o conjunto de backup
protegido por senha:
0 = Não protegido
1 = Protegido |
| recovery_model |
nvarchar(60) |
Modelo de recuperação do banco de dados:
FULL
BULK-LOGGED
SIMPLE |
| has_bulk_logged_data |
bit |
1 = Backup contém dados bulk-logged. |
| is_snapshot |
bit |
1 = Backup usado com a opção SNAPSHOT. |
| is_readonly |
bit |
1 = Banco de dados era somente leitura na hora do backup. |
| is_single_user |
bit |
1 = Banco de dados era de usuário único na hora do backup. |
| has_backup_checksums |
bit |
1 = Backup contém somas de verificação de backup. |
| is_damaged |
bit |
1 = Dano no banco de dados foi detectado quando esse backup foi criado. A operação de backup foi solicitada a continuar apesar dos erros. |
| begins_log_chain |
bit |
1 = Este é o primeiro em uma cadeia contínua de backups de log. Uma cadeia de logs é iniciada com o primeiro backup de log usado depois que o banco de dados é criado ou quando é alternado do modelo de recuperação simples para o completo ou bulk-logged. |
| has_incomplete_metadata |
bit |
1 = Um backup da parte final do log com metadados incompletos. Para obter mais informações, consulte Backups da parte final do log. |
| is_force_offline |
bit |
1 = Banco de dados usado offline que usou a opção NORECOVERY quando o backup foi feito. |
| is_copy_only |
bit |
1 = Um backup somente cópia. Para obter mais informações, consulte Backups somente cópia. |
| first_recovery_fork_guid |
uniqueidentifier |
ID da bifurcação de recuperação inicial. Corresponde a FirstRecoveryForkID de RESTORE HEADERONLY.
Para backups de dados, first_recovery_fork_guid é igual a last_recovery_fork_guid. |
| last_recovery_fork_guid |
uniqueidentifier |
ID da bifurcação de recuperação final. Corresponde a RecoveryForkID de RESTORE HEADERONLY.
Para backups de dados, first_recovery_fork_guid é igual a last_recovery_fork_guid. |
| fork_point_lsn |
numeric(25,0) |
Se first_recovery_fork_guid não for igual a recovery_fork_guid, esse será o número de seqüência de log do ponto de bifurcação. Caso contrário, o valor será NULL. |
| database_guid |
uniqueidentifier |
ID exclusiva para o banco de dados. Corresponde a BindingID de RESTORE HEADERONLY. Quando o banco de dados é restaurado, um valor novo é atribuído. |
| family_guid |
uniqueidentifier |
ID exclusiva do banco de dados original na criação. Este valor permanece o mesmo quando o banco de dados é restaurado, mesmo para um nome diferente. |
| differential_base_lsn |
numeric(25,0) |
LSN base para backups diferenciais. Para um backup diferencial de base única, as alterações em LSNs maiores que ou iguais a differential_base_lsn são incluídas no backup diferencial.
Para um diferencial com várias bases, o valor é NULL e o LSN base deve ser determinado em nível de arquivo (consulte backupfile (Transact-SQL)).
Para tipos de backup não diferencial, o valor é sempre NULL. |
| differential_base_guid |
uniqueidentifier |
Para um backup diferencial de base única, o valor é o identificador exclusivo da base diferencial.
Para diferenciais com várias bases, o valor é NULL, e a base diferencial deve ser determinada em nível de arquivo.
Para tipos de backup não diferencial, o valor é NULL. |
| compressed_backup_size |
Numeric(20,0) |
Contagem de bytes total armazenada em disco.
Para calcular a taxa de compressão, use compressed_backup_size e backup_size.
Durante uma atualização de msdb, esse valor é definido como NULL, que indica um backup não compactado. |
A seguir apresento o conjunto de colunas existentes na Tabela de Sistema BackupMediaFamily:
|
Nome da coluna
|
Tipo de dados
|
Descrição
|
| media_set_id |
int |
Número de identificação exclusivo que identifica o conjunto de mídias do qual esta família é um membro. Faz referência a backupmediaset(media_set_id) |
| family_sequence_number |
tinyint |
Posição desta família de mídias no conjunto de mídias. |
| media_family_id |
uniqueidentifier |
Número de identificação exclusivo que identifica a família de mídias. Pode ser NULL. |
| media_count |
int |
Número de mídias na família. Pode ser NULL. |
| logical_device_name |
nvarchar(128) |
Nome deste dispositivo de backup em sys.backup_devices.name. Se este dispositivo de backup for temporário (em oposição a um dispositivo de backup permanente que existe em sys.backup_devices), o valor de logical_device_name será NULL. |
| physical_device_name |
nvarchar(260) |
Nome físico do dispositivo de backup. Pode ser NULL. |
| device_type |
tinyint |
Tipo de dispositivo de backup:
Disco:
2 = Temporário.
102 = Permanente.
Fita:
5 = Temporário.
105 = Permanente.
Pode ser NULL.
Todos os nomes de dispositivos permanentes e números de dispositivo podem ser encontrado em sys.backup_devices. |
| physical_block_size |
int |
Tamanho do bloco físico usado para gravar a família de mídias. Pode ser NULL. |
| mirror |
tinyint |
Número de espelhos (0-3). |
Bem agora que já conhecemos um pouco sobre cada Tabela de Sistema, seu conjunto de colunas e qual a finalidade de cada uma delas, vamos agora conhecer o código utilizado para obter o histórico de backup. Vou chamar este código de Código 1 – Histórico de Backups – Banco de Dados, apresentado abaixo:
— Código – Histórico de Backup – Banco de Dados —
SELECT
SERVERPROPERTY(‘Servername’) AS ‘Servidor’,
msdb.dbo.backupset.database_name As ‘Database’,
CASE msdb..backupset.type
WHEN ‘D’ THEN ‘Database’
WHEN ‘L’ THEN ‘Log’
WHEN ‘I’ THEN ‘Diferencial’
WHEN ‘F’ THEN ‘File ou Filegroup’
WHEN ‘G’ THEN ‘Diferencial Arquivo’
WHEN ‘P’ THEN ‘Parcial’
WHEN ‘Q’ THEN ‘Diferencial Parcial’
END AS ‘Tipo do Backup’,
msdb.dbo.backupset.backup_start_date As ‘Data Execuo’,
msdb.dbo.backupset.backup_finish_date As ‘Data Encerramento’,
msdb.dbo.backupset.expiration_date As ‘Data de Expirao’,
(msdb.dbo.backupset.backup_size / 1024) As ‘Tamanho do Backup em MBs’,
msdb.dbo.backupmediafamily.logical_device_name As ‘Dispositivo ou Local de Backup’,
msdb.dbo.backupmediafamily.physical_device_name As ‘Caminho do Arquivo’,
msdb.dbo.backupset.description As ‘Descrio’,
Case msdb.dbo.backupset.compatibility_level
When 80 Then ‘SQL Server 2000’
When 90 Then ‘SQL Server 2005’
When 100 Then ‘SQL Server 2008 ou SQL Server 2008 R2’
When 110 Then ‘SQL Server 2012’
End As ‘Nvel de Compatibilidade’,
msdb.dbo.backupset.name AS ‘Backup Set’
FROM
msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 103) >= GETDATE() – 15)
ORDER
BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date desc
Abaixo apresento a Figura 1 que ilustra o retorno das informações referente aos Históricos de Execução de Backup:
Figura 1 – Histórico de Execução de Backups de Bancos de Dados no Microsoft SQL Server.
Você pode observar que na claúsula Where temos a possibilidade de informar a quantidade de dias que você deseja que o SQL Server apresente o Histórico de Execução dos Backups, neste exemplo, estou solicitando o histórico com base nos últimos 15 dias.
Agora com o Código 1, fica um pouco mais fácil compreender como podemos encontrar estas informações. Fique a vontade para personalizar este código de acordo com a sua necessidade.
Vou encerrando mais esta dica. Acredito que consegui atingir o meu objetivo. Mais uma vez agradeço a sua visita, conto com a sua participação no meu blog.
Um grande abraço.
Até mais.
Sincronização concluída:
Fontes e Direitos Autorais: 
Fontes e Direitos Autorais: Windows Defender Security Intelligence – 28/05/2018.
Junior Galvão - MVP - Data Platform 