Identifying Last Full, Differential and Log Backup Time for All Databases in SQL Server

Identifying Last Full, Differential and Log Backup Time for All Databases in SQL Server
In a consistent environment to maintain database backups on a regular basis, there is always a need for manual checks. Obvious to the fact that the alerts for failed backups would be implemented on the server. But, to manually check without logging into the server disk drive, here is an easy way to find the last Full, Differential, and Log backups in a single script.

Single script to check All databases backup times:
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'D' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTFULLBACKUP
--CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'I' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTDIFFBACKUP
--CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'L' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTLOGBACKUP
--CONVERT(VARCHAR(16), MAX(CASE WHEN B.[TYPE] NOT IN ('D','I','L') THEN B.BACKUP_FINISH_DATE END), 120) AS LASTOTHERBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last Full backup time for all databases:
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'D' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTFULLBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last Differential backup time for all databases:
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'I' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTDIFFBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last Log backup time for all databases:
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'L' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTLOGBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last other database backups (apart from Full/Differential/Log) 
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE WHEN B.[TYPE] NOT IN ('D','I','L') THEN B.BACKUP_FINISH_DATE END), 120) AS LASTOTHERBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

Difference Between CURRENT_TIMESTAMP and GETDATE() in SQL Server

Are both CURRENT_TIMESTAMP and GETDATE() the same in SQL Server?

Of course, both CURRENT_TIMESTAMP and the GETDATE() perform a similar functionality, but a lot of time most of the Oracle/MySQL developers feel tricky about it.

Most often while writing any SELECTs or in the Stored Procedures, I often tend to prefer GETDATE() rather than CURRENT_TIMESTAMP. And as far as I have seen, most of the developers and DBAs are much familiar in using the GETDATE().

One Difference between CURRENT_TIMESTAMP and GETDATE()

CURRENT_TIMESTAMP -  CURRENT_TIMESTAMP is an ANSI SQL function, which retrieves the date and time that is set on the system on which SQL instance is running.

SELECT CURRENT_TIMESTAMP









GETDATE() – GETDATE() is the T-SQL genre of the same function, which again retrieves the date and time that is set on the system on which SQL instance is running. 

SELECT GETDATE() 









Further, in case a column in a table is set as CURRENT_TIMESTAMP, then the function gets converted to GETDATE() if the code is generated manually from the SSMS.

Related interview questions to refer:

How to Check Database Usage in SQL Server

How to Check Database Usage in SQL Server
Many a times, database developers and DBAs whom I meet, come up with a common question on how to check the databases in use or not. There are many ways to identity the database usage, but one of the best ways to find I felt was to use the system dmv to monitor the database usage in SQL Server.

Most of the times, in big organizations, a lot of databases will be created as part of request. Some of them can probably turn out to be a junk in the server. In case you come across any such instances to clean up the old databases that are not in use from quite long time, then simply use the dmv command as shown:

Select DBName,
MAX(LastAccessDate) DatabaseLastAccessedOn From (
Select DB_NAME(database_id) DBName,
last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update
FROM sys.dm_db_index_usage_stats where DB_NAME(database_id) not in ('Tempdb','msdb','model','system')
) AS Pvt
UNPIVOT
(LastAccessDate FOR last_user_access IN
(last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update)
) AS Unpvt
GROUP BY DBName
Order by 1

From the above SELECT command, the query checks for the last_user_seek, last_user_scan, last_user_lookup and last_userupdate for the respective databases in the server and produces the result.

Note: Use the code in jobs to schedule at different times to dump in a user table and select the distinct unused databases to get the best results. 

You may also need: 

How to Set a Database to Read-Only in SQL Server

I was having a discussion with one of my colleague regarding the database setting to Read-Only mode. This happened to Read-Only as part of the industry requirement for the databases, since there shouldn’t be any change in the data for the respective database.

Let’s check on how to set a database to Read-Only mode in SQL Server.

Below is the T-SQL script to change a database to Read-Only mode: 

USE [master]
GO
ALTER DATABASE [Arena3] SET READ_ONLY WITH NO_WAIT
GO



Below is the T-SQL script to change a database to Read/Write Mode:

USE [master]
GO
ALTER DATABASE [Arena3] SET READ_WRITE WITH NO_WAIT
GO

In case you would like to rewind the changed database modes from Read-Only access then simply use the command as:

ALTER DATABASE <database_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Note:  Please try not to use these commands on your production databases, which can probably have adverse effects for scheduled jobs and client update requests. 

How to Find SQL ERRORLOG Location in SQL Server

Most of the developers and DBAs working on SQL Server would probably have knowledge on how to find SQL ERRORLOG location. As the name says, the ERRORLOG is the place where all the error encountered in the SQL Server would be stored. Probably, the ERRORLOG comes in use when there is a need to check the issue in certain timeframe in SQL Server instance. The ERRORLOG is generated every time a new instance is created in SQL Server.

Let’s check how to find the ERRORLOG location and the number of ways to find it, though most of the people working on it would be familiar on it.

1. Use XP_READERRORLOG Stored Procedure

XP_READERRORLOG is a system stored procedure used to find the ERRORLOG in SQL Server.

USE MASTER
GO
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'
       GO 


In this case, the ERRORLOG is configured to store in the D:\ drive. The location can vary based on organization requirements.

2. Use SQL Server Configuration Manager

In case, if you are unable to get the ERRORLOG location using the XP_READERRORLOG stored proc, then proceed to use the SQL Server Configuration Manger to find the location.

Steps to get the ERRORLOG location from SQL Server Configuration Manager: 

1.       Open the SQL Server Configuration Manager application
2.       From the left window pane select “SQL Server Service”
3.       Right click on “SQL Server (Your Server Name)” option from the right pane.
4.       Click on properties for more options
5.       A new window pops with advanced multiple options. Select “Startup Parameter” option to get the location of ERROR LOG.
6.       ERRORLOG location is found under the tab “Existing Parameters” starts with a command as -e

Let’s look at the screenshot for the same:


What is ACID Properties in SQL Server

What is ACID Properties in SQL Server
Many times, ACID properties happen to be the most asked interview question and most of the DBAs fail to answer. If you are a DBA, then ACID property is something mandatory to understand not just the acronym but also the concept behind it.

ACID properties are an acronym that is in conjunction with the transactions happening in the SQL Server. A transaction is a group of tasks that perform one more operation. For every transaction to happen in the database without any issues, there is a need to satisfy ACID properties.

A – Atomicity

Atomicity can be considered as an atomic unit in the database, wherein any transaction happening in the database should be finished without leaving any operation in the partial state. In the layman scenario, it can be considered as a “Do or Die”.

C – Consistency

It happens that a lot of data are stored in the databases in both small and big organizations. When a transaction is running, always there shouldn’t be any sort of impact on the data stored in the databases. Consistency is an idea to keep the data consistent irrespective of any number of data retrieval and transactions happening for the databases.

I – Isolation

In common terms, Isolation is nothing but separating an entity from another. Similarly, in the database as well, any transaction happening in the database should not interfere with the simultaneous transaction happening parallelly in the same database system. Irrespective of any number of transactions happening in the database system, there should not be a clash between the transactions itself.

D – Durability

A lot of times, database crashes are very common. Durability places a vital role in keeping the committed data safe. If a transaction is committed in the database, then the data is never lost. Irrespective of the failure of the system, the database should be durable enough to hold the committed content until the last updates.