DB4Breakfast

I just spent 2 days working on updating the documentations for this year disaster recovery. It's fun stuff - writing and practicing recovery.

Goals: Known all available type of backups in SQL Server. This info will be used later for our backup/restore process. As far as I know, these are the available type of backup in SQL Server:  > Full  > Full with Copy-only option  > Differential  > Transaction Log  > Transaction Log with Copy-only option  > Partial  > File  > Filegroup Note: There's no incremental backup in SQL Server. Also, incremental and differential backup are not the same.   I'll cover each type of backup in later posts. Credits: http://msdn.microsoft.com/en-us/library/ms186865(v=sql.105).aspx http://msdn.microsoft.com/en-us/library/ms179401(v=sql.105).aspx https://www.simple-talk.com/sql/backup-and-recovery/partial-backup-and-restore/ http://msdn.microsoft.com/en-us/library/ms191495.aspx

Goal: Know all available recovery models in SQL Server. This will build the foundation for our backup/restore skill. There are 3 recovery models in SQL Server: simple, bulk logged, and full. We'll cover each recovery model later. Credits: http://msdn.microsoft.com/en-us/library/ms189275.aspx

Goal: This post provide some documentation for the fn_dblog() function. Key Points:     * view transaction log of a database (if you want to view the transaction log of a log backup, use fn_dump_dblog function instead.)     * useful for:           > restore database to a certain point in time           > find out when an operation (page split, table delete, table truncate...).    * take 2 parameters, start time and end time.    * It's not recommended use this function to scan the transaction log of a production database because it can reduce performance due to increase disk I/O. -- get fn_dblog() definition sp_helptext 'sys.fn_dblog'; --result  create function sys.fn_dblog  (   @start   nvarchar (25) = NULL,   @end     nvarchar (25) = NULL  )    returns table as  return ...

Today, I'm very sad because my wonderful coworker is leaving our company. We've been working together for almost two years on the ehr project. I don't know what I will do without her. I will miss her a lot and wish the best of luck to her new journey.

Goal: understand SQL Server Transaction Log architecture. This topic is complex, and I'm no expert; that's why I've attached several links from book online and other experts. Before going to details about backup and restore. We must understand the basic of the SQL Server Transaction Log architecture. For more details, please see Paul Randal's post here .  Key Points: * Every database has at least one transaction log (yes, including tempdb) * Every record in a transaction log is identified by a LSN (Log Sequence Number). LSN is unique 10 bytes in size (4 bytes for VLF number + 4 bytes for block number + 2 bytes for log records number) , it's like a time stamp. Ex: 0000002e:0000027a:0001 * Realistically, it's not possible to use all LSN. Kimberly Tripp and Paul Randal provide an excellent example here . Based on the example, it would take 240 million years to generate 4 billion petabytes transaction log. * Log records are the changes made to a data...

I'm not sure that you heard about the 10,000 hours rule. Basically, it would normally take 10,000 hours of practice to master a skill. For database backup and restore, I don't know many hours are needed. Well, it doesn't hurt to go back to the basics. : ) Thus, my next series of blogs is about "backup and restore."