If you take log backups and you changed from summer time the last Sunday of October, your backup log chain might be broken The last weekend of October is a common date for the daylight savings (or summer time) to get to its end in the north hemisphere. <\/p>\n
According to Wikipedia:<\/p>\n
Daylight saving time (DST) or summer time is the practice of advancing clocks during summer months by one hour so that in the evening daylight is experienced an hour longer, while sacrificing normal sunrise times. Typically, users in regions with summer time adjust clocks forward one hour close to the start of spring and adjust them backward in the autumn to standard time.<\/em><\/p>\n So last Sunday we moved the clock backwards one hour at 3am, so we have our clocks pointing 2am twice.<\/p>\n And how that affects me as DBA? Well, long story short… Your backup log chain can be broken, but please keep reading to see why.<\/p>\n SQL Server in order to make transactions durable and recoverable in the event of a crash uses the transaction log, which is one of the database files and any database in SQL Server must have one.<\/p>\n This file contains a description of any change that has happened to the database, that allows transactions to be rolled back, Availability Group replicas and Mirror databases to be synchronized and so on. <\/p>\n Each record within the log file has its own LSN (log sequence number) which is an ever increasing identifier, that way each change is perfectly identified in the order that it occurred.<\/p>\n When your databases use the full or bulk logged recovery model, you have to take care of the transaction log by doing often enough transaction log backups which will [eventually] truncate the log file and allow you to restoring the database to a specific point in time.<\/p>\n Restoring to a point is time is the reason why most of the shops out there use the full recovery model for their databases and DBA’s run SQL Agent jobs or Maintenance plans to perform transaction log backups periodically. <\/p>\n According to BOL<\/a>, BACKUP LOG does:<\/p>\n Under the full recovery model or bulk-logged recovery model, backs up the transaction log of the database to create a log backup<\/em><\/p>\n Depending on your RPO<\/a> those transaction log backups happen more o less frequent, a common pattern can be every hour.<\/p>\n One of the many arguments for the BACKUP T-SQL<\/a> statement is [INIT|NOINIT] which \u00abControls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).\u00bb<\/em>.<\/p>\n That’s it if you take your backups specifying WITH INIT<\/strong> the backup media will be overwritten in case it does exist. <\/p>\n So if you have taken a backup at 2am using the pattern DBName_yyyymmdd_hhmm.trn WITH INIT, and you do it twice because of the DST, the second backup will overwrite the first and break the log chain<\/a><\/p>\n Let’s see how it happens.<\/p>\n You can see how all transactions after the 1am LOG BACKUP are lost and nothing can be done except taking a new full backup to restart the chain, not really nice situation to be.<\/p>\n If instead of using INIT, we use NOINIT, the behavior is different as it will not overwrite the media<\/p>\n Our backup log chain is intact and any point in time restore is possible.<\/p>\n Just for clarification, the backup media generated twice at 2am contains 2 different backups, which can be seen by doing RESTORE HEADERONLY and can be restored individually one after another.<\/p>\n Just a bit of wrap up, further reading for the concepts involved:<\/p>\n If you take log backups and you changed from summer time the last Sunday of October, your backup log chain might be broken The last weekend of October is a common date for…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[18,13,12],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/86"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=86"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/86\/revisions"}],"predecessor-version":[{"id":355,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/86\/revisions\/355"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=86"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=86"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=86"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nTransaction Log Architecture<\/strong>
\n <\/p>\n
\nBACKUP LOG<\/strong>
\n <\/p>\n\r\nIF DB_ID('LogChain') IS NOT NULL DROP DATABASE [LogChain]\r\nGO\r\n \r\nCREATE DATABASE [LogChain]\r\nGO\r\n \r\nALTER DATABASE [LogChain] SET RECOVERY FULL\r\nGO\r\n \r\n-- Full backup taken on October 25th at midnight \r\nBACKUP DATABASE [LogChain] TO DISK = 'LogChain_Full_backup_INIT_20151025.bak' WITH INIT\r\nGO\r\n \r\n-- Log backup taken on October 25th at 1am\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_INIT_20151025_010000.trn' WITH INIT\r\nGO\r\n \r\n-- Log backup taken on October 25th at 2am\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_INIT_20151025_020000.trn' WITH INIT\r\nGO\r\n \r\n-----------------------------------------------------------------------------------\r\n-- DST Applies here, so after 02:59, it is again 02:00\r\n-----------------------------------------------------------------------------------\r\n \r\n-- Log backup taken on October 25th at 2am (second time)\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_INIT_20151025_020000.trn' WITH INIT\r\nGO\r\n \r\n-- Log backup taken on October 25th at 3am\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_INIT_20151025_030000.trn' WITH INIT\r\nGO\r\n \r\n-- Log backup taken on October 25th at 4am\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_INIT_20151025_040000.trn' WITH INIT\r\nGO\r\n \r\n-----------------------------------------------------------------------------------\r\n-- Now we have to restore to some point between 3am and 4am, say 03:30\r\n-----------------------------------------------------------------------------------\r\n \r\n-- Full Backup\r\nRESTORE DATABASE [LogChain]\r\n FROM DISK = 'LogChain_Full_backup_INIT_20151025.bak'\r\n WITH REPLACE, NORECOVERY\r\nGO\r\n \r\n-- Transaction Log backup (1am)\r\nRESTORE LOG [LogChain]\r\n FROM DISK = 'LogChain_Log_backup_INIT_20151025_010000.trn'\r\n WITH NORECOVERY\r\nGO\r\n \r\n-- Transaction Log backup (2am)\r\nRESTORE LOG [LogChain]\r\n FROM DISK = 'LogChain_Log_backup_INIT_20151025_020000.trn'\r\n WITH NORECOVERY\r\nGO\r\n--Msg 4305, Level 16, State 1, Line 48\r\n--The log in this backup set begins at LSN 37000000020800001, which is too recent to apply to the database. An earlier log backup that includes LSN 37000000019200001 can be restored.\r\n--Msg 3013, Level 16, State 1, Line 48\r\n--RESTORE DATABASE is terminating abnormally.\r\n \r\nRESTORE HEADERONLY FROM DISK = 'LogChain_Log_backup_INIT_20151025_020000.trn'\r\n-- Just one line, the first log backup is lost \r\n \r\n-- Transaction Log backup (3am)\r\nRESTORE LOG [LogChain]\r\n FROM DISK = 'LogChain_Log_backup_INIT_20151025_030000.trn'\r\n WITH NORECOVERY\r\nGO\r\n \r\n-- Transaction Log backup (4am)\r\nRESTORE LOG [LogChain]\r\n FROM DISK = 'LogChain_Log_backup_INIT_20151025_040000.trn'\r\n WITH NORECOVERY, STOPAT = '20151025 03:30:00'\r\nGO\r\n<\/pre>\n\r\nIF DB_ID('LogChain') IS NOT NULL DROP DATABASE [LogChain]\r\nGO\r\n \r\nCREATE DATABASE [LogChain]\r\nGO\r\n \r\nALTER DATABASE [LogChain] SET RECOVERY FULL\r\nGO\r\n \r\n-- Full backup taken on October 25th at midnight \r\nBACKUP DATABASE [LogChain] TO DISK = 'LogChain_Full_backup_NOINIT_20151025.bak' WITH NOINIT\r\nGO\r\n \r\n-- Log backup taken on October 25th at 1am\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_NOINIT_20151025_010000.trn' WITH NOINIT\r\nGO\r\n \r\n-- Log backup taken on October 25th at 2am\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_NOINIT_20151025_020000.trn' WITH NOINIT\r\nGO\r\n \r\n-----------------------------------------------------------------------------------\r\n-- DST Applies here, so after 02:59, it is again 02:00\r\n-----------------------------------------------------------------------------------\r\n \r\n-- Log backup taken on October 25th at 2am (second time)\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_NOINIT_20151025_020000.trn' WITH NOINIT\r\nGO\r\n \r\n-- Log backup taken on October 25th at 3am\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_NOINIT_20151025_030000.trn' WITH NOINIT\r\nGO\r\n \r\n-- Log backup taken on October 25th at 4am\r\nBACKUP LOG [LogChain] TO DISK = 'LogChain_Log_backup_NOINIT_20151025_040000.trn' WITH NOINIT\r\nGO\r\n \r\n-----------------------------------------------------------------------------------\r\n-- Now we have to restore to some point between 3am and 4am, say 03:30\r\n-----------------------------------------------------------------------------------\r\n \r\n-- Full Backup\r\nRESTORE DATABASE [LogChain]\r\n FROM DISK = 'LogChain_Full_backup_NOINIT_20151025.bak'\r\n WITH REPLACE, NORECOVERY\r\nGO\r\n \r\n-- Transaction Log backup (1am)\r\nRESTORE LOG [LogChain]\r\n FROM DISK = 'LogChain_Log_backup_NOINIT_20151025_010000.trn'\r\n WITH NORECOVERY\r\nGO\r\n \r\n--=================================================== \r\nRESTORE HEADERONLY FROM DISK = 'LogChain_Log_backup_NOINIT_20151025_020000.trn'\r\n-- We have 2 lines, so the 1st backup can be restored and so can be the 2nd\r\n--===================================================\r\n \r\n-- Transaction Log backup (2am)\r\nRESTORE LOG [LogChain]\r\n FROM DISK = 'LogChain_Log_backup_NOINIT_20151025_020000.trn'\r\n WITH FILE = 1, NORECOVERY\r\nGO\r\n \r\nRESTORE LOG [LogChain]\r\n FROM DISK = 'LogChain_Log_backup_NOINIT_20151025_020000.trn'\r\n WITH FILE = 2, NORECOVERY\r\nGO\r\n \r\n-- Transaction Log backup (3am)\r\nRESTORE LOG [LogChain]\r\n FROM DISK = 'LogChain_Log_backup_NOINIT_20151025_030000.trn'\r\n WITH NORECOVERY\r\nGO\r\n \r\n-- Transaction Log backup (4am)\r\nRESTORE LOG [LogChain]\r\n FROM DISK = 'LogChain_Log_backup_NOINIT_20151025_040000.trn'\r\n WITH NORECOVERY, STOPAT = '20151025 03:30:00'\r\nGO\r\n \r\n \r\n<\/pre>\n
\nUseful Links<\/strong><\/p>\n\n