Backup online databases only...

Comments

• gscdba Posts: 5 Bronze 1

  January 24, 2012 7:16AM

  ok - seems dynamic SQL is the answer...
  
  
  
  EXEC sp_executesql @dSQL

  0 · Share on Twitter
• gscdba Posts: 5 Bronze 1

  January 24, 2012 7:27AM

  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)
  

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  January 24, 2012 10:20AM

  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

  0 · Share on Twitter
• gscdba Posts: 5 Bronze 1

  January 25, 2012 5:49AM

  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.

  0 · Share on Twitter