Recovery Models in SQL Server Databases

 "Recovery Model" determines how long the data is to be kept in the transaction logs. It also allows what sort of backups and restores you can perform on the database.

Types of recovery models

There are three types of recovery models in SQL Server

1. Simple
2. Full 
3. Bulk-logged

How can I change the recovery model of a database?

The recovery model of a database can be changed either using the GUI (SSMS) or using a T-SQL statement.

To change using SSMS follow these steps:

1. Right-click the database and choose options

2. Select "Options" from the pages

3. From the Recovery model drop-down list choose the appropriate one.

4. Click OK

To change using T-SQL statement use the following syntax:

      
USE master;
ALTER DATABASE YourDBName SET RECOVERY SIMPLE

You can use options SIMPLE, FULL or BULK_LOGGED

You can find out the recovery model of the databases which resides on your server using the following query.

SELECT 
	[name]
	,DATABASEPROPERTYEX([name],'Recovery') AS recovery_model
FROM
	sys.databases
WHERE
	database_id > 4   

Note: database_id > 4 will ensure that system databases information is excluded.

Now we will look into closely what options it may enable during backup/restore for each type of recovery model.

Simple Recovery Model

• You cannot take backups of the transaction log explicitly
• A database backup will always contain both data and transaction log file

• Transaction log will not grow beyond what's been defined. The space in the transaction log file will be re-used (overwritten) once the transactions are written to the data file.
• In case of a disaster transaction occurred between the last full backup and the disaster timeline cannot be recovered. (E.g: Assume we have a database in which the backups are taken every hour. (8am, 9am etc.) In case a disaster happens at 10:45am, transactions between 10am and 10:45am will be lost)

Bulk-logged Recovery Model

• You can take the backup of the transaction log explicitly
• Log truncation won't happen once the transaction log backup is taken. Hence it needs to be truncated manually (Same as the "Full" recovery model)
• In the bulk-logged recovery model, certain operations are logged minimally. E.g: Bulk import operations such as BCP operations and BULK INSERT, SELECT INTO operations. More details on such operations can be found on the following link: https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15#MinimallyLogged

Full Recovery Model

 

• Supports transaction log backup
• Chances of a data loss are minimal (subject to the backup strategy implemented)
• Log truncation must be done manually (Upon taking the transaction log backup, it will mark unused space which will be overwritten later by future transactions)
• Transaction log could grow large compared to the "Simple" recovery model

The scope of this post is to give a brief idea of the recovery models in SQL Server. In a future post, I will explain how to bring a database to a certain date and time by having a Full Recovery Model and a good backup strategy.

Strange behavior on JSON_VALUE when table contains blank and non-blank values (JSON text is not properly formatted. Unexpected character '.' is found at position 0.)

Few days back we had a requirement to search whether a certain value exists in one of the table fields where the values are stored as JSON strings. The default constructor has been set up not to allow any NULLs but in case there’s no value, the default value has been setup as an empty string.
So basically the query would be similar to something shown below

SELECT 
 'x'
FROM
 [schema].[TableName] AS Src
WHERE
 JSON_VALUE(Src.ColumnName,'$.Root.AttributeName') LIKE 'SearchValue%'

How ever when we ran this query we got the following error

Msg 13609, Level 16, State 2, Line 36
JSON text is not properly formatted. Unexpected character '.' is found at position 0.

Initially we thought that we have typed the attribute incorrectly since it’s case sensitive. But in this case it was correct.

We investigated further and found out few things. But prior explaining them we will replicate this issue. For this I will create one simple table and insert three records.

--== Create a table ==--
CREATE TABLE dbo.Employee_Information (
 Id    INT
 ,FirstName  NVARCHAR(100)
 ,LastName  NVARCHAR(100)
 ,JsonData  NVARCHAR(MAX)
)

--== Insert few rows ==--
INSERT INTO dbo.Employee_Information (
 Id
 ,FirstName
 ,LastName
 ,JsonData
)
VALUES
(1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}')
,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}')
,(3,'Luke','Skywalker','')

Now we will use the following query to find any records which the LastName is like ‘Doe’.

SELECT 
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

Msg 13609, Level 16, State 2, Line 36
JSON text is not properly formatted. Unexpected character '.' is found at position 0.

**Note : The query will return results till the error occurs. Hence you will see some rows in your result tab in SSMS.

These are the observations we made during our investigation

Observation 01

If you query the table with a predicate and if that predicate doesn’t include any rows with blank values in the JSON (it’s an NVARCHAR column) field the query will executed successfully.

--== Success ==--
SELECT 
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
 AND Id IN (1,2)

--== Success ==--
SELECT 
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
 AND Id <> 3

--== Fail ==--
SELECT 
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
 AND Id = 3

Observation 02

Even you use a filter to fetch only rows containing a valid JSON the execution will be successful.

--== Success ==--
SELECT 
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 ISJSON(E.JsonData) > 0
 AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

Observation 03

Even you use a filter to fetch only rows containing a non-blank value in the JSON field, it will fail.

--== Fail ==--
SELECT 
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 E.JsonData <> ''
 AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

Observation 04

If you remove records and only keep either one type of rows (either only blank rows or only non-blank) the query will be executed successfully.

TRUNCATE TABLE dbo.Employee_Information
INSERT INTO dbo.Employee_Information (
 Id
 ,FirstName
 ,LastName
 ,JsonData
)
VALUES
(1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}')
,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}')

--== Success ==--
SELECT 
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

Observation 05

If you have rows only containing blank values in the JSON field the query will fail.

TRUNCATE TABLE dbo.Employee_Information
INSERT INTO dbo.Employee_Information (
 Id
 ,FirstName
 ,LastName
 ,JsonData
)
VALUES
(1,'John','Doe','')
,(2,'Jane','Doe','')


--== Fail ==--
SELECT 
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

Hope this might help you if you encounter this strange behavior during your development.

Note : All the above queries are executed under the following SQL Server Version (SELECT @@VERSION)




Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
     Oct 28 2016 18:17:30
     Copyright (c) Microsoft Corporation
     Developer Edition (64-bit) on Windows Server 2012 Standard 6.2 (Build 9200: ) (Hypervisor)

Applying database principal through out the server (for all databases) for a particular user

Ever come across a requirement which you required to give db_datareader access to a specific user across all the databases on a particular SQL Server. The task is simple as long as you don’t have many databases in the same SQL Server. How ever if the number of databases are very high this can be a very time consuming one.

This can be done either using the GUI (SSMS) or using a T-SQL script. We will consider both options.

Using SQL Server Management Studio

In order to illustrate this we will create a SQL Login ‘db_user_read_only’ with ‘public’ server role and on the user mapping, we will apply the db_datareader principal.

Like mentioned it would be easy to use the GUI when you have less number of databases. But if the SQL Server contains lots of databases this will be a very time consuming job. Then it would be very handy to use the latter approach.

Using T-SQL

You can use the following script to apply the db_datareader principal across all the databases on a particular server.

DECLARE 
	@Sql AS NVARCHAR(MAX)
	,@UserId AS VARCHAR(MAX) = 'YourLoginId'
SET @Sql = CONCAT('
USE [?];
IF EXISTS (SELECT 0 FROM sys.database_principals AS DP WHERE name = ''',@UserId,''')
BEGIN
	EXEC sys.sp_change_users_login ''update_one'',''',@UserId,''',''',@UserId,'''
END
ELSE
	
	CREATE USER [',@UserId,'] FOR LOGIN [',@UserId,']
	ALTER ROLE [db_datareader] ADD MEMBER [',@UserId,']
')
EXEC sys.sp_MSforeachdb 
	@command1 = @Sql
	,@replacechar = '?'

Please note the following:

• On the above code I haven’t excluded the system databases.
• If the login exists on the database it will map the database user using sp_change_users_login

Hope this might be very useful to you.

Replacing sp_depends with sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities

sp_depends have been one of the most used system stored procedures in SQL Server. Infact many of us still use that even though Microsoft had annouced that it will be removed from the future releases.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql?view=sql-server-2017

Alternatively Microsoft has provided two dynamic management views (these have been introduced with SQL Server 2008) in order to get similar kind of information.

• sys.dm_sql_referencing_entities
• sys.dm_sql_referenced_entities

You can get further details on the aforementioned view by visiting the link. (links are embedded into the view name)
However if you have used sp_depends you might have already faced the issue that the results which is being returned from this stored procedure is not very accurate (most of the time it seems fine)
Otherday I was going through these two view in order to create an sp which is similar to sp_depends and thought of sharing the query so that it can be useful to anyone who depends on this sp.

DECLARE
 @objname   AS NVARCHAR(100) = 'Website.SearchForPeople'
 ,@objclass   AS NVARCHAR (60) = 'OBJECT'


  SELECT 
   CONCAT(sch.[name],'.',Obj.[name]) AS [name]
   ,(CASE Obj.type
    WHEN 'C'  THEN 'CHECK constraint'
    WHEN 'D'  THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F'  THEN 'FOREIGN KEY constraint'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R'  THEN 'Rule (old-style, stand-alone)'
    WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
    WHEN 'TR' THEN 'SQL trigger'
    WHEN 'UQ' THEN 'UNIQUE constraint'
    WHEN 'AF' THEN 'Aggregate function (CLR)'
    WHEN 'C' THEN 'CHECK constraint'
    WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F' THEN 'FOREIGN KEY constraint'
    WHEN 'FN' THEN 'SQL scalar function'
    WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
    WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
    WHEN 'IF' THEN 'SQL inline table-valued function'
    WHEN 'IT' THEN 'Internal table'
    WHEN 'P' THEN 'SQL Stored Procedure'
    WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
    WHEN 'PG' THEN 'Plan guide'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R' THEN 'Rule (old-style, stand-alone)'
    WHEN 'RF' THEN 'Replication-filter-procedure'
    WHEN 'S' THEN 'System base TABLE'
    WHEN 'SN' THEN 'Synonym'
    WHEN 'SO' THEN 'Sequence OBJECT'
    WHEN 'U' THEN 'Table (user-defined)'
    WHEN 'V' THEN 'VIEW'
    WHEN 'SQ' THEN 'Service queue'
    WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
    WHEN 'TF' THEN 'SQL table-valued-function'
    WHEN 'TR' THEN 'SQL DML trigger'
    WHEN 'TT' THEN 'Table type'
    WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
    WHEN 'X'  THEN 'Extended stored procedure'
    ELSE 'Undefined'
   END) AS [type]
   ,Obj.create_date
   ,Obj.modify_date
   ,src.referenced_minor_name AS [column]
   ,IIF(src.is_selected   = 1,'yes','no') AS is_selected
   ,IIF(src.is_updated    = 1,'yes','no') AS is_updated
   ,IIF(src.is_select_all = 1,'yes','no') AS is_select_all
   ,IIF(src.is_insert_all = 1,'yes','no') AS is_insert_all
  FROM 
   sys.dm_sql_referenced_entities (@objname,@objclass) AS src
   JOIN sys.objects AS Obj
    ON src.referenced_id = Obj.[object_id]
   JOIN sys.schemas AS Sch
    ON Sch.[schema_id] = Obj.[schema_id]
  WHERE 1=1
  
  SELECT 
   CONCAT(Src.referencing_schema_name,'.',Src.referencing_entity_name) AS [name]
   ,(CASE Obj.type
    WHEN 'C'  THEN 'CHECK constraint'
    WHEN 'D'  THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F'  THEN 'FOREIGN KEY constraint'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R'  THEN 'Rule (old-style, stand-alone)'
    WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
    WHEN 'TR' THEN 'SQL trigger'
    WHEN 'UQ' THEN 'UNIQUE constraint'
    WHEN 'AF' THEN 'Aggregate function (CLR)'
    WHEN 'C' THEN 'CHECK constraint'
    WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F' THEN 'FOREIGN KEY constraint'
    WHEN 'FN' THEN 'SQL scalar function'
    WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
    WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
    WHEN 'IF' THEN 'SQL inline table-valued function'
    WHEN 'IT' THEN 'Internal table'
    WHEN 'P' THEN 'SQL Stored Procedure'
    WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
    WHEN 'PG' THEN 'Plan guide'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R' THEN 'Rule (old-style, stand-alone)'
    WHEN 'RF' THEN 'Replication-filter-procedure'
    WHEN 'S' THEN 'System base TABLE'
    WHEN 'SN' THEN 'Synonym'
    WHEN 'SO' THEN 'Sequence OBJECT'
    WHEN 'U' THEN 'Table (user-defined)'
    WHEN 'V' THEN 'VIEW'
    WHEN 'SQ' THEN 'Service queue'
    WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
    WHEN 'TF' THEN 'SQL table-valued-function'
    WHEN 'TR' THEN 'SQL DML trigger'
    WHEN 'TT' THEN 'Table type'
    WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
    WHEN 'X'  THEN 'Extended stored procedure'
    ELSE 'Undefined'
   END) AS [type]
   ,Obj.create_date
   ,Obj.modify_date
  FROM 
   sys.dm_sql_referencing_entities (@objname,@objclass) AS Src
   JOIN sys.objects AS Obj
    ON Obj.[object_id] = Src.referencing_id 

I have even compiled a stored procedure using this syntax and it can be found on the following reporsitory: https://github.com/manjukefernando/sp_depends_v2