0

I'm looking for a solution to backing up databases based on their status stored in a DB Table (Inactive databases stay online as well) i.e. Get the status of active DBs using

SELECT dbName from DBMain.TableWithDatabaseStatus where activeStatus=1

And perform full/diff/transactional backups only on the results of these databases e.g. Perform full backup if no recent backup exists, perform diff and incremental otherwise.

I'm currently looking at Ola Halengren's maintenance solution, where I found a good option @ChangeBackupType = 'Y' -> if full backup doesn't exists take that first. Query is something like this..

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@ChangeBackupType = 'Y'

But is there a way I can manually add/update DB names (based on results from query with activeStatus) in -> @Databases = 'USER_DATABASES', before running the backup script?

The main goal would be

  • Take full backups of all the active DBs on the weekend
  • Perform diff and transactional on the weekdays
  • If a new DB becomes active which wasn't active on weekend, take a full backup of that DB and go through the rest of DBs in normal routine.

ADBA here, so any pointers or help with Ola halengren's script is appreciated!

1 Answer 1

0

This is fairly straightforward to accomplish, you just need a wrapper for Ola's scripts to implement your logic and it should be good to go.

Here is a sample method for doing that, as scripted, this will take a LOG backup of any database that is active (I used a temp table, #TableStatus).

To fully implement you logic, you would need four jobs running this script with variations.

  • One job that runs on the weekend, that takes a FULL backup of any
    database that has the active flag.

  • One job that runs during the week (whatever interval you want) that
    takes a DIFF backup of any database that has the active flag.

  • One job that runs during the week (at desired interval) that takes a LOG backup of any database that has the active flag.

  • A job that runs at a different interval to catch any NEW (or
    re-activated) databases that are marked as Active. This one gets a
    little tricky.

You need a method for tracking both WHEN a database has become "active" AND if a full backup has been taken of it. You can do this a bunch of different ways, some more maintenance free than others. I've done the simplest, adding a field "FullBackupTaken", it should be set to NULL when the database ActiveStatus gets set to 1. Then you need a job that does a FULL backup on any Active database that has FullBackupTaken IS NULL.

A more complicated approach might have a "DateMadeActive" and then you can dive in to the msdb tables to see if a FULL has been taken for it or not.

DROP TABLE IF EXISTS #TableStatus 

CREATE TABLE #TableStatus
    (
    DatabaseName NVARCHAR(200) NOT NULL
    , ActiveStatus INT NOT NULL
    , FULLBackupTaken DATETIME2(7) NULL
    )

/** Specify desired backup type here, LOG, FULL, DIFF **/
DECLARE @BackupType NVARCHAR(20) = N'LOG'

DECLARE @BackupDirectory NVARCHAR(512) = 'C:\backup'

DECLARE @ChangeBackupType NCHAR(1) = N'Y'

DECLARE @DatabaseName NVARCHAR(200)

DECLARE curDBLoop CURSOR LOCAL STATIC FORWARD_ONLY
FOR (   SELECT T.DatabaseName 
        FROM #TableStatus AS T
            INNER JOIN sys.databases AS D ON D.[name] = T.DatabaseName 
                   AND D.state_desc = N'ONLINE'
                  WHERE T.ActiveStatus = 1
        )

OPEN curDBLoop 

FETCH NEXT FROM curDBLoop
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN

    PRINT 'Taking backup of ' + COALESCE(@DatabaseName, '??')

    EXEC dbo.DatabaseBackup @Databases = @DatabaseName, 
                            @Directory = @BackupDirectory, 
                            @BackupType = @BackupType, 
                            @ChangeBackupType = @ChangeBackupType;

    IF @BackupType = N'FULL'
    BEGIN
        UPDATE #TableStatus
        SET FULLBackupTaken = SYSUTCDATETIME()
    END

    FETCH NEXT FROM curDBLoop
    INTO @DatabaseName

END

CLOSE curDBLoop 
DEALLOCATE curDBLoop
3
  • Thank you so much for the detailed response! Looks exactly like what I was searching for. Probably seems simple for a DBA, but I'm a noob at SQL so I didn't think I could loop through the results of the query. I'll dig deeper, but as far as re-activated DB is concerned, even when all differential backups are being taken - but instead it takes a Full backup (if I understand 'ChangeBackupType ' option correctly) that should suffice. thanks again! <3
    – MSKH
    Oct 19, 2022 at 15:05
  • You don't really need the loop, because Ola's script accepts a comma-separated list. You can just set @Databases to the result of STRING_AGG over the list of databases Oct 20, 2022 at 0:36
  • yes, originally I had that kind of solution in mind, though the above answer still clarifies how I can approach this.. Appreciate your suggestion, will try that!
    – MSKH
    Oct 20, 2022 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.