SQL Server: scripts para administração de backups ⋆ Blog do Cardoso

(Last Updated On: 2017-02-14)

Nesse post disponibilizo alguns scripts para ajudar na administração dos backups de servidores Microsoft SQL Server. A maioria dos scripts abaixo foram testados nas versões 2000, 2005, 2008, 2012 e 2014. Pode ser que sejam necessários ajustes nos scripts para que executem corretamente nestas versões e em versões futuras do SGBD da Microsoft.

Esse post será atualizado sempre que eu tenha novas necessidades de scripts para administrar o backup dos meus bancos de dados SQL Server.

1. Lista o RECOVERY MODEL de todas as bases de dados

SELECT recovery_model_desc, 
	count(*) 
FROM sys.databases
GROUP BY recovery_model_desc

SELECT recovery_model_desc,

count(*)

FROM sys.databases

GROUP BY recovery_model_desc

2. Gera script para alterar RECOVERY MODEL

--Gera script para alterar o RECOVERY MODEL para FULL das bases de dados configuradas em SIMPLE
SELECT 'ALTER DATABASE ['+NAME+'] SET RECOVERY FULL;' 
FROM sys.databases 
WHERE recovery_model_desc = 'simple' 
      AND database_id > 4
ORDER BY name;

--Gera script para alterar o RECOVERY MODEL para FULL das bases de dados configuradas em SIMPLE

SELECT 'ALTER DATABASE ['+NAME+'] SET RECOVERY FULL;'

FROM sys.databases

WHERE recovery_model_desc = 'simple'

AND database_id > 4

ORDER BY name;

3. Lista últimos backups FULL executados

--Lista últimos backups executados. Recovery FULL.
SELECT 
	database_name,
	max(backup_finish_date) DATA_TERMINO,
	case type 
		when 'L' then 'Log' end,
	recovery_model 
FROM msdb..backupset
WHERE type = 'L'
GROUP BY 
	database_name,
	type,
	recovery_model
ORDER BY 2

--Lista últimos backups executados. Recovery FULL.

SELECT

database_name,

max(backup_finish_date) DATA_TERMINO,

case type

when 'L' then 'Log' end,

recovery_model

FROM msdb..backupset

WHERE type = 'L'

GROUP BY

database_name,

type,

recovery_model

ORDER BY 2

--Lista últimos backups full
SELECT 
	name, 
	max(backup_finish_date) 
FROM 
	msdb..backupset
WHERE 
	type = 'D'
	AND backup_finish_date >= '2014-04-01 0:0:01'
GROUP BY 
	name;

--Lista últimos backups full

SELECT

name,

max(backup_finish_date)

FROM

msdb..backupset

WHERE

type = 'D'

AND backup_finish_date >= '2014-04-01 0:0:01'

GROUP BY

name;

4. Lista localização do último backup no disco

--Lista último local (no disco) do backup
SELECT bs.name, 
	bmf.physical_device_name, 
	max(bs.backup_finish_date)
FROM msdb..backupset bs 
		JOIN msdb..backupmediafamily bmf
		ON bs.media_set_id = bmf.media_set_id
WHERE 
	physical_device_name LIKE '%:%'
GROUP BY 
	bs.name, 
	bmf.physical_device_name
ORDER BY 3 DESC;

--Lista último local (no disco) do backup

SELECT bs.name,

bmf.physical_device_name,

max(bs.backup_finish_date)

FROM msdb..backupset bs

JOIN msdb..backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE

physical_device_name LIKE '%:%'

GROUP BY

bs.name,

bmf.physical_device_name

ORDER BY 3 DESC;

5. Mostra progresso dos backups

-- MOSTRA TEMPO DECORRIDO E RESTANTE DO BACKUP
-- Removendo ou ajustando a clausula WHERE mostra outros os processos em execucao
SELECT command,
		s.text,
		start_time,
		percent_complete,
		CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
		+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
		+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
		CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
		+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
		+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
		dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM 
	sys.dm_exec_requests r
CROSS APPLY 
	sys.dm_exec_sql_text(r.sql_handle) s
WHERE 
	r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');

-- MOSTRA TEMPO DECORRIDO E RESTANTE DO BACKUP

-- Removendo ou ajustando a clausula WHERE mostra outros os processos em execucao

SELECT command,

s.text,

start_time,

percent_complete,

CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '

+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '

+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,

CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '

+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '

+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,

dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time

FROM

sys.dm_exec_requests r

CROSS APPLY

sys.dm_exec_sql_text(r.sql_handle) s

WHERE

r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');

6. Restaurar backup

-- Altera a base de dados para SINGLE_USER
ALTER DATABASE [NomeBaseDeDados]
SET SINGLE_USER WITH
ROLLBACK 
   --AFTER 60 --Espera 60 segundos para que as transações ativas sejam completadas
   IMMEDIATE  --Termina as transações ativas imediatamente

-- Restore
RESTORE DATABASE [NomeBaseDeDados]
FROM 
	DISK = 'D:\BackUp\ArquivoBackup.bak'
	WITH MOVE 'NomeLogicoArquivoMDF' 
		TO 'D:\Data\ArquivoMDF.mdf',
	MOVE 'NomeLogicoArquivoLDF' 
		TO 'D:\Data\ArquivoLDF.ldf'

		
/*If there is no error in statement before database will be in multiuser
mode.  If error occurs please execute following command it will convert
database in multi user.*/

-- Altera a base de dados para MULTI_USER
ALTER DATABASE [NomeBaseDeDados] SET MULTI_USER
GO

-- Altera a base de dados para SINGLE_USER

ALTER DATABASE [NomeBaseDeDados]

SET SINGLE_USER WITH

ROLLBACK

--AFTER 60 --Espera 60 segundos para que as transações ativas sejam completadas

IMMEDIATE --Termina as transações ativas imediatamente

-- Restore

RESTORE DATABASE [NomeBaseDeDados]

FROM

DISK = 'D:\BackUp\ArquivoBackup.bak'

WITH MOVE 'NomeLogicoArquivoMDF'

TO 'D:\Data\ArquivoMDF.mdf',

MOVE 'NomeLogicoArquivoLDF'

TO 'D:\Data\ArquivoLDF.ldf'

/*If there is no error in statement before database will be in multiuser

mode. If error occurs please execute following command it will convert

database in multi user.*/

-- Altera a base de dados para MULTI_USER

ALTER DATABASE [NomeBaseDeDados] SET MULTI_USER

7. Histórico dos backups

SELECT
      backup_type =
            CASE f.[type]
                WHEN 'D' THEN 'Full'
                WHEN 'L' THEN 'Log'
                WHEN 'I' THEN 'Diff'
            END
    , f.database_name
    , f.backup_start_date
    , f.backup_finish_date
    , duration = CAST(f.backup_finish_date - f.backup_start_date AS TIME)
    , f.backup_size
    , b.physical_device_name
FROM (
    SELECT
          s.media_set_id
        , s.[type]
        , s.database_name
        , s.backup_start_date
        , s.backup_finish_date
        , backup_size =
            CASE WHEN s.backup_size = s.compressed_backup_size
                THEN s.backup_size
                ELSE s.compressed_backup_size
            END / 1048576.
        , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
    FROM msdb.dbo.backupset s
    --WHERE s.database_name = DB_NAME()
) f
JOIN msdb.dbo.backupmediafamily b ON f.media_set_id = b.media_set_id
WHERE f.RowNum = 1
ORDER BY f.backup_finish_date DESC

SELECT

backup_type =

CASE f.[type]

WHEN 'D' THEN 'Full'

WHEN 'L' THEN 'Log'

WHEN 'I' THEN 'Diff'

END

, f.database_name

, f.backup_start_date

, f.backup_finish_date

, duration = CAST(f.backup_finish_date - f.backup_start_date AS TIME)

, f.backup_size

, b.physical_device_name

FROM (

SELECT

s.media_set_id

, s.[type]

, s.database_name

, s.backup_start_date

, s.backup_finish_date

, backup_size =

CASE WHEN s.backup_size = s.compressed_backup_size

THEN s.backup_size

ELSE s.compressed_backup_size

END / 1048576.

, RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)

FROM msdb.dbo.backupset s

--WHERE s.database_name = DB_NAME()

) f

JOIN msdb.dbo.backupmediafamily b ON f.media_set_id = b.media_set_id

WHERE f.RowNum = 1

ORDER BY f.backup_finish_date DESC

SQL Server: scripts para administração de backups

1. Lista o RECOVERY MODEL de todas as bases de dados

2. Gera script para alterar RECOVERY MODEL

3. Lista últimos backups FULL executados

4. Lista localização do último backup no disco

5. Mostra progresso dos backups

6. Restaurar backup

7. Histórico dos backups

Relacionado

Deixe uma resposta Cancelar resposta

Posts recentes

Procurar

Arquivos

SQL Server: scripts para administração de backups

1. Lista o RECOVERY MODEL de todas as bases de dados

2. Gera script para alterar RECOVERY MODEL

3. Lista últimos backups FULL executados

4. Lista localização do último backup no disco

5. Mostra progresso dos backups

6. Restaurar backup

7. Histórico dos backups

Compartilhe isso:

Relacionado

Deixe uma resposta Cancelar resposta

Posts recentes

Categorias

Procurar

Arquivos