SQL SERVER ADMINISTRATION: FULL, DIFFERENTIAL AND TRANSACTION LOG BACKUP TYPES

In this tutorial, I will go through the different types available for creating Backups, which will be essential for your decisicion on which Backup Strategy to go for.
I will mainly focus on Full, Differential and Transaction Log Backups.

FULL DATABASE BACKUPS
This backup type will include all objects (views, procedures, functions...), the tables including the data, users and rights. 
A Full Backup enables you to restore the database as it was at the time the Backup was created. Transactions occuring during the Backup process will also be captured.

DIFFERENTIAL BACKUPS
Differential Backups will capture the data that has been altered since the last Full Backup. So when restoring, you will need the Full + Differential Backup files.
An advantage is that Differential Backups are faster than Full Backups (could be a huge difference depending on the size of the Database).

TRANSACTION LOG BACKUPS
Transactions Log Backups will capture all changes that occured since the last transaction log backup. Within this process, a cleaning of the transaction logs happens (a removal of transactions that have been comitted or cancelled).
This Backup Type captures data up to the time the Backup Process was started, whereas Full and Differential Backups also capture transactions that are altered during the backup process.

FILE AND FILEGROUP BACKUPS
This type is usually used for large Databases. Basically specific Files and Filegroups are backed up.

COPY-ONLY BACKUPS
Copy-Only Backups will deliver the same results as a Full Database Backup or Transaction Log backups. The reason for using a Copy-Only Backup is that this Backup type will be ignored, meaning a Copy-Only Backup initiated between a Full and Transactional Backup will not affect the timestamp of the last Full Backup.

You can create a Backup either manually or with T-SQL Command.
If you decide to do it manually, right click on any Database -> Tasks -> Back Up

T-SQL:

--Full Backup
BACKUP DATABASE Databasename TO [YourPath]

--Transactional Backup
BACKUP LOG Databasename TO [YourPath]

--Differential Backup
BACKUP DATABASE Databasename TO [YourPath] WITH DIFFERENTIAL

Select the Database, the Backup Type and Destination

To get a better understanding of the different Backup Types, let's have a look at the table below.
At the time I created the Backup, it appears to be empty:

  

I inserted 1 country (Sweden) into the table and created a Differential and a Transaction Log Backup.

Finally I inserted another row by adding Netherlands to the table and created another Transaction Log Backup.

RESTORING BACKUPS

Now that we have created our Backups, will need to restore them. Right click on the Destination Database -> Tasks -> Restore -> Database

On the General tab, select device and click on the ... button to select location of the stored Backup Files.
You will also need to select a Destination Database.
On the Files tab, ensure your files are pointing to the right folder (and not of the original source)

On the Options tab, select Overwrite and RESTORE WITH RECOVERY.










To restore a FULL Backup you need to select the Full Backup File:

To restore a DIFFERENTIAL Backup you need to select the Full Backup File  + Differential Backup files:

 










To restore a TRANSACTION LOG Backup you need to select the Full Backup File  + Transaction Log files:

As you can see in the above picture you have the option to restore to any time where an an Transaction Log Backup was created.
To get a better picture I will summarize the 3 Backup Types in relation to entries in the Country table.

SQL SERVER ADMINISTRATION: EXTRACT SHOWPLAN RESULTS FROM SQL SERVER PROFILER FOR FUTHER ANALYSIS

The Estimated or Actual Execution Plan provided by SQL Server gives a nice graphical overview, but can be hard to read if you have too many objects and you will also need to point your cursor on each object for further information. There is also an option to extract the Execution Plan in XML, but that requires additonal steps to make it readable. In this tutorial I will show an alternative way with SQL Server Profiler that will provide detailed execution plan information in text form that can be easily reused for further analysis.

 Once you've open SQL Server Profiler click on the File tab and then on New Trace.

SQL Server Profiler returns massive information. To narrow down the results and to focus on our main goal, some settings are essential. In the Trace properties click on the Show all events tab and expand the Performance section to capture the Showplan data.

Under Performacnce you will see all types of Showplan information. The one most relevant for our excercise here is Showplan All.

To narrow down the results even more, click on Column Filters in the Event Selections Tab. Depending on column and operator you can basically filter on any available column with an exact serch term or use wildcards. I will filter on my login to avoid getting system admin records returned.

Meanwhile I executed my query from SSMS and SQL Server Profiler immediately returned following records:

Showplan All returns the execution plan and in addition detailed information such as what type of join operator is used, CPU costs, nr of executions etc. Simply mark the relevant text and right click copy.

You can save this info as a txt file or simply paste it in Excel. Once you have it in the right format, the whole thing is very easy to read and you can sort descending by the most expensive objects to lead you straight to the root cause.

T-SQL: SQL Server Transaction Isolation Levels

There are 4 attributes ensured to a transaction (ACID):

A: Atomicity - In a transaction either all included elements will get committed or none of them
C: Consistency - Data integrity is ensured, meaning if any failure occurs during the transaction, the previous state at begin of the transaction will be kept.
I: Isolation - A transaction in process may not be affected by other transactions.
D: Durability - Committed data is saved by the system.

There are 4 different isolation levels. A sample with SQL code for each type will be provided.

I will use the following Artist table as a basis for my samples:

SELECT [ArtistID]
               ,[Artist]
FROM [Tutorials].[stg].[Artist]SELECT [ArtistID]

ArtistID    Artist
----------- ------------
1           00Agents
2           06 Style
3           10°
4           1000 Ohm
.......

1) READ UNCOMMITTED
This isolation level allows to read changed data from other not closed transactions, which may later be rolled back.

Transaction 1:
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED

SELECT [ArtistID]
              ,[Artist]
FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

The query delivers the following result:
ArtistID    Artist
----------- -----------
1           00Agents

(1 row(s) affected)

Now I will run the following transaction, but won't commit the transaction:

Transaction 2:
BEGIN TRANSACTION

INSERT INTO [Tutorials].[stg].[Artist]
VALUES
('00Agents')

If I run the first query again, I will now also see the inserted value from above (not committed transaction)

Transaction 1:
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED

SELECT [ArtistID]
              ,[Artist]
FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

ArtistID    Artist
----------- -----------
1           00Agents
38         00Agents

(2 row(s) affected)

Instead of committing I will now rollback the transaction. Now we see the original result again.

----------- -----------
1           00Agents

(1 row(s) affected)

This scenario where uncomitted transactions can be captured, is often referred to as a "Dirty Read".


2) READ COMMITTED
This is the standard default value in SQL Server.
Here we are able to only see committed transactions:

Assuming I'm inserting values in a table, and I open a second transaction where I want to read the table, the following will happen.

Here we insert the values (but do not commit right away for demonstration reasons):

Transaction 1:
BEGIN TRANSACTION

INSERT INTO [Tutorials].[stg].[Artist]
VALUES
('00Agents')


Now we want to select values from that same table:


Transaction 2:
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

SELECT [ArtistID]
              ,[Artist]
FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

Transaction 2 will deliver no result until Transaction 1 is finished.

Once I commit the transaction with the insert values, the select query (Transaction 2) shows results:

Transaction 2:
--BEGIN TRANSACTION

--INSERT INTO [Tutorials].[stg].[Artist]
--VALUES
--('00Agents')

COMMIT TRANSACTION

ArtistID    Artist
----------- ----------
1            00Agents
39          00Agents

(2 row(s) affected)


3) REPEATABLE READ
This isolation level gurantees that data read in a transaction will deliver the same result set later in that transaction. In the sample below I will have a SELECT query, add a waitfor delay and will start a second session in that break that will update values in the underlying table and then after the break run the same SELECT statement in Session 1 to see if the changes happend.

Transaction 1
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ

BEGIN TRANSACTION

SELECT [ArtistID]
              ,[Artist]

FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

WAITFOR DELAY '00:00:05'

SELECT [ArtistID]
              ,[Artist]

FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

COMMIT TRANSACTION

 Transaction 2:

BEGIN TRANSACTION

UPDATE [Tutorials].[stg].[Artist]
SET Artist = '01Agents'
WHERE ArtistID = 1

COMMIT TRANSACTION


Although we have an update between the 2 queries, the result is the same.


ArtistID    Artist
----------- ----------
1            00Agents
39          00Agents

(2 row(s) affected)

ArtistID    Artist
----------- ----------

1            00Agents
39          00Agents

(2 row(s) affected)The reason why the change happens after Transaction 1 is finished, is due to a share lock that is kept until the transaction with Isolation Level REPEATABLE READ is finished.

4) SERIALIZABLE
This  Isolation Level is quite comparable with REPEATABLE READ. The main difference is that Isolation Level SERIALIZABLE wouldn't allow inserted rows. Updates and Deletes are locked with REPEATABLE READS, but inserts are technically possible - but not with Isolation Level SERIALIZABLE. This scenario is also known as a "Phantom Read".

SQL SERVER ADMINISTRATION: REORGANIZE OR REBUILD CLUSTERED AND NON-CLUSTERED INDEXES

In this blog entry, I would like to describe how to reorganize or rebuild indexes.

First of all, SQL server stores row or index data on 8KB sized memory Pages. For each table (no matter how big), a own page is created. If the table exceeds a certain size due to manipulation of data (e.g. new row inserts), the page will be extended („Page Split“) to a second page etc.

 

The new page will also cause the creation of a new page in the upper level with the index tree, which may also lead to a page split.

Page Spilts are time consuming, cause fragmentation and index pages aren’t phyically ordered as supposed. All this results to a decrease of performance.

Let's have a brief look on the architecture of indexes in SQL Server.

In a table without a clustered index, the data will be stored within the page unsorted („Heap“).

Indexes have a root page, which is the starting point. The tree is split between non-leaf (root and intermediate leafs) and leaf-levels.

In a clustered index, the leaf level is the actual data page. This means the data is stored here in ordered form.

The most significant difference in the index architecture between clustered and non-clustered, is that the leaf level in non-clustered indexes contain key values and not the actual data. 

In case of a heap, there is a row locator in the leaf node pointing to the correct data page.

A non-clustered index in a table that has a clustered index, will refer to the clustered index for pointing the row location.

Disadvantage here is that non-clustered indexes will have to be rebuild if the clustered index is rebuild or dropped. An advantage here is that a Reorganize of a clustered index, will mean that the non-clustered index does not have to be reorganized.

To solve the above listed issues concerning performance, indexes need to be REORGANIZEd or REBUILD.

The level of fragmentation can be analyzed with the help of system function sys.dm_db_index_physical_stats

Here is a more precise query:
SELECT sch.name  + ' ' + tab.name             AS 'Table',
idx.name                                                       AS 'Index',
stat.avg_fragmentation_in_percent,
stat.page_count,
idx.type_desc                                                AS IndexType
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS stat
INNER JOIN sys.tables  AS tab                   ON tab.object_id = stat.object_id
INNER JOIN sys.schemas AS sch               ON tab.schema_id = sch.schema_id
INNER JOIN sys.indexes AS idx                 ON idx.object_id = stat.object_id
AND stat.index_id = idx.index_id
ORDER BY stat.avg_fragmentation_in_percent DESC
If the average fragmention is between 5 and 30 %, a REORGANIZE is fine, if it's above 30% REBUILD is advised.

Following T-SQL syntax is required for REORGANIZE:

ALTER INDEX indexname

ON tablename REORGANIZE

In case of a rebuild, there is an option for a fill factor. This means the page will be filled with the percentage value defined. The rest is left open to avoid a fast fragmentation.

ALTER INDEX indexname

REBUILD WITH (FILLFACTOR = 80)

The fillfactor by default will only relate to the leaf level of the tree. If you want to also cover the other pages PAD_INDEX = ON will do the job.

ALTER INDEX indexname 
REBUILD WITH (FILLFACTOR = 80, PAD_INDEX = ON)