Not an SQL admin and new to ola-hallengren great scripts.
Just wondering if people are mostly doing a blanket user database backup with his full/diff script regardless on if a DB is in Simple or Full recovery mode, or is it preferred that Simple mode DBs and backup up separately etc.
Or, does it not even matter??
It depends on your RTO (Recovery Time Objective) and RPO (Recovery Point Objective).
Recovery Time Objective: Time duration to restore the database in case of any disaster.
Recovery Time Objective: The time interval that may pass until the database recovered. (Acceptable time duration for data loss).
Before you decide your recovery model and backup strategy, I would suggest you go through the Database Recovery Model and Backup Types in SQL Server before you decide your recovery model and backup strategy.
Edit:1
The ola-hallengren backup SP doesn't automatically execute all supported backups according to the recovery model of respective databases.
So, if you execute the SP with @BackupType = 'LOG' for the database with simple recovery model, I hope it'll fail and raise & log an error.
You need to execute separate script for all types of backup with @BackupType = 'FULL',@BackupType = 'DIFF' & @BackupType = 'LOG'.
You can execute separate script for LOG backup of the databases with FULL/BULKED_LOG recovery model with @Databases='Database_1, Database_2,...'.
Note: All backup logs (success/failure) are maintained into CommandLog table.
Edit:2
At line 2136, it's validating, " if @BackupType with recovery model of the database, then only it's proceeding to prepare backup script. to if condition is like this. Prior to that line I didn't see any validation to raise/log an error message into the commandlong table.
So I would suggest you to execute separate script for transaction log backup that includes the databases with full recovery model.
Fourth line of the below script validating the condition.
IF @CurrentDatabaseState = 'ONLINE'
AND NOT (@CurrentUserAccess = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
AND NOT (@CurrentInStandby = 1)
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel = 'SIMPLE')
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL)
AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL)
AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabaseName = 'master')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'N' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'Y' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'DIFF' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'N' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'Y' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT ((@CurrentLogShippingRole = 'PRIMARY' AND @CurrentLogShippingRole IS NOT NULL) AND @CurrentBackupType = 'LOG')
AND NOT (@CurrentIsReadOnly = 1 AND @Updateability = 'READ_WRITE')
AND NOT (@CurrentIsReadOnly = 0 AND @Updateability = 'READ_ONLY')
AND NOT (@CurrentBackupType = 'LOG' AND @LogSizeSinceLastLogBackup IS NOT NULL AND @TimeSinceLastLogBackup IS NOT NULL AND NOT(@CurrentLogSizeSinceLastLogBackup >= @LogSizeSinceLastLogBackup OR @CurrentLogSizeSinceLastLogBackup IS NULL OR DATEDIFF(SECOND,@CurrentLastLogBackup,GETDATE()) >= @TimeSinceLastLogBackup OR @CurrentLastLogBackup IS NULL))
Thanks!
The most common way to handle this is to skip those in simple mode when you do log backup. That is how Ola's scripts does it, and also how the built-in main plans does it.
(As a historic comment, maint plans did attempt to do log backup also for databases in simple mode in the very first incarnation back in SQL Server 2005. I believe that this was "fixed" in some sp, perhaps sp2.)
I.e., in essence you just throw in all databases (or however you want to group them) in your maint job, and the utility you use will handle this for you. In other words, you control whether to do log backup using the recovery model setting.
Great question actually and to answer your question if you enable both you are fine. The LOG Backup job will automatically skip databases that are in SIMPLE recovery by default as there is no work to complete. Worst case scenario you schedule it to run once every 24hrs, and it never does anything and if anyone ever creates or restores a database that is in Full recovery at least it will backup the logs once a day.
Please ensure that you setup schedules for these two jobs as I don't believe one is created by default: the DatabaseBackup - USER_DATABASES - FULL DatabaseBackup - USER_DATABASES - LOG
