Backup online databases only...

gscdbagscdba Posts: 5 Bronze 1
edited January 25, 2012 5:49AM in SQL Backup Previous Versions
Answered my own question!
:!:

Comments

  • gscdbagscdba Posts: 5 Bronze 1
    ok - seems dynamic SQL is the answer...



    EXEC sp_executesql @dSQL
  • gscdbagscdba Posts: 5 Bronze 1
    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)
    
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • gscdbagscdba Posts: 5 Bronze 1
    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.
Sign In or Register to comment.