What are the challenges you face when working across database platforms? Take the survey
Options

Does FULL/DIFF backup job auto include any new databases?

jerryhungjerryhung Posts: 138
edited November 17, 2009 2:04PM in SQL Backup Previous Versions
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
    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

Comments

  • Options
    Hi,

    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).
    1. 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)
    2. 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,
    Robin Anderson
    Development
    Red-Gate Software
  • Options
    Thanks

    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
    Jerry Hung
    DBA, MCITP
Sign In or Register to comment.