If you are not a native Portuguese speaker, or prefer to read it in another language, please scroll down the page and you will find a translator at the very right side.
Existem diversas opções para que uma determinada empresa independente de seu porte tenha uma polícia de backup bem definida, de conhecimento de todos e que seja seguida à risca, falei sobre isso no post: Política de backup – Implante esta ideia.
Para encontrar todos meus artigos sobre Backup, Restore e assuntos relacionados, Clique aqui.
Das diversas opções e combinações possíveis com todos os tipos disponíveis de Backups no SQL Server, acredito que a opção em se fazer um backup full dos bancos todos os dias seja a menos efetiva e ainda pior, a que causa de certa forma, um desperdício de recursos de processamento e armazenamento.
Para comprovar o que estou afirmando no trecho anterior, vou realizar a seguir testes com as seguintes combinações (que já encontrei em muitas empresas que prestei serviços):
- Backup Full diário (sem Backup Diferencial e sem Backup de Log)
- Backup Full diário + Backup Diferencial no meio do dia.
- Backup Full semanal + Backup Diferencial diário + Backup de Log a cada hora útil
- Backup Full semanal + Backup de Log a cada hora útil.
Caso queira ir direto aos resultados dos testes, CLIQUE AQUI.
Sequência de testes realizados:
Para os exemplos, utilizarei o banco de dados StackOverflow2010, clique aqui para mais informações e download desse banco de dados de exemplo.
Teste 1:
Backup Full diário (sem Backup Diferencial e sem Backup de Log).
O script a seguir simula a criação de um Backup Full por dia, sem qualquer outro tipo de backup (Differential ou TLog) durante uma semana.
|
1 2 3 4 5 6 7 |
backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Segunda.bak' with stats = 1 backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Terca.bak' with stats = 1 backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Quarta.bak' with stats = 1 backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Quinta.bak' with stats = 1 backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Sexta.bak' with stats = 1 backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Sabado.bak' with stats = 1 backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Domingo.bak' with stats = 1 |
Resultado:
- Tamanho total (semana): 59.9GB
- Recuperabilidade: Somente no momento da conclusão do backup (backup_finish_date), sendo assim possível que haja a perda de até 1 dia de dados.
- Comentário: Infelizmente já tive alguns clientes onde o ambiente era considerado altamente crítico, com uma rotina de backup como a supracitada com o argumento de que não poderiam perder dados, e por esse motivo, era realizado o backup full diário, sem qualquer backup adicional. Muito cuidado nessa estratégia.
Teste 2:
Backup Full diário + Backup Diferencial no meio do dia.
O script a seguir simula a criação de um Backup Full (D) todos os dias, e adicionalmente um Backup do tipo Differential (I) que seria executado diariamente com 12h de intervalo para o Full anterior. Para esse e os demais testes, simularemos uma carga de 20.000 registros por dia nesse banco, o que será equivalente a 160MB de dados por dia (lembrando da proporcionalidade, estamos trabalhando aqui com uma base de dados menor que 10GB).
Atente-se à recuperabilidade explicada após os exemplos dos scripts nesse teste.
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
--Garantindo que seja um banco restaurado do zero, sem nenhuma alteração USE master GO RESTORE DATABASE StackOverflow2010 FROM disk = 'E:\ToBeRestored\StackOverflow2010.bak' WITH STATS ,MOVE 'StackOverflow2010' TO 'D:\MSSQL\Data\StackOverflow2010.mdf' ,MOVE 'StackOverflow2010_log' TO 'D:\MSSQL\Log\StackOverflow2010_log.ldf' ,REPLACE GO --Alterando a durabilidade das transações para que os inserts sejam mais rápidos. ALTER DATABASE StackOverflow2010 SET DELAYED_DURABILITY = FORCED GO USE StackOverflow2010 GO -- Criação de uma tabela para exemplos de crescimento da base de dados DROP TABLE IF EXISTS dbo.TesteBackup CREATE TABLE dbo.TesteBackup ( id INT IDENTITY ,valor CHAR(8000) NOT NULL DEFAULT REPLICATE('A',8000) ) GO /******************* SEGUNDA FEIRA *******************/ --Backup Full Segunda-Feira (Propositalmente, não foi utilizado o Backup Compression) BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Segunda.bak' WITH STATS GO --Inserção de 20000 registros para simular carga de dados INSERT INTO dbo.TesteBackup DEFAULT VALUES GO 20000 EXEC sys.sp_spaceused TesteBackup --name rows reserved data index_size unused --TesteBackup 20000 160072 KB 160000 KB 8 KB 64 KB --Backup do tipo Differential de Segunda Feira BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Segunda.bak' WITH DIFFERENTIAL, STATS GO /******************* TERÇA FEIRA *******************/ --Backup Full Terça-Feira BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Terca.bak' WITH STATS GO --Inserção de 20000 registros para simular carga de dados INSERT INTO dbo.TesteBackup DEFAULT VALUES GO 20000 EXEC sys.sp_spaceused TesteBackup --name rows reserved data index_size unused --TesteBackup 40000 320200 KB 320000 KB 8 KB 192 KB --Backup do tipo Differential de Terça-Feira BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Terca.bak' WITH DIFFERENTIAL, STATS GO /******************* QUARTA FEIRA *******************/ --Backup Full Quarta-Feira (Propositalmente, não foi utilizado o Backup Compression) BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Quarta.bak' WITH STATS GO --Inserção de 20000 registros para simular carga de dados INSERT INTO dbo.TesteBackup DEFAULT VALUES GO 20000 EXEC sys.sp_spaceused TesteBackup --name rows reserved data index_size unused --TesteBackup 60000 480264 KB 480000 KB 8 KB 256 KB --Backup do tipo Differential de Quarta-Feira BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Quarta.bak' WITH DIFFERENTIAL, STATS GO /******************* QUINTA FEIRA *******************/ --Backup Full Quinta-Feira (Propositalmente, não foi utilizado o Backup Compression) BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Quinta.bak' WITH STATS GO --Inserção de 20000 registros para simular carga de dados INSERT INTO dbo.TesteBackup DEFAULT VALUES GO 20000 EXEC sys.sp_spaceused TesteBackup --name rows reserved data index_size unused --TesteBackup 80000 640264 KB 640000 KB 8 KB 256 KB --Backup do tipo Differential de Quinta-Feira BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Quinta.bak' WITH DIFFERENTIAL, STATS GO /******************* SEXTA FEIRA *******************/ --Backup Full Sexta-Feira (Propositalmente, não foi utilizado o Backup Compression) BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Sexta.bak' WITH STATS GO --Inserção de 20000 registros para simular carga de dados INSERT INTO dbo.TesteBackup DEFAULT VALUES GO 20000 EXEC sys.sp_spaceused TesteBackup --name rows reserved data index_size unused --TesteBackup 100000 800328 KB 800000 KB 8 KB 320 KB --Backup do tipo Differential de Sexta-Feira BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Sexta.bak' WITH DIFFERENTIAL, STATS GO /******************* SÁBADO *******************/ --Backup Full Sábado (Propositalmente, não foi utilizado o Backup Compression) BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Sabado.bak' WITH STATS GO --Inserção de 20000 registros para simular carga de dados INSERT INTO dbo.TesteBackup DEFAULT VALUES GO 20000 EXEC sys.sp_spaceused TesteBackup --name rows reserved data index_size unused --TesteBackup 120000 960328 KB 960000 KB 8 KB 320 KB --Backup do tipo Differential de Sabado BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Sabado.bak' WITH DIFFERENTIAL, STATS GO /******************* DOMINGO *******************/ --Backup Full Domingo (Propositalmente, não foi utilizado o Backup Compression) BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Domingo.bak' WITH STATS GO --Inserção de 20000 registros para simular carga de dados INSERT INTO dbo.TesteBackup DEFAULT VALUES GO 20000 EXEC sys.sp_spaceused TesteBackup --name rows reserved data index_size unused --TesteBackup 140000 1120328 KB 1120000 KB 8 KB 320 KB --Backup do tipo Differential de Domingo BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Domingo.bak' WITH DIFFERENTIAL, STATS GO |
Resultado:
- Tamanho total (semana): 73.1GB
- Recuperabilidade: Somente no ponto exato da conclusão de cada um dos backups (Full ou Differential), havendo dessa maneira a possibilidade real de perda de algumas horas de dados.
- Comentário: Apesar de ter melhorado um pouco a recuperabilidade do ambiente (reduzindo a possibilidade de perda de dados de 1 dia para algumas horas), o espaço necessário para armazenar 1 semana de backup aumentou consideravelmente, em troca de pouca possibilidade de recuperação real dos dados, no momento da necessidade de um restore desses backups.
Teste 3:
Backup Full semanal + Backup Diferencial diário + Backup de Log a cada hora útil.
O script simula a execução de um backup Full uma vez por semana (nesse caso será criado o exemplo de 1 semana, logo apenas 1 Backup Full), um backup Differential uma vez ao dia e um backup do Log de Transações uma vez a cada hora durante as horas úteis do dia (consideremos horas úteis o período entre 8:00 e 18:00).
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 |
--Garantindo que seja um banco restaurado do zero, sem nenhuma alteração USE master GO RESTORE DATABASE StackOverflow2010 FROM disk = 'E:\ToBeRestored\StackOverflow2010.bak' WITH STATS ,MOVE 'StackOverflow2010' TO 'D:\MSSQL\Data\StackOverflow2010.mdf' ,MOVE 'StackOverflow2010_log' TO 'D:\MSSQL\Log\StackOverflow2010_log.ldf' ,REPLACE GO --Alterando a durabilidade das transações para que os inserts sejam mais rápidos. ALTER DATABASE StackOverflow2010 SET DELAYED_DURABILITY = FORCED GO --Garantindo Recovery Model como Full para que possa ser realizado Backup do Transaction log. ALTER DATABASE StackOverflow2010 SET RECOVERY FULL GO USE StackOverflow2010 GO -- Criação de uma tabela para exemplos de crescimento da base de dados DROP TABLE IF EXISTS dbo.TesteBackup CREATE TABLE dbo.TesteBackup ( id INT IDENTITY ,valor CHAR(8000) NOT NULL DEFAULT REPLICATE('A',8000) ) GO /******************* DOMINGO (01:00) *******************/ --Backup Full Domingo 23:00 (Propositalmente, não foi utilizado o Backup Compression) BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Segunda.bak' WITH STATS GO /******************* SEGUNDA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Segunda_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 PRINT @i END --Backup do tipo Differential de SEGUNDA 23:00 BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Segunda.bak' WITH DIFFERENTIAL, STATS GO /******************* TERÇA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Terca_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END --Backup do tipo Differential de TERÇA 23:00 BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Terca.bak' WITH DIFFERENTIAL, STATS GO /******************* QUARTA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Quarta_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END --Backup do tipo Differential de QUARTA 23:00 BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Quarta.bak' WITH DIFFERENTIAL, STATS GO /******************* QUINTA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Quinta_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END --Backup do tipo Differential de QUINTA 23:00 BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Quinta.bak' WITH DIFFERENTIAL, STATS GO /******************* SEXTA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Sexta_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END --Backup do tipo Differential de SEXTA 23:00 BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Sexta.bak' WITH DIFFERENTIAL, STATS GO /******************* SÁBADO *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Sabado_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END --Backup do tipo Differential de SABADO 23:00 BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Sabado.bak' WITH DIFFERENTIAL, STATS GO /******************* DOMINGO *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Domingo_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END --Nesse caso não teria o backup full no domingo, pois iniciar-se-ia uma um novo ciclo com o backup Full |
Resultado:
- Tamanho total (semana): 17.7 GB
- Recuperabilidade: Como foi realizado uma rotina de backup contemplando Backups do Log de Transações, é possível recuperar o banco em qualquer ponto do tempo a partir de um Backup Full inicial (seja ele qual for), restaurando os backups de TLog na sequência (ou o primeiro backup Differential anterior ao ponto no tempo requerido).
- Comentário: É possível afirmar com certeza que essa é uma boa estratégia (salvo exceções) de backup para base de dados críticas que suportam pouco ou zero perda de dados. Obviamente cada empresa tem suas particularidades e isso deve ser levado em conta no momento da implantação das políticas de Backup e Restore.
Teste 4:
Backup Full semanal + Backup de Log a cada hora útil.
O script a seguir é bem semelhante ao anterior (Teste 3) com a exceção que esse não simulará a execução de um backup Differential diário. Nesse caso teremos a simulação de um backup Full aos Domingos e backups de Transaction Log a cada hora, em horas úteis (considerando horas úteis o período entre 8:00 e 18:00).
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 |
--Garantindo que seja um banco restaurado do zero, sem nenhuma alteração USE master GO RESTORE DATABASE StackOverflow2010 FROM disk = 'E:\ToBeRestored\StackOverflow2010.bak' WITH STATS ,MOVE 'StackOverflow2010' TO 'D:\MSSQL\Data\StackOverflow2010.mdf' ,MOVE 'StackOverflow2010_log' TO 'D:\MSSQL\Log\StackOverflow2010_log.ldf' ,REPLACE GO --Alterando a durabilidade das transações para que os inserts sejam mais rápidos. ALTER DATABASE StackOverflow2010 SET DELAYED_DURABILITY = FORCED GO --Garantindo Recovery Model como Full para que possa ser realizado Backup do Transaction log. ALTER DATABASE StackOverflow2010 SET RECOVERY FULL GO USE StackOverflow2010 GO -- Criação de uma tabela para exemplos de crescimento da base de dados DROP TABLE IF EXISTS dbo.TesteBackup CREATE TABLE dbo.TesteBackup ( id INT IDENTITY ,valor CHAR(8000) NOT NULL DEFAULT REPLICATE('A',8000) ) GO /******************* DOMINGO (01:00) *******************/ --Backup Full Domingo 23:00 (Propositalmente, não foi utilizado o Backup Compression) BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Segunda.bak' WITH STATS GO /******************* SEGUNDA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Segunda_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 PRINT @i END GO /******************* TERÇA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Terca_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END GO /******************* QUARTA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Quarta_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END GO /******************* QUINTA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Quinta_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END GO /******************* SEXTA *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Sexta_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END GO /******************* SÁBADO *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Sabado_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END GO /******************* DOMINGO *******************/ --Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200) WHILE @i < 19 BEGIN SET @j = 1 WHILE @j < 2001 BEGIN INSERT INTO dbo.TesteBackup DEFAULT VALUES SET @j = @j+1 END SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Domingo_'+CAST(@i AS VARCHAR(2))+'.bak''' EXEC(@backupcmd) SET @i = @i+1 END GO --Nesse caso não teria o backup full no domingo, pois iniciar-se-ia uma um novo ciclo com o backup Full |
Resultado:
- Tamanho total (semana): 11.9GB
- Recuperabilidade: Assim como na opção anterior, a geração dos backups do log de transações possibilita a restauração de backups em qualquer ponto do tempo, desde que iniciado com um backup Full e dando sequência aos demais backups de log até o ponto desejado.
- Comentários: Esse é o backup com menor custo de armazenamento que também oferece uma recuperabilidade em qualquer ponto no tempo, uma possível desvantagem é que caso ocorra a perda ou corrupção de algum arquivo de backup do log de transações, não é possível prosseguir um restore após aquele ponto, caso não haja um backup Differential subsequente.
Resultados e Considerações Finais:
Cada um dos diversos tipos de backup disponíveis no SQL Server tem sua importância e função dentro de uma política / rotina de backup bem definida que funcione de acordo com as necessidades e criticidade das aplicações e do negócio suportado pelas bases de dados presentes nas instâncias do SQL Server.
Uma política / rotina de backup com um alto nível de excelência deve compreender a utilização conjunta desses diferentes tipos de backup para fornecer de forma mais barata, rápida e correta os requisitos supracitados (cabe aqui a menção a RTO (Tempo total para recuperação) e RPO (Suportabilidade para possíveis perdas de dados)).
Veja abaixo os números dos testes:
Para dar uma noção de grandeza, farei a alteração da medida em GB para TB.
- Teste 1: Backup Full diário (sem Backup Diferencial e sem Backup de Log)
- Tamanho total em uma semana….: 59.9 TB
- Possibilidade de perda de dados..: Até 24 horas
- Indicado para (Opinião pessoal)…: NÃO INDICADO
- Teste 2: Backup Full diário + Backup Diferencial no meio do dia.
- Tamanho total em uma semana: 73.1 TB
- Possibilidade de perda de dados: 12 horas (dependendo do horário do backup)
- Indicado para (Opinião pessoal): NÃO INDICADO
- Teste 3: Backup Full semanal + Backup Diferencial diário + Backup de Log a cada hora útil
- Tamanho total em uma semana: 17.7 TB
- Possibilidade de perda de dados: Sem perda de Dados (nas CNTP)
- Indicado para (Opinião pessoal): Ambientes de Produção, mesmo com consumo um pouco maior com relação ao Teste 4.
- Teste 4: Backup Full semanal + Backup de Log a cada hora útil.
- Tamanho total em uma semana: 11.9 TB
- Possibilidade de perda de dados: Sem perda de Dados (nas CNTP)
- Indicado para (Opinião pessoal): Ambientes de Produção, melhor utilização custo benefício da área de armazenamento do backup, desde que se tenha ciência da criticidade de manter a integridade dos arquivos de backup do log de transações.
Para concluir, em minha humilde opinião, o Backup Full Diário torna-se uma opção inadequada na grande maioria das situações (para ser sincero, eu não sei listar uma onde seria a melhor opção), visto que demanda um consumo maior de armazenamento e não garante a possibilidade de restauração no ponto do tempo, caso seja necessário.
Obrigado por chegar até aqui,
Caso discorde, concorde ou queira argumentar / perguntar, fique à vontade para compartilhar suas ideias nos comentários.
Abraço,
Edvaldo Castro
Obrigado pelas informações. Ajudaram muito.
Excelente sua dissertação, parabens pelo conteudo enxuto, abriu bastante minha mente sobre o assunto.
Depois se puder fale sobre armazenamento de backups na nuvem, tambem existe como faze-los usando os padroes que vc falou?
Evaldo, eu agradeço muito o seu post.
Eu não sabia como provar para a empresa onde estou no momento o quanto eles estão desperdiçando espaço e tudo mais e com esse post eu consigo informação suficiente para ser ouvido.
O cara que montou os bkps que são feitos hoje não é gestor, ele é chefe e não sabe literalmente nada de TI, inclusive é contra tecnologia. E foi esse cara quem fez os scripts de bkps que serão alterados em breve com a ajuda desse post. 🙂
Muito obrigado.
Olá Robson,
Muito obrigado pelo seu comentario no post.
Fico extremamente feliz em ler seu comentário e em saber que pude ajudá-lo. Espero que você tenha sucesso em seus argumentos para mostrar que na grande maioria das vezes, Backup Full todos os dias não necessariamente é a melhor estratégia de Backup.
Abraço,
Edvaldo Castro
Olá Eduardo, parabéns pelo artigo ficou show. Em relação ao always on em base grandes, digo 6TB a 10TB, o que você acha? e sobre o crescimento do log ?
Olá Silvestre,
Muito obrigado pelo acesso e comentário.
O AlwaysOn para bases muito grandes segue o mesmo conceito das bases menores, sendo que a diferença, é que se a base é muito transacional, você terá que monitorar a replicação das transações para as replicas secundárias e possívelmente executar um backup do log de transações com maior frequência.
Como sempre as grandes respostas da vida são: 42 e Depende… Cada caso é um caso, mas no geral, vai exigir um acompanhamento mais de perto.
Grande abraço,
Edvaldo Castro
Obrigado Edvaldo pelo feedeback! Muito bom seu artigo me ajudou nessa situação que te relatei. Parabéns!
Edvaldo, e sobre realizar ‘backup’ copiando os arquivos .mdf e .ldf? Aqui onde trabalho o pessoal de infra adquiriu uma ferramenta onde a mesma a cada 1h faz copia do diferencial dos arquivos e sobe pra nuvens. Eu sou contra esse tipo de ‘backup’ mas eles disseram que investiram na ferramenta e que esse tipo de backup não é problema. Qual sua opinião sobre apenas copiar os arquivos .mdf e .ldf?
Olá Marcos, obrigado pela leitura e comentário.
Partindo do princípio da definição de Backup como cópia de segurança, eu diria que sim, é válido. Olhando um pouco mais como um DBA e com recuperabilidade do ambiente de banco de dados, eu particularmente gosto de ter controle sobre como estão os backups e saber que posso contar com os mesmos quando precisar realizar um retore. Não sei qual é a ferramenta e nem como ela funciona, mas até onde sei, o Windows coloca um handle nos arquivos de Log e Dados para bancos de dados online, o que em teoria poderia impedir seu acesso. Se ainda não foi feito, eu sugereria testes rotineiros sobre as possibilidades de restore dessa modalidade que foi adotada aí pela sua infra.
Mas sinceramente, não me agrada muito fazer backup do SQL Server copiando arquivos, tendo uma vasta gama de opções nativas que podem fazer muito mais e com muito mais garantia.
Abraço
Edvaldo Castro