I am not the first DBA to take up this cause… but I am determined to be the last

OK maybe this article is not THAT important LOL but it was a great quote by Obama so I figured I’d work it in LOL

Please know what Recovery Mode your databases are in and your recovery requirements and then back them up accordingly.

Too often I see databases that are in a Full or Bulk Logged recovery mode but do not have transaction log backups running.  If you’re not performing transaction log backups there really is no good reason to be in a Full or Bulk Logged Recovery Mode.

Below is a script that you can run against a single instance or if you use a Central Management Server ( See Brent Ozar’s post on CMS) that you can use as a starting point to provide information on your databases, what recovery mode then are in, and their most recent Full, Differential, and Transaction Log Backup Date/Time.  I’ve also included the databases “Updateability” and “UserAccess” as these settings would play a role in your backup strategy.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SELECT 	A.dbid, A.name as [DatabaseName], 
		CONVERT(sysname,DATABASEPROPERTYEX(A.name, 'Recovery')) AS [RecoveryMode],
		(	SELECT	max(backup_finish_date) 
			FROM	msdb.dbo.backupset 
			WHERE	[type] = 'D' and 
					database_name = A.name) AS [LastFullBackupDateTime],
		(	SELECT	max(backup_finish_date) 
			FROM	msdb.dbo.backupset 
			WHERE	[type] = 'I' and 
					database_name = A.name) AS [LastDiffBackupDateTime],
		(	SELECT	max(backup_finish_date) 
			FROM	msdb.dbo.backupset 
			WHERE	[type] = 'L' and 
					database_name = A.name) AS [LastLogBackupDateTime],
		CONVERT(sysname,DATABASEPROPERTYEX(A.name, 'Updateability')) Updateability,
		CONVERT(sysname,DATABASEPROPERTYEX(A.name, 'UserAccess')) UserAccess
FROM 	master.dbo.sysdatabases A WITH(NOLOCK)
WHERE 	A.name NOT IN ('tempdb')
GROUP BY A.dbid, A.name
ORDER BY A.name

Policy Based Management is also a great way to alert you on databases that do not have an appropriate backup configuration but if you’re not quite “there” yet this is a good place to start until you bridge that gap.

 
Here is a quick description of each Recovery Mode’s backup capabilities:

Simple Recovery Mode

• Full Backups – Yes
• Differential Backups – Yes
• Transaction Log Backups are NOT available because transactions are truncated from the transaction log usually after each checkpoint

Bulk-Logged Recovery Mode

• Full Backups – Yes
• Differential Backups – Yes
• Transaction Log Backups – Yes (but Bulk Operations like BCP, Bulk Insert, etc are minimally logged)

Full Recovery Mode

• Full Backups – Yes
• Differential Backups – Yes
• Transaction Log Backups – Yes

Enjoy!!