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)
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
Posts: 5 Bronze 1
Comments
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)Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.