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
1 2 3 4 | SELECT recovery_model_desc, count(*) FROM sys.databases GROUP BY recovery_model_desc |
2. Gera script para alterar RECOVERY MODEL
1 2 3 4 5 6 | --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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | --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 |
1 2 3 4 5 6 7 8 9 10 11 | --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
1 2 3 4 5 6 7 8 9 10 11 12 13 | --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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- 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 |
7. Histórico dos backups
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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 |