{"id":2871,"date":"2011-07-13T16:59:05","date_gmt":"2011-07-13T15:59:05","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=2871"},"modified":"2019-09-13T14:34:59","modified_gmt":"2019-09-13T13:34:59","slug":"point-in-time-recovery-et-fn_dump_dblog","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/","title":{"rendered":"Point-in-time recovery et fn_dump_dblog()"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Point-in-time recovery<\/em> d\u00e9signe une restauration de base de donn\u00e9es consistante \u00e0 un point pr\u00e9cis soit dans le temps (STOPAT), soit dans une s\u00e9quence de transactions (STOPATMARK, STOPBEFOREMARK). On ne peut faire du PIT recovery que lorsque la base est en mode de restauration complet.<\/p>\n

Le probl\u00e8me inh\u00e9rent \u00e0 ce genre de restauration, c’est qu’on ne sait pas exactement jusqu’o\u00f9 restaurer. Lorsque votre coll\u00e8gue vous dit qu’il a supprim\u00e9 accidentellement les donn\u00e9es de la table principale vers 13h45, c’est assez vague comme r\u00e9ponse. Si vous restaurez \u00e0 13h44, que fait-on des transactions valid\u00e9es entre 13h44 et le moment pr\u00e9cis o\u00f9 le delete a \u00e9t\u00e9 ex\u00e9cut\u00e9 ? Il faut jouer avec RESTORE LOG WITH STOPAT, STANDBY pour d\u00e9terminer le point dans le temps o\u00f9 les donn\u00e9es ne sont plus dans la table. Milliseconde par milliseconde ? Bof bof bof…<\/p>\n

L’alternative est d’utiliser des marques de transactions:<\/p>\n

BEGIN TRAN MiseAJourProduits\r\n<\/span>GO\r\nDELETE FROM <\/span>PRODUITS -- WHERE ID_PRODUIT = @id_produit<\/span><\/span> (a\u00efeuuu)<\/span>\r\nGO\r\n...\r\nCOMMIT TRAN MiseAJourProduits<\/span>\r\nGO<\/span><\/pre>\n
D\u00e8s lors il est possible de restaurer avant la transaction qui pose probl\u00e8me en utilisant l’option STOPBEFOREMARK de RESTORE LOG:<\/p>\n
RESTORE LOG maBASE FROM DISK='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\maBase.20110712.trn'\r\nWITH RECOVERY, STOPBEFOREMARK = 'MiseAJourProduits<\/span>'\r\nGO<\/span><\/pre>\n
Seul probl\u00e8me, c’est \u00e0 l’initiative du d\u00e9veloppeur de le faire, or la plupart du temps les transactions sont non marqu\u00e9es, je ne vous parle m\u00eame pas des progiciels…<\/p>\n
Fiat lux:<\/h2>\n
J’ai longtemps regrett\u00e9 l’absence d’un outil built-in tel que LogMiner<\/a> pour pouvoir lire le contenu d’un backup log, et pouvoir effectuer une restauration pr\u00e9cise \u00e0 la transaction pr\u00e8s. En effet,\u00a0 STOPATMARK \/ STOPBEFOREMARK permettent d’utiliser une syntaxe telle que :<\/p>\n
RESTORE LOG ... WITH STOPBEFOREMARK = 'lsn:<LSN number>'<\/span>.<\/pre>\n
Seulement voil\u00e0, comment conna\u00eetre les diff\u00e9rentes transactions embarqu\u00e9es dans un backup log ? fn_dblog() permet d’obtenir cette information sur le journal lui-m\u00eame, mais quid des backups…<\/p>\n
Et il y a quelques jours, je joue avec OPENROWSET()<\/a> et l\u00e0 je tombe sur une fonction syst\u00e8me non-document\u00e9e dans la base resource : fn_dump_dblog()<\/strong><\/span>. En lisant le nom, l’\u00e9motion m’\u00e9treint. Le souffle court, je jette un coup d’oeil au source de la fonction:<\/p>\n
create function sys.fn_dump_dblog \u00a0\r\n ( \u00a0\r\n    @start\u00a0\u00a0\u00a0 nvarchar (25) = NULL, \u00a0\r\n    @end\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar (25) = NULL, \u00a0\r\n    @devtype\u00a0 nvarchar (260) = NULL, -- NULL(DISK) | DISK | TAPE | VIRTUAL_DEVICE \u00a0<\/span><\/span>\r\n    @seqnum\u00a0\u00a0 Int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 1, \u00a0\r\n    @fname1\u00a0\u00a0 nvarchar (260) = NULL, \u00a0\r\n    @fname2\u00a0\u00a0 nvarchar (260) = NULL, \u00a0\r\n    @fname3\u00a0\u00a0 nvarchar (260) = NULL,\u00a0\r\n    ...\r\n    @fname64\u00a0\u00a0 nvarchar (260) = NULL \u00a0\r\n ) \u00a0\r\nreturns table \u00a0\r\nas \u00a0\r\n return select \u00a0\r\n     [Current LSN],  [Operation], \u00a0[Context], \u00a0[Transaction ID], \u00a0 [Tag Bits], \u00a0\r\n     [Log Record Fixed Length], \u00a0[Log Record Length], \u00a0 [Previous LSN], \u00a0\r\n     (...)\r\n     from OpenRowset (DBLog, @start, @end, @devtype, @seqnum, \u00a0\r\n     @fname1,  @fname2, \u00a0@fname3, \u00a0@fname4, \u00a0@fname5,\u00a0\r\n     (...)\r\n\u00a0    @fname62, \u00a0@fname63, \u00a0@fname64)<\/span><\/pre>\n
Et puis en gouglant un peu, je tombe sur cet article de Dimitri Furman<\/a>, une personne de MS Consulting Services \u00e0 New-York qui d\u00e9crit bri\u00e8vement la fonction et ses arguments. Je ne peux pas r\u00e9sister, il va falloir tester \u00e7a.<\/p>\n
Sc\u00e9nario de test:<\/h2>\n
On va donc cr\u00e9er une base de d\u00e9mo, peupler une table et supprimer des intervalles de valeurs en les entrela\u00e7ant de backup logs. On arrive \u00e0 une suppression par erreur, et on souhaite remonter les donn\u00e9es juste \u00e0 la transaction d’avant, sachant que les transactions ne sont pas marqu\u00e9es.<\/p>\n
\/*************************************************************************************\r\n Obj:\u00a0\u00a0 \u00a0   DEMO RESTAURATION SUITE A ERREUR MANUELLE\r\n            UTILISATION DU MODE STANDBY POUR EFFECUTER UN POINT-IN-TIME RECOVERY\r\n Aut:\u00a0\u00a0 \u00a0   dbaffaleuf@capdata\r\n Crdate:\u00a0\u00a0 \u00a02011\/07\/13\r\n*\/<\/span>\r\n\r\n-- Population de la base<\/span>\r\nuse master\r\nGO\r\nif exists (select 1 from sys.databases where name='demorecovery<\/span>')\r\n drop database demorecovery\r\nGO\r\ncreate database demorecovery\r\nGO\r\n\r\nuse demorecovery\r\nGO\r\ncreate table T1(\r\n a numeric identity,\r\n b char(4000) default replicate('b<\/span>',4000),\r\n c bigint default round(rand()*100,0))\r\nGO\r\ninsert into T1 default values\r\nGO 1000\r\ncreate unique clustered index IDX_T1C on T1(a)\r\nGO\r\ncreate index IDX_T1NC__c on T1(c)\r\nGO\r\n\r\n-- Activation du mode de r\u00e9cup\u00e9ration complet<\/span>\r\nalter database demorecovery set recovery full\r\nGO\r\nbackup database demorecovery to disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_FULL.bak<\/span>' with init, stats\r\nGO\r\n\r\n-- Quelques transactions et quelques backups de transactions<\/span>\r\ndelete from T1 where a < 10\r\nGO\r\nbackup log demorecovery to disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG1.bak<\/span>' with init, stats\r\nGO\r\ndelete from T1 where a < 20\r\nGO\r\nbackup log demorecovery to disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG2.bak<\/span>' with init, stats\r\nGO\r\ndelete from T1 where a < 30\r\nGO\r\nbackup log demorecovery to disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG3.bak<\/span>' with init, stats\r\nGO\r\n\r\n-- Trois autres transactions \u00e0 suivre, la derni\u00e8re (a<60) est une erreur manuelle<\/span>\r\ndelete from T1 where a < 40\r\nGO\r\ndelete from T1 where a < 50\r\nGO\r\ndelete from T1 where a < 60\u00a0 -- <-- CES DONNEES SONT EFFACEES PAR ERREUR !!!<\/span>\r\nGO\r\nbackup log demorecovery to disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG4.bak<\/span>' with init, stats\r\nGO\r\n\r\n-- Une autre transaction valide passe entre-temps<\/span>\r\ndelete from T1 where a > 990\r\nGO\r\n\r\n-- Le t\u00e9l\u00e9phone sonne, l'utilisateur demande s'il est possible de r\u00e9cup\u00e9rer les donn\u00e9es avant le delete where a < 60<\/span>\r\nuse master\r\nGO\r\n-- tail log backup<\/span>\r\nbackup log demorecovery to disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_TAILLOG.bak<\/span>' with init, stats, NO_TRUNCATE, NORECOVERY\r\nGO\r\nselect state_desc from sys.databases where name='demorecovery'\r\nGO\r\n\r\nstate_desc\r\n------------------------------------------------------------\r\nRESTORING<\/span>\r\n<\/em><\/span>\r\n-- On identifie le backup de journal qui embarque l'erreur: demorecovery_LOG4.bak\r\n-- on recherche les diff\u00e9rentes transactions contenues \u00e0 l'int\u00e9rieur avec fn_dump_dblog():<\/span>\r\nselect *, [Current LSN]\r\n ,Operation\r\n ,[Transaction ID]\r\n ,AllocUnitId\r\n ,[Begin Time]\r\n ,[Transaction Name]\r\n ,[End Time]\r\n ,[Description]\r\nfrom fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT,\r\n'C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG4.bak<\/span>',\r\nDEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\nDEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\nDEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\nDEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\nDEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\nDEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\nDEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\nDEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\nDEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)\r\nwhere Operation\u00a0 = 'LOP_BEGIN_XACT<\/span>'\r\n\r\nCurrent LSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Transaction ID\u00a0 Begin Time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Transaction Name\u00a0\u00a0\u00a0 Description\r\n----------------------- ----------------- --------------\u00a0 ------------------------ ------------------- -----------------------------------------------------------------------\r\n00000041:00000338:000e\u00a0 LOP_BEGIN_XACT\u00a0\u00a0\u00a0 0000:000008b8\u00a0\u00a0 2011\/07\/13 15:59:43:580\u00a0 DELETE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DELETE;0x0105000000000005150000006aa3aebb1b5c3491642b62e2e8030000\r\n00000042:00000021:0001\u00a0 LOP_BEGIN_XACT\u00a0\u00a0\u00a0 0000:000008b9\u00a0\u00a0 2011\/07\/13 15:59:43:587\u00a0 DELETE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DELETE;0x0105000000000005150000006aa3aebb1b5c3491642b62e2e8030000\r\n00000042:00000076:0001\u00a0 LOP_BEGIN_XACT\u00a0\u00a0\u00a0 0000:000008ba\u00a0\u00a0 2011\/07\/13 15:59:43:630\u00a0 DELETE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DELETE;0x0105000000000005150000006aa3aebb1b5c3491642b62e2e8030000<\/em><\/span>\r\n\r\n\r\n-- On restaure jusqu'au N-1 backup log avant pb<\/span>\r\nrestore database demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_FULL.bak<\/span>' with stats, norecovery\r\nGO\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG1.bak<\/span>' with stats, norecovery\r\nGO\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG2.bak<\/span>' with stats, norecovery\r\nGO\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG3.bak<\/span>' with stats, norecovery\r\nGO\r\n\r\n-<\/span>- <\/span>On restaure en standby mode \u00e0 la premi\u00e8re transaction pour d\u00e9terminer si les donn\u00e9es entre 50 et 60 sont toujours l\u00e0<\/span>\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG4.bak<\/span>' with stats,\r\nstandby='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\stdby_files.bak<\/span>', stopbeforemark='lsn:0x00000042:00000021:0001<\/span>'\r\nGO\r\n\r\nselect state_desc, is_in_standby from sys.databases where name='demorecovery'\r\nGO\r\n\r\nstate_desc    is_in_standby\r\n------------- --------------\r\nONLINE\u00a0\u00a0 \u00a0    1\r\n<\/em><\/span>\r\nuse demorecovery\r\nGO\r\nselect * from T1 where a < 60\r\nGO\r\n\r\na\u00a0\u00a0\u00a0\u00a0\u00a0 b\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c\r\n------ ---------------------------- ----\r\n40\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 31\r\n41\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 28\r\n42\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 77\r\n43\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 18\r\n44\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 12\r\n45\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 42\r\n46\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n47\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 46\r\n48\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 39\r\n49\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 74\r\n50\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 45\r\n51\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n52\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 32\r\n53\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 10\r\n54\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n55\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 99\r\n56\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 71\r\n57\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 38\r\n58\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n59\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 41<\/span><\/em>\r\n\r\n-- OK, allons voir \u00e0 la transaction suivante<\/span>\r\nuse master\r\nGO\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG4.bak<\/span>' with stats,\r\nstandby='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\stdby_files.bak<\/span>', stopbeforemark='lsn:0x00000042:00000076:0001<\/span>'\r\nGO\r\n\r\nuse demorecovery\r\nGO\r\nselect * from T1 where a < 60\r\nGO\r\n\r\na\u00a0\u00a0\u00a0\u00a0\u00a0 b\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c\r\n------ ---------------------------- ----\r\n50\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 45\r\n51\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n52\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 32\r\n53\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 10\r\n54\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n55\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 99\r\n56\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 71\r\n57\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 38\r\n58\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n59\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 41<\/em><\/span>\r\n\r\n-- On dirait bien qu'on est dans l'\u00e9tat attendu. Allons voir \u00e0 la derni\u00e8re transaction pour confirmer:<\/span>\r\nuse master\r\nGO\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG4.bak<\/span>' with stats,\r\nstandby='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\stdby_files.bak<\/span>'\r\nGO\r\n\r\nuse demorecovery\r\nGO\r\nselect * from T1 where a < 60\r\nGO\r\n\r\na\u00a0\u00a0\u00a0\u00a0\u00a0 b\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c\r\n------ ---------------------------- ----<\/em><\/span>\r\n\r\n-- Ouaip, donc il faut revenir au point pr\u00e9c\u00e9dent, en rechargeant tout depuis le backup FULL:<\/span>\r\nuse master\r\nGO\r\nrestore database demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_FULL.bak<\/span>' with stats, norecovery\r\nGO\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG1.bak<\/span>' with stats, norecovery\r\nGO\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG2.bak<\/span>' with stats, norecovery\r\nGO\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG3.bak<\/span>' with stats, norecovery\r\nGO\r\n\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_LOG4.bak<\/span>' with stats,\r\nstopbeforemark='lsn:0x00000042:00000076:0001<\/span>', recovery\r\nGO\r\n\r\nuse demorecovery\r\nGO\r\nselect * from T1 where a < 60\r\nGO\r\n\r\na\u00a0\u00a0\u00a0\u00a0\u00a0 b\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c\r\n------ ---------------------------- ----\r\n50\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 45\r\n51\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n52\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 32\r\n53\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 10\r\n54\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n55\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 99\r\n56\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 71\r\n57\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 38\r\n58\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2\r\n59\u00a0\u00a0\u00a0\u00a0 bbbbbbbbbbbbbbbbbbbbbbbbbbbb 41<\/em><\/span>\r\n\r\n-- Note: la transaction suivante (a>990) est perdue. On ne peut pas la recharger car il y aurait une cassure dans la cha\u00eene de LSNs.\r\n-- Si on avait tent\u00e9 de restaurer le tail-log \u00e0 la suite:<\/span>\r\nrestore log demorecovery from disk='C:\\UTRECHT\\MSSQL.1\\MSSQL\\Backup\\demorecovery_TAILLOG.bak<\/span>' with stats, recovery\r\nGO\r\n\r\nMsg\u00a04305, Niveau\u00a016, \u00c9tat\u00a01, Ligne\u00a01\r\nLe journal dans ce jeu de sauvegarde commence au num\u00e9ro de s\u00e9quence d'enregistrement 66000000017600001, ce qui est trop r\u00e9cent\r\npour une application \u00e0 la base de donn\u00e9es. Une sauvegarde de fichier journal ant\u00e9rieure qui inclut le num\u00e9ro de s\u00e9quence\r\nd'enregistrement 66000000009200001 peut \u00eatre restaur\u00e9e.\r\nMsg\u00a03013, Niveau\u00a016, \u00c9tat\u00a01, Ligne\u00a01\r\nRESTORE LOG s'est termin\u00e9 anormalement.<\/span><\/em>\r\n\r\n<\/span><\/pre>\n
Donc voil\u00e0 enfin une m\u00e9thode pour r\u00e9cup\u00e9rer une base pile \u00e0 l’endroit o\u00f9 on veut la r\u00e9cup\u00e9rer, encore une fois gr\u00e2ce \u00e0 une fonction non document\u00e9e qui m\u00e9riterait d’\u00eatre plus connue.<\/p>\n
A+. David B.<\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
Point-in-time recovery d\u00e9signe une restauration de base de donn\u00e9es consistante \u00e0 un point pr\u00e9cis soit dans le temps (STOPAT), soit dans une s\u00e9quence de transactions (STOPATMARK, STOPBEFOREMARK). On ne peut faire du PIT recovery que lorsque la base est en… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":7907,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[93,10],"class_list":["post-2871","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver","tag-backup","tag-journal-de-transaction"],"yoast_head":"\nPoint-in-time recovery et fn_dump_dblog() - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Point-in-time recovery et fn_dump_dblog() - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Point-in-time recovery d\u00e9signe une restauration de base de donn\u00e9es consistante \u00e0 un point pr\u00e9cis soit dans le temps (STOPAT), soit dans une s\u00e9quence de transactions (STOPATMARK, STOPBEFOREMARK). On ne peut faire du PIT recovery que lorsque la base est en… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2011-07-13T15:59:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-13T13:34:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2011\/07\/fndblog2.png\" \/>\n\t<meta property=\"og:image:width\" content=\"654\" \/>\n\t<meta property=\"og:image:height\" content=\"474\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"David Baffaleuf\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"David Baffaleuf\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/\"},\"author\":{\"name\":\"David Baffaleuf\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\"},\"headline\":\"Point-in-time recovery et fn_dump_dblog()\",\"datePublished\":\"2011-07-13T15:59:05+00:00\",\"dateModified\":\"2019-09-13T13:34:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/\"},\"wordCount\":537,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"backup\",\"journal de transaction\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/\",\"name\":\"Point-in-time recovery et fn_dump_dblog() - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2011-07-13T15:59:05+00:00\",\"dateModified\":\"2019-09-13T13:34:59+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Point-in-time recovery et fn_dump_dblog()\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\",\"name\":\"David Baffaleuf\",\"sameAs\":[\"http:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Point-in-time recovery et fn_dump_dblog() - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/","og_locale":"fr_FR","og_type":"article","og_title":"Point-in-time recovery et fn_dump_dblog() - Capdata TECH BLOG","og_description":"Point-in-time recovery d\u00e9signe une restauration de base de donn\u00e9es consistante \u00e0 un point pr\u00e9cis soit dans le temps (STOPAT), soit dans une s\u00e9quence de transactions (STOPATMARK, STOPBEFOREMARK). On ne peut faire du PIT recovery que lorsque la base est en… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2011-07-13T15:59:05+00:00","article_modified_time":"2019-09-13T13:34:59+00:00","og_image":[{"width":654,"height":474,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2011\/07\/fndblog2.png","type":"image\/png"}],"author":"David Baffaleuf","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"David Baffaleuf","Dur\u00e9e de lecture estim\u00e9e":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/"},"author":{"name":"David Baffaleuf","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf"},"headline":"Point-in-time recovery et fn_dump_dblog()","datePublished":"2011-07-13T15:59:05+00:00","dateModified":"2019-09-13T13:34:59+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/"},"wordCount":537,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["backup","journal de transaction"],"articleSection":["SQL Server"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/","url":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/","name":"Point-in-time recovery et fn_dump_dblog() - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2011-07-13T15:59:05+00:00","dateModified":"2019-09-13T13:34:59+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/point-in-time-recovery-et-fn_dump_dblog\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Point-in-time recovery et fn_dump_dblog()"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf","name":"David Baffaleuf","sameAs":["http:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/2871","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=2871"}],"version-history":[{"count":65,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/2871\/revisions"}],"predecessor-version":[{"id":3014,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/2871\/revisions\/3014"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7907"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=2871"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=2871"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=2871"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
En lisant le param\u00e8tre @devtype<\/em> j’avale mon sandwich indien poulet-curry de travers. \u00c7a fait dix mois que je bosse sur un provider OLEDB qui permette de lire dans un backup full (un peu l’\u00e9quivalent de l’Archive Database Access <\/a>sous Sybase ASE), et l\u00e0 je d\u00e9couvre qu’il existe une fonction qui fait la m\u00eame chose sur les backups log. Depuis la 2005 RTM. Argl.<\/p>\n