Backup online databases only...

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

  gscdba January 24, 2012 07:16

  - 0 +
• 

  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)
  

  gscdba January 24, 2012 07:27

  - 0 +
• 
  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.

  petey January 24, 2012 10:20

  - 0 +
• 

  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.

  gscdba January 25, 2012 05:49

  - 0 +

Add comment

Please sign in to leave a comment.