SQLTeam.com | Weblogs | Forums

Making Differential Backups of Databases in SIMPLE Recovery Mode


#1

Hi all,
We use Commvault to make all of our database backups.
For example, I see this running:

backup database ContractDB to Disk = 'H:\ContractDBDiff2.diff' with differential;

But this database uses the SIMPLE recovery mode. So I'm wondering if these differential backups are nay good, would even be usable. (We do make a FULL backup daily, followed by these differentials.)

Does anyone know for certain? Thanks for your tips.


#2

SIMPLE Recovery = No log backups.
Operations that require transaction log backups are not supported by the simple recovery model

I bet those backups are full backups even though they say diff.

https://msdn.microsoft.com/en-us/library/ms189275.aspx


#3

You can perform differential backups against a database in simple recovery. In order for those to be used - you also have to have the full backup that was performed prior to the differential.

It appears you have both CommVault and Native backups where the native backups are performing the diffs. If so, then as long as you can restore the fulls from CommVault with no recovery - then you can use those diffs to recover up to that point in time as long as CommVault is not performing copy only backups.


#4

Thanks yosiasz and Jeffw8713 In the past, I've only used full and t-log backups so I'm learning from you.
Commvault is running botha Full backup weekly and frequent differentials. So what's the difference between
transaction log backups and differentials? I suppose with differential you can't perform a point-in-time restore whereas yiu can with tlog backups which are made when the db is using the Full recovery model. Am I right?

Thanks this is very informative.


#5

Hi,

The differential backups are all the changes since the last full backup, you would often use these to save time and space when it comes to backing up your database, as you create each diff backup the file would (in theory) be larger than the last diff backup as each file contains all the changes since the last full backup. This means that you can restore with just two files, the full backup and the last differential.

Transaction logs give you the ability to restore to a point in time and, providing your databases are in the full recovery model, should be run in conjunction with your full or full/diff backup strategy. The transaction log backups contain all the changes since the last transaction log backup (or full backup which ever was last). To restore with these you still need to make sure you have your full backup file in place but you then have to restore each and every transaction log file sequentially to achieve recovery to the point in time. In addition you need your transaction log backups to manage your transaction logs for any database in Bulk or Full recovery, this will stop your log just growing and growing.


#6

Thanks for that detailed explanation of how differential backups are used, Stephen.
This brings up one final question. How can I view the date/time of the last differential backup, what is the T-SQL
statement to retrieve that, or is there one?
Thanks!


#7

Here You may get a script for checking last backup time
blog.sqlauthority.com/2010/11/04/sql-server-finding-last-backup-time-for-all-database/


#8

Hi,

Sorry about the delay in responding, I wasn't ignoring you just away for the weekend.

I haven't read Jason's reply but I suspect that will be pretty all encompassing, I was just going to suggest querying the dbo.backupset in your msdb database, that is a good starting point for your backup information, it tells you backup types, LSN numbers, date etc. There are a lot of columns in that table but if you google the table name there is a Microsoft MSDN page that will explain most, if not all, of the columns.

I hope that helps as well as Jason's.