Backup online databases only...

gscdba

Answered my own question!
:!:

gscdba

ok - seems dynamic SQL is the answer...



EXEC sp_executesql @dSQL

gscdba

gscdba wrote:

ok - seems dynamic SQL is the answer...



EXEC sp_executesql @dSQL

For those interested in a dynamic solution, I used this to generate the comma separated list of databases which are online and in full recovery model, then wrapped the SQLAgent job step generated by Red-Gate backup:

DECLARE @sql NVARCHAR(MAX)
DECLARE @listOfDatabasesForBackup NVARCHAR(MAX)
SET @listOfDatabasesForBackup = (
SELECT DISTINCT
    STUFF((SELECT
            [name] + ','
           FROM
            sys.[databases] AS D
           WHERE
            [state] = 0
            AND [recovery_model] = 1
          FOR
           XML PATH('')
          ), 1, 0, '') AS 'OnlineDatabases'
FROM
    sys.[databases] AS D
WHERE
    [state] = 0
    AND [recovery_model] = 1
)
SET @listOfDatabasesForBackup = LEFT(@listOfDatabasesForBackup, LEN(@listOfDatabasesForBackup) - 1)

petey

What sort of databases was SQL Backup picking up when you used the wildcard backup i.e.

EXEC master..sqlbackup '-sql "BACKUP DATABASES [*] TO ... " '

Thanks.

gscdba

petey wrote:

What sort of databases was SQL Backup picking up when you used the wildcard backup i.e.

EXEC master..sqlbackup '-sql "BACKUP DATABASES [*] TO ... " '

Thanks.

Was unaware of this wildcard feature - in any case, I needed a specific filter (online and full recovery model) so querying sys.databases gave me the flexibility.