Backup online databases only...

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Backup online databases only...

Postby gscdba » Tue Jan 24, 2012 12:52 pm

Answered my own question!
:!:
Last edited by gscdba on Tue Jan 24, 2012 1:22 pm, edited 4 times in total.
gscdba
 
Posts: 4
Joined: Tue Jan 24, 2012 12:46 pm

Postby gscdba » Tue Jan 24, 2012 1:16 pm

ok - seems dynamic SQL is the answer...



EXEC sp_executesql @dSQL
gscdba
 
Posts: 4
Joined: Tue Jan 24, 2012 12:46 pm

Re:

Postby gscdba » Tue Jan 24, 2012 1:27 pm

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:

Code: Select all
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
 
Posts: 4
Joined: Tue Jan 24, 2012 12:46 pm

Postby petey » Tue Jan 24, 2012 4:20 pm

What sort of databases was SQL Backup picking up when you used the wildcard backup i.e.

Code: Select all
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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby gscdba » Wed Jan 25, 2012 11:49 am

petey wrote:What sort of databases was SQL Backup picking up when you used the wildcard backup i.e.

Code: Select all
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: 4
Joined: Tue Jan 24, 2012 12:46 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 1 guest