Tipos de Backup:
Full Backup
O Full Backup (Backup Completo) captura todos os dados que estão armazenados no banco de dados. A Engine executa esta tarefa copiando todas as Extents (Uma Extent contém 8 páginas de dados físicas e contínuas, ocupando 64kb de espaço) que possuem objetos do banco alocados. Com este tipo de backup, é possível recriar toda a base de dados. Além disso, ele sempre está disponível para o Administrador, independente do Recovery Model (Full, Bulk Logged ou Simple) configurando no banco em questão.
Full Backup
O Full Backup (Backup Completo) captura todos os dados que estão armazenados no banco de dados. A Engine executa esta tarefa copiando todas as Extents (Uma Extent contém 8 páginas de dados físicas e contínuas, ocupando 64kb de espaço) que possuem objetos do banco alocados. Com este tipo de backup, é possível recriar toda a base de dados. Além disso, ele sempre está disponível para o Administrador, independente do Recovery Model (Full, Bulk Logged ou Simple) configurando no banco em questão.
Differential Backup
O Differential Backup (Backup Diferencial) captura todas as Extents que sofreram alterações desde o ultimo Full Backup. Isso significa que todas as alterações de dados e objetos realizadas no banco são copiadas e armazenadas. As informações das Extents alteradas são armazenadas através do Extents Map.
Extent Map é um conjunto de páginas de dados pertencentes ao banco de dados, onde é armazenado um mapeamento de todas as Extents usadas pela base de dados. Cada Extent é um bit no mapa, de valor inicial 0 (zero). Quando uma Extent sofre alterações, o SQL Server acessa o mapa e marca com o valor 1. Esta é a condição usada pela Backup Engine para realizar o backup diferencial: apenas as Extents que possuem o valor 1 no mapeamento. Quando é realizado o Full Backup, todos os valores são zerados.
O backup diferencial sempre trabalha em conjunto com o backup completo: caso não exista um backup completo do banco, o SQL Server não permite a criação de um backup diferencial. Graças a integração com o Full Backup, o Differential Backup também é independente do Recovery Model configurado na base de dados.
Por fim, vale ressaltar que o backup diferencial não é a mesma coisa de um backup incremental: cada backup diferencial criado pode substituir todos os backups (Diferenciais e de Log) criados anteriormente até o ultimo backup completo, nos caso de restauração da base de dados.
O backup diferencial sempre trabalha em conjunto com o backup completo: caso não exista um backup completo do banco, o SQL Server não permite a criação de um backup diferencial. Graças a integração com o Full Backup, o Differential Backup também é independente do Recovery Model configurado na base de dados.
Por fim, vale ressaltar que o backup diferencial não é a mesma coisa de um backup incremental: cada backup diferencial criado pode substituir todos os backups (Diferenciais e de Log) criados anteriormente até o ultimo backup completo, nos caso de restauração da base de dados.
O Transaction Log Backup (Backup do Log de Transações) trabalha em cima do log ativo, capturando todas as transações finalizadas deste o ultimo backup, qualquer que seja o tipo. Este tipo de backup é incremental: cada vez que é realizado, ele inicia a cópia do ponto em que foi realizado o ultimo backup e copia todas as transações finalizadas gravadas no log. Ao encontrar uma transação em aberto, o SQL Server finaliza a cópia.
Todas as transações que foram copiadas para o backup são marcadas pelo SQL Server. Esta marcação informa ao SQL Server que a transação em questão pode ser substituída por outra nova, ajudando a minimizar os impactos do crescimento do arquivo de log.
Backup em Disco
TEMP (Backup temporário).
BACKUP DATABASE DB_BACKUP
TO DISK = 'C:\BACKUP\BKP1.BKP'
BACKUP DATABASE DB_BACKUP
TO DISK = '\\192.168.1.32\SHARED\BACKUP\BKP1.BKP'
NAMED (Backup nomeado)
Pode ser feito de diferentes bancos de dados.
EXEC sp_addumpdevice 'DISK', 'DEV_BKP1', 'C:\BACKUP\DEV_BAKP1.BAK'
BACKUP DATABASE DB_BACKUP
TO DEV_BKP1
BACKUP DATABASE DB_MASTER
TO DEV_BKP1
Tudo isto pode ser feito pela interface conforme print abaixo, clicando com o botão da direita:
Criando dois dispositivos
EXEC SP_ADDUMPDEVICE 'DISK','DEV01','C:\BACKUP\DEV01_FULL.BAK'
EXEC SP_ADDUMPDEVICE 'DISK','DEV02','C:\BACKUP\DEV02_FULL.BAK'
BACKUP DATABASE DB_BACKUP
TO DEV01,DEV02
WITH NAME = 'BKP_FULL_DB_BACKUP',DESCRIPTION = 'A MIRRORED FULL BACKUP'
Comandos para obter informações
Verifica se o arquivo de backup é valido.
RESTORE VERIFYONLY
FROM DEV01
O comando LABELONL é util para dar uma olhada o que tem no backup.
RESTORE LABELONLY
FROM DEV01
O comando FILELISTONLY é interessante para ver quais arquivos contem o backup.
RESTORE FILELISTONLY
FROM DEV01
O comando HEADERONLY traz outras informações a repeito do backup.
RESTORE HEADERONLY
FROM DEV01Restaurando backup
USE MASTER
RESTORE DATABASE DB_BACKUP
FROM DEV01,DEV02
WITH REPLACE
Obs: A clausula WITH REPLACE é porque o banco de dados já existe.
O unico backup obrigatório é o backup FULL.
O backup de log limpa o log do database.
Visualizar arquivos de dados.
1) BACKUP FULL
O backup de log limpa o log do database.
Visualizar arquivos de dados.
1) BACKUP FULL
2) BACKUP DIFFERENCIAL (SMALLER, QUICKER)
BACKUP STRATEGY:
BACKUP FULL 10:00
BACKUP STRATEGY:
BACKUP FULL 10:00
BACKUP DIFF. 11:00
BACKUP DIFF. 12:00
RESTORE PROCESS:
AT 10:00 -> ONLY BACKUP FULL
AT 11:00 -> BACKUP FULL + BACKUP DIFF.
AT 12:00 -> BACKUP FULL + BACKUP DIFF. + BACKUP DIFF.
USE DB_BACKUP
CREATE TABLE TB_BKPDIFF
(
COL1 INT,
COL2 VARCHAR(10),
COL3 DATETIME
)
SELECT TOP 10 * FROM TB_BKPDIFF
EXEC SP_ADDUMPDEVICE 'DISK','DEV_FULL','C:\BACKUP\DEV_FULL.BAK'
EXEC SP_ADDUMPDEVICE 'DISK','DEV_DIFF','C:\BACKUP\DEV_DIFF.BAK'
BACKUP DATABASE DB_BACKUP
TO DEV_FULL
INSERT TB_BKPDIFF VALUES(1,'A',GETDATE())
INSERT TB_BKPDIFF VALUES(2,'B',GETDATE())
INSERT TB_BKPDIFF VALUES(3,'C',GETDATE())
INSERT TB_BKPDIFF VALUES(4,'D',GETDATE())
BACKUP DATABASE DB_BACKUP
TO DEV_DIFF
WITH DIFFERENTIAL
INSERT TB_BKPDIFF VALUES(5,'E',GETDATE())
INSERT TB_BKPDIFF VALUES(6,'F',GETDATE())
INSERT TB_BKPDIFF VALUES(7,'G',GETDATE())
INSERT TB_BKPDIFF VALUES(8,'H',GETDATE())
BACKUP DATABASE DB_BACKUP
TO DEV_DIFF
WITH DIFFERENTIAL
RESTORE HEADERONLY
FROM DEV_FULL
RESTORE HEADERONLY
FROM DEV_DIFF
USE DB_BACKUP
CREATE TABLE TB_BKPDIFF
(
COL1 INT,
COL2 VARCHAR(10),
COL3 DATETIME
)
SELECT TOP 10 * FROM TB_BKPDIFF
EXEC SP_ADDUMPDEVICE 'DISK','DEV_FULL','C:\BACKUP\DEV_FULL.BAK'
EXEC SP_ADDUMPDEVICE 'DISK','DEV_DIFF','C:\BACKUP\DEV_DIFF.BAK'
BACKUP DATABASE DB_BACKUP
TO DEV_FULL
INSERT TB_BKPDIFF VALUES(1,'A',GETDATE())
INSERT TB_BKPDIFF VALUES(2,'B',GETDATE())
INSERT TB_BKPDIFF VALUES(3,'C',GETDATE())
INSERT TB_BKPDIFF VALUES(4,'D',GETDATE())
BACKUP DATABASE DB_BACKUP
TO DEV_DIFF
WITH DIFFERENTIAL
INSERT TB_BKPDIFF VALUES(5,'E',GETDATE())
INSERT TB_BKPDIFF VALUES(6,'F',GETDATE())
INSERT TB_BKPDIFF VALUES(7,'G',GETDATE())
INSERT TB_BKPDIFF VALUES(8,'H',GETDATE())
BACKUP DATABASE DB_BACKUP
TO DEV_DIFF
WITH DIFFERENTIAL
RESTORE HEADERONLY
FROM DEV_FULL
RESTORE HEADERONLY
FROM DEV_DIFF
1) BACKUP FULL
USE MASTER
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
USE MASTER
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
2) BACKUP FULL + BACKUP DIFF.
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE, NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=1,REPLACE,RECOVERY
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE, NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=1,REPLACE,RECOVERY
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
3) BACKUP FULL + BACKUP DIFF. + BACKUP DIFF.
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE, NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=1,REPLACE,NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=2,REPLACE,RECOVERY
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
CASO BACKUP DIFERENCIAL1) BACKUP FULL
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE, NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=1,REPLACE,NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=2,REPLACE,RECOVERY
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
CASO BACKUP DIFERENCIAL1) BACKUP FULL
2) BACKUP DIFFERENCIAL (SMALLER, QUICKER)
BACKUP STRATEGY:
BACKUP FULL 10:00
BACKUP DIFF. 11:00
BACKUP DIFF. 12:00
RESTORE PROCESS:
AT 10:00 -> ONLY BACKUP FULLAT 11:00 -> BACKUP FULL + BACKUP DIFF.AT 12:00 -> BACKUP FULL + BACKUP DIFF. + BACKUP DIFF.
USE DB_BACKUP
CREATE TABLE TB_BKPDIFF
(
COL1 INT,
COL2 VARCHAR(10),
COL3 DATETIME
)
SELECT TOP 10 * FROM TB_BKPDIFF
EXEC SP_ADDUMPDEVICE 'DISK','DEV_FULL','C:\BACKUP\DEV_FULL.BAK'
EXEC SP_ADDUMPDEVICE 'DISK','DEV_DIFF','C:\BACKUP\DEV_DIFF.BAK'
BACKUP DATABASE DB_BACKUP
TO DEV_FULL
INSERT TB_BKPDIFF VALUES(1,'A',GETDATE())
INSERT TB_BKPDIFF VALUES(2,'B',GETDATE())
INSERT TB_BKPDIFF VALUES(3,'C',GETDATE())
INSERT TB_BKPDIFF VALUES(4,'D',GETDATE())
BACKUP DATABASE DB_BACKUP
TO DEV_DIFF
WITH DIFFERENTIAL
INSERT TB_BKPDIFF VALUES(5,'E',GETDATE())
INSERT TB_BKPDIFF VALUES(6,'F',GETDATE())
INSERT TB_BKPDIFF VALUES(7,'G',GETDATE())
INSERT TB_BKPDIFF VALUES(8,'H',GETDATE())
BACKUP DATABASE DB_BACKUP
TO DEV_DIFF
WITH DIFFERENTIAL
RESTORE HEADERONLY
FROM DEV_FULL
RESTORE HEADERONLY
FROM DEV_DIFF
AT 10:00 -> ONLY BACKUP FULLAT 11:00 -> BACKUP FULL + BACKUP DIFF.AT 12:00 -> BACKUP FULL + BACKUP DIFF. + BACKUP DIFF.
USE DB_BACKUP
CREATE TABLE TB_BKPDIFF
(
COL1 INT,
COL2 VARCHAR(10),
COL3 DATETIME
)
SELECT TOP 10 * FROM TB_BKPDIFF
EXEC SP_ADDUMPDEVICE 'DISK','DEV_FULL','C:\BACKUP\DEV_FULL.BAK'
EXEC SP_ADDUMPDEVICE 'DISK','DEV_DIFF','C:\BACKUP\DEV_DIFF.BAK'
BACKUP DATABASE DB_BACKUP
TO DEV_FULL
INSERT TB_BKPDIFF VALUES(1,'A',GETDATE())
INSERT TB_BKPDIFF VALUES(2,'B',GETDATE())
INSERT TB_BKPDIFF VALUES(3,'C',GETDATE())
INSERT TB_BKPDIFF VALUES(4,'D',GETDATE())
BACKUP DATABASE DB_BACKUP
TO DEV_DIFF
WITH DIFFERENTIAL
INSERT TB_BKPDIFF VALUES(5,'E',GETDATE())
INSERT TB_BKPDIFF VALUES(6,'F',GETDATE())
INSERT TB_BKPDIFF VALUES(7,'G',GETDATE())
INSERT TB_BKPDIFF VALUES(8,'H',GETDATE())
BACKUP DATABASE DB_BACKUP
TO DEV_DIFF
WITH DIFFERENTIAL
RESTORE HEADERONLY
FROM DEV_FULL
RESTORE HEADERONLY
FROM DEV_DIFF
1) BACKUP FULL
USE MASTER
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
2) BACKUP FULL + BACKUP DIFF.
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE, NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=1,REPLACE,RECOVERY
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
3) BACKUP FULL + BACKUP DIFF. + BACKUP DIFF.
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE, NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=1,REPLACE,NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=2,REPLACE,RECOVERY
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
DBCC SHOWFILESTATS
exec SP_ADDUMPDEVICE 'DISK','DEV_FULL','C:\BACKUP\DEV_FULL.BAK'
exec SP_ADDUMPDEVICE 'DISK','DEV_LOG','C:\BACKUP\DEV_LOG.BAK'
CREATE TABLE TB_TIME
(
COL1 INT,
COL2 DATETIME
)
SELECT * FROM TB_TIME
Bakup full.
BACKUP DATABASE DB_BACKUP
TO DEV_FULL
INSERT TB_TIME VALUES(1,GETDATE())
INSERT TB_TIME VALUES(2,GETDATE())
Backup do Log.
BACKUP LOG DB_BACKUP
TO DEV_LOG
INSERT TB_TIME VALUES(3,GETDATE())
INSERT TB_TIME VALUES(4,GETDATE())
BACKUP LOG DB_BACKUP
TO DEV_LOG
DELETE TB_TIME
BACKUP LOG DB_BACKUP
TO DEV_LOG
RECOVERY
USE MASTER
Restaurando primeiramente backup full.
A clausula NORECOVERY e para ninguem utilizar o mesmo ate que termine o recovery por completo.
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE,NORECOVERY
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,NORECOVERY,FILE=1
O ultimo backup que for voltar colocal o mesmo como RECOVERY para colocar o banco de dados em uso.
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,RECOVERY,FILE=2
SELECT * FROM DB_BACKUP.DBO.TB_TIME
Restaurando o log na hora determinada 2008-06-15 17:50:44.873.
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE,NORECOVERY
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,NORECOVERY,FILE=1
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,NORECOVERY,FILE=2
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,RECOVERY,FILE=3,STOPAT='2008-06-15 17:51:00.000'
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE, NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=1,REPLACE,NORECOVERY
RESTORE DATABASE DB_BACKUP
FROM DEV_DIFF
WITH FILE=2,REPLACE,RECOVERY
SELECT *
FROM DB_BACKUP.DBO.TB_BKPDIFF
DBCC SHOWFILESTATS
exec SP_ADDUMPDEVICE 'DISK','DEV_FULL','C:\BACKUP\DEV_FULL.BAK'
exec SP_ADDUMPDEVICE 'DISK','DEV_LOG','C:\BACKUP\DEV_LOG.BAK'
CREATE TABLE TB_TIME
(
COL1 INT,
COL2 DATETIME
)
SELECT * FROM TB_TIME
Bakup full.
BACKUP DATABASE DB_BACKUP
TO DEV_FULL
INSERT TB_TIME VALUES(1,GETDATE())
INSERT TB_TIME VALUES(2,GETDATE())
Backup do Log.
BACKUP LOG DB_BACKUP
TO DEV_LOG
INSERT TB_TIME VALUES(3,GETDATE())
INSERT TB_TIME VALUES(4,GETDATE())
BACKUP LOG DB_BACKUP
TO DEV_LOG
DELETE TB_TIME
BACKUP LOG DB_BACKUP
TO DEV_LOG
RECOVERY
USE MASTER
Restaurando primeiramente backup full.
A clausula NORECOVERY e para ninguem utilizar o mesmo ate que termine o recovery por completo.
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE,NORECOVERY
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,NORECOVERY,FILE=1
O ultimo backup que for voltar colocal o mesmo como RECOVERY para colocar o banco de dados em uso.
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,RECOVERY,FILE=2
SELECT * FROM DB_BACKUP.DBO.TB_TIME
Restaurando o log na hora determinada 2008-06-15 17:50:44.873.
RESTORE DATABASE DB_BACKUP
FROM DEV_FULL
WITH REPLACE,NORECOVERY
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,NORECOVERY,FILE=1
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,NORECOVERY,FILE=2
RESTORE LOG DB_BACKUP
FROM DEV_LOG
WITH REPLACE,RECOVERY,FILE=3,STOPAT='2008-06-15 17:51:00.000'