Spent 2 days for Disaster Recovery

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

Type of backups in SQL Server

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

Recovery Models in SQL Server

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

fn_dblog( ) function documentation

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 select * 
 from OpenRowset (DBLog, @start, @end, NULL, 1, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL )

-- get table result definition.
sp_help 'sys.fn_dblog';

Result.

Column_name	Type	Length	Prec	Scale	Nullable
Current LSN	nvarchar	46			no
Operation	nvarchar	62			no
Context	nvarchar	62			no
Transaction ID	nvarchar	28			no
LogBlockGeneration	bigint	8	19	0	no
Tag Bits	binary	2			no
Log Record Fixed Length	smallint	2	5	0	no
Log Record Length	smallint	2	5	0	no
Previous LSN	nvarchar	46			no
Flag Bits	binary	2			no
Log Reserve	int	4	10	0	no
AllocUnitId	bigint	8	19	0	yes
AllocUnitName	nvarchar	774			yes
Page ID	nvarchar	28			yes
Slot ID	int	4	10	0	yes
Previous Page LSN	nvarchar	46			yes
PartitionId	bigint	8	19	0	yes
RowFlags	smallint	2	5	0	yes
Num Elements	smallint	2	5	0	yes
Offset in Row	smallint	2	5	0	yes
Modify Size	smallint	2	5	0	yes
Checkpoint Begin	nvarchar	48			yes
CHKPT Begin DB Version	smallint	2	5	0	yes
Max XDESID	nvarchar	28			yes
Num Transactions	smallint	2	5	0	yes
Checkpoint End	nvarchar	48			yes
CHKPT End DB Version	smallint	2	5	0	yes
Minimum LSN	nvarchar	46			yes
Dirty Pages	int	4	10	0	yes
Oldest Replicated Begin LSN	nvarchar	46			yes
Next Replicated End LSN	nvarchar	46			yes
Last Distributed Backup End LSN	nvarchar	46			yes
Last Distributed End LSN	nvarchar	46			yes
Server UID	int	4	10	0	yes
SPID	int	4	10	0	yes
Beginlog Status	binary	4			yes
Xact Type	int	4	10	0	yes
Begin Time	nvarchar	48			yes
Transaction Name	nvarchar	66			yes
Transaction SID	varbinary	85			yes
Xact ID	bigint	8	19	0	yes
Xact Node ID	int	4	10	0	yes
Xact Node Local ID	int	4	10	0	yes
End Time	nvarchar	48			yes
Transaction Begin	nvarchar	46			yes
Replicated Records	int	4	10	0	yes
Oldest Active LSN	nvarchar	46			yes
Server Name	nvarchar	258			yes
Database Name	nvarchar	258			yes
Mark Name	nvarchar	66			yes
Master XDESID	nvarchar	28			yes
Master DBID	int	4	10	0	yes
Preplog Begin LSN	nvarchar	46			yes
Prepare Time	nvarchar	48			yes
Virtual Clock	bigint	8	19	0	yes
Previous Savepoint	nvarchar	46			yes
Savepoint Name	nvarchar	66			yes
Rowbits First Bit	smallint	2	5	0	yes
Rowbits Bit Count	smallint	2	5	0	yes
Rowbits Bit Value	binary	1			yes
Number of Locks	smallint	2	5	0	yes
Lock Information	nvarchar	512			yes
LSN before writes	nvarchar	46			yes
Pages Written	smallint	2	5	0	yes
Data Pages Delta	int	4	10	0	yes
Reserved Pages Delta	int	4	10	0	yes
Used Pages Delta	int	4	10	0	yes
Data Rows Delta	bigint	8	19	0	yes
Command Type	int	4	10	0	yes
Publication ID	int	4	10	0	yes
Article ID	int	4	10	0	yes
Partial Status	int	4	10	0	yes
Command	nvarchar	52			yes
Byte Offset	smallint	2	5	0	yes
New Value	binary	1			yes
Old Value	binary	1			yes
New Split Page	nvarchar	28			yes
Rows Deleted	smallint	2	5	0	yes
Bytes Freed	smallint	2	5	0	yes
CI Table Id	int	4	10	0	yes
CI Index Id	smallint	2	5	0	yes
NewAllocUnitId	bigint	8	19	0	yes
FileGroup ID	smallint	2	5	0	yes
Meta Status	binary	4			yes
File Status	binary	4			yes
File ID	smallint	2	5	0	yes
Physical Name	nvarchar	522			yes
Logical Name	nvarchar	258			yes
Format LSN	nvarchar	46			yes
RowsetId	bigint	8	19	0	yes
TextPtr	binary	16			yes
Column Offset	int	4	10	0	yes
Flags	int	4	10	0	yes
Text Size	bigint	8	19	0	yes
Offset	bigint	8	19	0	yes
Old Size	bigint	8	19	0	yes
New Size	bigint	8	19	0	yes
Description	nvarchar	512			no
Bulk allocated extent count	int	4	10	0	yes
Bulk RowsetId	bigint	8	19	0	yes
Bulk AllocUnitId	bigint	8	19	0	yes
Bulk allocation first IAM Page ID	nvarchar	28			yes
Bulk allocated extent ids	nvarchar	1922			yes
RowLog Contents 0	varbinary	8000			yes
RowLog Contents 1	varbinary	8000			yes
RowLog Contents 2	varbinary	8000			yes
RowLog Contents 3	varbinary	8000			yes
RowLog Contents 4	varbinary	8000			yes
Compression Log Type	smallint	2	5	0	yes
Compression Info	varbinary	8000			yes
PageFormat PageType	smallint	2	5	0	yes
PageFormat PageFlags	smallint	2	5	0	yes
PageFormat PageLevel	smallint	2	5	0	yes
PageFormat PageStat	smallint	2	5	0	yes
PageFormat FormatOption	smallint	2	5	0	yes
Log Record	varbinary	8000			no

As you can see there're  many columns the  fn_dblog() returns.
We won't likely need all of them.

--An example how to use sys.fn_dblog function
SELECT
  [Current LSN], [Previous LSN], Operation, Context,
  [Transaction ID], [Transaction Name], [Transaction SID],
  [Transaction Begin], [Begin Time], [End Time],
  [Log Record], [Minimum LSN], 
  [Checkpoint Begin], [Checkpoint End], Description,
  [Page ID], [Dirty Pages] AS dirty_page_number,
  [New Split Page], [Pages Written], [Used Pages Delta],
  [Lock Information], [Number of Locks]
FROM sys.fn_dblog(NULL, NULL);

Paul Randal has a blog post about fn_dblog() and provide some detailed example. You can read it here. 

Credits:
http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/#comment-7872
http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx
http://stevestedman.com/tag/fn_dblog

My coworker is leaving us today.

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.

SQL Server Transaction Log

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 database.
To view the log records, try these (they're not document on  BOL)
DBCC LOG('AdventureWorks',3);
USE AdventureWorks;
go
SELECT * FROM sys.fn_dblog(null,null); --see Paul Randal blog here

* Log records are kept in sequence order in the transaction log; thus there's no performance gain if creating multiple transaction log files per database.
* Each physical log file is divided into multiple uneven virtual log files (VLFs). Think this as a hard drive has multiple partitions.
* VLF states (Brad McGehee sums it up nicely here)
    > Unused: no log record is written to the VLF, and VLF is zeroed out
    > Active: when 1 or more active log record exists in the VLF.
    >  Recoverable: No active log record exists but it's not available for SQL Server to use.VLF is not truncated.
    > Reusable: No active log record exists in the VLF and the VLF is truncated.

* Transaction log can include these: only one logical log, one or more truncated VLF(s), and one or more unused VLF(s).
* Transaction logs are circular. Think this as a race track. This means when logical log reaches end of the physical log file, the new log records are written to the beginning of the physical file. (read more here)
* Transaction logs are truncated after a log backup. Log truncation is not the same as log shrink. After a log truncation the size of the physical log file doesn't get any smaller.
* To reduce the physical log file size, shrink the log by use DBCC SHRINKFILE(). Ex: DBCC SHRINKFILE (AdventureWorks_Tlog, 10); -- Shrink a db's transaction log file to 10 Mb in size.
* Transaction Log truncation:
    Under full and bulk-logged recovery model, VLF is truncated if a checkpoint occurred since the previous backup and after a transaction log backup. Under simple recovery model, log truncation occurs after a checkpoint. (read more here)
* missing any?

Insights:
  1. SQL Server transaction log and database recovery model are linked together.
  2. Good practice to perform regular log backup or the transaction log file will grow very large.
  3. Log backup does not shrink the transaction log file. It's only deallocate the VLFs for later user.
  4. last but not least, understand SQL Server transaction log is one of the key to be an exception DBA. This topic is complex and fun. 

Credits:
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
http://msdn.microsoft.com/en-us/library/jj835093.aspx
http://msdn.microsoft.com/en-us/library/ms190411.aspx
https://docs.google.com/viewer?a=v&q=cache:kj_IFj-I2NgJ:www.bradmcgehee.com/wp-content/uploads/presentations/St%2520Louis_Inside%2520the%2520SQL%2520Server%2520Transaction%2520Log.pdf+&hl=en&gl=us&pid=bl&srcid=ADGEESh64yODlanM9ljyhctpdPBKxGQbITq_3syZSOAQ5HmL9wp53AB_z5sP1C49eRbRDOekzJUQ7JoaOUKEMnnX5Tf0nbdTKdAXt5m-5h00iyz-0-JLebfw3zVbkbxNR7Jqz2BMS_Wz&sig=AHIEtbTH3gOfncEzWzTJlo0kuAAvivqOdg
http://msdn.microsoft.com/en-us/library/ms189573.aspx
http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/is-it-possible-to-run-out-of-log-sequence-numbers-137120

Backup & Restore Basics series

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 "back and restore."