Does FULL/DIFF backup job auto include any new databases?
jerryhung
Posts: 138
When creating a new backup job, we get a LIST of databases to select with checkboxes
Q1: If we create a new Database on the instance, does the FULL/DIFF/TLOG RedGate backup job automatically include those?
Some considerations
Q2: Is the TLOG backup job smart enough to detect the new DB recovery mode, and ONLY include it if it's in FULL mode?
SQL 2005 Maint. Plan had this problem (if you select "All User Databases", and change Recovery mode afterwards, the plan fails)
I believe SQL 2008 fixes this issue
To add, judging from the job created, it explicitly list all the databases included. If that's the case, can we REQUEST this to be added as a feature b]automatically include newly created databases in existing backup plans[/b?
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [model,msdb,DB1, DB2] TO DISK = ''\\server\<database>\<AUTO>.sqb'' WITH ERASEFILES = 7, MAILTO_ONERRORONLY = ''jerryhung@xxx.com'', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 3, THREADCOUNT = 7, VERIFY, DIFFERENTIAL"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
Q1: If we create a new Database on the instance, does the FULL/DIFF/TLOG RedGate backup job automatically include those?
Some considerations
-
more work if we have to manually ADD them
may create failures if the new databases haven't had a FULL backup before the DIFF/TLOG backup are run
Q2: Is the TLOG backup job smart enough to detect the new DB recovery mode, and ONLY include it if it's in FULL mode?
SQL 2005 Maint. Plan had this problem (if you select "All User Databases", and change Recovery mode afterwards, the plan fails)
I believe SQL 2008 fixes this issue
To add, judging from the job created, it explicitly list all the databases included. If that's the case, can we REQUEST this to be added as a feature b]automatically include newly created databases in existing backup plans[/b?
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [model,msdb,DB1, DB2] TO DISK = ''\\server\<database>\<AUTO>.sqb'' WITH ERASEFILES = 7, MAILTO_ONERRORONLY = ''jerryhung@xxx.com'', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 3, THREADCOUNT = 7, VERIFY, DIFFERENTIAL"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
Jerry Hung
DBA, MCITP
DBA, MCITP
Comments
New databases will only be automatically included if you have a generic backup operation such as BACKUP DATABASES *, or BACKUP DATABASES EXCLUDE [...]. (See 'toolkit syntax' in help).
- Creating a new database will not alter your existing SQL Server Agent jobs. If you created the job using SQL Backup, you'll be able to easily edit the job from within the GUI (Select the instance and switch to the jobs tab, then right click -> Edit)
- The GUI is smart enough to know what's possible and will grey-out databases that are in simple recovery mode for TLOG operations. If you use the stored procedure directly, an error will be generated if you attempt to perform such an action. If you use a generic operation like 'BACKUP LOGS * TO DISK...' it will automatically exclude simple recovery mode databases.
Hope this helps,Development
Red-Gate Software
I figured out how to do those in GUI
BACKUP * for FULL backup
BACKUP * exclude master for all DIFF backup
However, the GUI is still not "smart" enough to interept
BACKUP * for TLOG backup
at run-time, where it ONLY backs up databases in full recovery mode
I guess it's not a big deal since I will get an ERROR email if a database recovery mode is changed out of FULL mode, however it is cumbersome to REMEMBER to add new FULL-recovery-mode databases into the TLOG backup job
DBA, MCITP