Tag Archives: Backups

Find Databases without recent full backups

Posted on by
Reply

One of the important tasks a SQL Server DBA does is database backup. While maintaining the backups of the important database is important, the need to monitor that the exists a valid Full backups for the databases is equally important. This blog will try to explain how to find the list of databases which do not have a Full backup in the recent few days. (8 days in this example). You can obviously change the value in DATEADD function to suit your choice for # of days to look for.

The code used in this example is found at the end of this blog.

First, I run the script to find the list of databases which do not have a full backup. The very first statement you see in the script (SELECT @@VERSION ) is not required, I included it my example to show the SQL Server Version on which I am executing these scripts. The reason for including it is, a slight change in the script. In SQL Server 2005 and 2008, I am querying sys.databases and in SQL Server 2000 it does not exist, so I would need to use master..sysdatabases.

The query returns list of the databases on my instance which do not have a full backup in the last 8 days.

I then perform a backup of Adventureworks database, which is one of the database missing a full backup. the picture below is the script I run to create a backup of Adventureworks DB.

The next thing I do is run the same script that was run at the beginning to find out the databases without full backups and this time displays only the database Snapshot_Source.

The following is the script that works for SQL Server 2000. Note there is no database missing full backups, indicating I have at least full backup of all databases on that server.. Based on the importance of the data in the databases, other backups types have to be configured and monitored. However, a full database backup would always be the base of any database recovery process and should be as recent as possible to avoid delays in recovery process.

Script used in this example…

SELECT @@VERSION AS [SQL SERVER VERSION]

SELECT NAME FROM sys.databases
WHERE NAME != 'TEMPDB'
AND NAME NOT IN (    SELECT DISTINCT database_name FROM msdb..backupset
WHERE backup_start_date > DATEADD(DAY,-8,GETDATE())
AND  TYPE = 'D' )

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

SQL Server Backup File info using T-SQL Query

Posted on by
Reply

Few days back, I was asked this question. Using T-SQL is there a way to find the backup details about a database? Including physical location of the database where the backup of a particular database was performed. I did recollect that I wrote such a T-SQL query sometime back to find the backup locations of database backup. I came up with the below query that helped finds the backup information of a particular database.

Find the T- SQL Script at the end of the post. Remember to change the value of the local variable @DBNAME when running in your system. The query has been tested in SQL Server 2008.

The output of the query was wide enough not to fit in the image, hence it has been broken into 2 and displayed. The query displays the details in the reverse chronology, i.e the most recent backup first.

T-SQL Query used in the article

DECLARE @DBNAME VARCHAR(128)

SET @DBNAME = 'DATABASENAME'

SELECT A.database_name, B.physical_device_name
,A.media_set_id,A.backup_size,
A.backup_start_date,A.backup_finish_date
FROM msdb.dbo.backupset A
INNER JOIN msdb.dbo.backupmediafamily B
ON A.media_set_id = B.media_set_id
WHERE A.Database_Name= @DBNAME
ORDER BY A.backup_finish_date DESC

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Backup Information for a Database

Posted on by
Reply

How do I find the backup information about a particular database ?

Some one asked me this question in the past and since it is being repeatedly asked in forums and to me personally , I decided to write this.

Having the backup information of a database is very important for a database administrator. In case of disaster, the DBA has to be aware when was the last time that database was backed up.  If a SQL Server is inherited from another Admin and the schedule of the backups and the backup types performed is not known it is easy to find by querying the backup related tables in MSDB.

One of those tables is msdb.dbo.backupset. It has the backup information related to all databases on that instance. information such as backup size, backup type and the start and end time of the backup to name a few.

The following script is one such script that gives the backup info of a specific database with the most recent one first.

The value in bkup_type column indicates the type of backup performed. D indicates FULL backup, I indicates Differential and L indicates Log backup.

Use the following Script.

DECLARE @DBNAME VARCHAR(50)

SET @DBNAME = 'LSWB'

SELECT CEILING(((backup_size / 1024)/1024)/1024)
Bkup_Size_In_GB,
CEILING(((backup_size / 1024)/1024)) Bkup_Size_In_MB,
[type] Bkup_Type,
backup_start_date StartTime,backup_finish_date
FROM msdb.dbo.backupset
WHERE DATABASE_NAME = @DBNAME
ORDER BY backup_start_date DESC

— Bru Medishetty