novembre
2011
Un article de Ptit_Dje
J'ai longtemps cru qu'un full backup d'une base de données en full recovery model rompait la chaine des transaction logs backup de la base de données. Je pensais qu'il était donc indispensable d'utiliser l'option with copy_only pour effectuer le backup de celle-ci afin de ne pas casser la chaine de tlogs backup. Jusqu'au beau jour ou un collègue me dit que ce n'est pas le cas. - Sérieux ?! Je suis sceptique...
La dessus, j’ouvre SSMS et j’effectue un petit test (le code ci-dessous n’est pas l’original ;)):
alter database test set recovery full
backup database test to disk = 'c:\temp\test.bak1'
go
use test
go
create table testTable
(
id int
)
backup log test to disk = 'c:\temp\test.trn1'
insert into testTable select 1
backup database test to disk = 'c:\temp\test.bak2'
insert into testTable select 2
backup log test to disk = 'c:\temp\test.trn2'
insert into testTable select 3
backup log test to disk = 'c:\temp\test.trn3_copy' with copy_only
insert into testTable select 4
backup log test to disk = 'c:\temp\test.trn4'
insert into testTable select 5
backup log test to disk = 'c:\temp\test.trn5'
insert into testTable select 6
alter database test set recovery simple
alter database test set recovery full
backup database test to disk = 'c:\temp\test.bak6'
backup log test to disk = 'c:\temp\test.trn6'
GO
use master
GO
drop database test
GO
-- Full backup does not break the log chain and tlog backup with no copy neither
print 'Restore session 1'
restore database test from disk = 'c:\temp\test.bak1' with norecovery
restore database test from disk = 'c:\temp\test.trn1' with norecovery
restore database test from disk = 'c:\temp\test.trn2' with norecovery
restore database test from disk = 'c:\temp\test.trn4' with recovery
select 'Restore session 1', * from test.dbo.testTable
GO
drop database test
GO
-- This one is working too
print 'Restore session 2'
restore database test from disk = 'c:\temp\test.bak1' with norecovery
restore database test from disk = 'c:\temp\test.trn1' with norecovery
restore database test from disk = 'c:\temp\test.trn2' with norecovery
restore database test from disk = 'c:\temp\test.trn3_copy' with norecovery
restore database test from disk = 'c:\temp\test.trn4' with recovery
select 'Restore session 2', * from test.dbo.testTable
GO
drop database test
GO
-- Transaction log backup (without only_copy option) missing prevent to rebuild the chain
-- Error: The log in this backup set begins at LSN 21000000017200001, which is too recent to apply to the database. An earlier log backup that includes LSN 21000000017100001 can be restored.
print 'Restore session 3'
restore database test from disk = 'c:\temp\test.bak1' with norecovery
restore database test from disk = 'c:\temp\test.trn1' with norecovery
restore database test from disk = 'c:\temp\test.trn2' with norecovery
restore database test from disk = 'c:\temp\test.trn3_copy' with norecovery
restore database test from disk = 'c:\temp\test.trn5' with recovery
select 'Restore session 3', * from test.dbo.testTable
GO
drop database test
GO
-- switch to simple recovery model
-- Error: This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
print 'Restore session 4'
restore database test from disk = 'c:\temp\test.bak1' with norecovery
restore database test from disk = 'c:\temp\test.trn1' with norecovery
restore database test from disk = 'c:\temp\test.trn2' with norecovery
restore database test from disk = 'c:\temp\test.trn4' with norecovery
restore database test from disk = 'c:\temp\test.trn5' with norecovery
restore database test from disk = 'c:\temp\test.trn6' with norecovery
select 'Restore session 4', * from test.dbo.testTable
– Ah ben oui ! Tu as raison. Tu viens de casser un mythe, j’y croyais dur comme fer!
Comme on peut le voir ci-dessus, un full backup effectué sans l’option with copy_only ne casse pas une chaine de transaction log backup.
On remarquera qu’un backup du transaction log avec l’option with copy_only non plus.
Par contre, un transaction log backup effectué et manquant (restore session 3 – le fichier c:\temp\test.trn4) provoque bien une rupture de la chaine.
L’erreur générée étant:
Msg 4305, Level 16, State 1, Line 10
The log in this backup set begins at LSN **********************, which is too recent to apply to the database. An earlier log backup that includes LSN **********************, can be restored.
Msg 3013, Level 16, State 1, Line 10
RESTORE DATABASE is terminating abnormally.
On remarquera aussi que le fait de switcher le recovery modele de la base de données en simple puis de le repasser en full rompt également la chaine des transaction logs backup.
L’erreur étant maintenant différente:
Msg 4312, Level 16, State 1, Line 11
This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.