Rotating Header Image

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!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

3 Comments

  1. Paul Randal says:

    Don’t forget the log backups that include a minimally-logged operation performed in the BULK_LOGGED recovery mode will be the *same* size as if the operation was performed in the FULL recovery mode. Minimal logging only affects the amount of log generated, not what gets put into the log backup – it will contain all extents changed by the minimally-logged operation.

    Thanks

  2. Thanks Paul!!

    Very good point 🙂

    I should of included this post from you as a “recommended read” as there is some great stuff in there: http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-(allocation-BULK_LOGGED-mode-locking).aspx

  3. While, externally, the phone is almost identical to the M8, under the hood you’ll find an Qualcomm Snapdragon 615, and it’s fairly obvious that this is where the M8s’ price-tag comes from.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.