Activity overview
Latest activity by 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. / comments
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 o...
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)
/ comments
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 a...
ok - seems dynamic SQL is the answer...
EXEC sp_executesql @dSQL / comments
ok - seems dynamic SQL is the answer...
EXEC sp_executesql @dSQL
Backup online databases only...
Answered my own question!
:!: