Red Gate forums :: View topic - Backup online databases only...
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  | Register 
Go to product support center
SQL Backup 6
SQL Backup 6 forum

Backup online databases only...

 Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
gscdba



Joined: 24 Jan 2012
Posts: 4
PostPosted: Tue Jan 24, 2012 12:52 pm    Post subject: Backup online databases only... Reply with quote
Answered my own question!
Exclamation

Last edited by gscdba on Tue Jan 24, 2012 1:22 pm; edited 4 times in total
Back to top
View user's profile Send private message
gscdba



Joined: 24 Jan 2012
Posts: 4
PostPosted: Tue Jan 24, 2012 1:16 pm    Post subject: Reply with quote
ok - seems dynamic SQL is the answer...



EXEC sp_executesql @dSQL
Back to top
View user's profile Send private message
gscdba



Joined: 24 Jan 2012
Posts: 4
PostPosted: Tue Jan 24, 2012 1:27 pm    Post subject: Re: Reply with quote
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:

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)
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2216
PostPosted: Tue Jan 24, 2012 4:20 pm    Post subject: Reply with quote
What sort of databases was SQL Backup picking up when you used the wildcard backup i.e.

Code:
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
Back to top
View user's profile Send private message Send e-mail
gscdba



Joined: 24 Jan 2012
Posts: 4
PostPosted: Wed Jan 25, 2012 11:49 am    Post subject: Re: Reply with quote
petey wrote:
What sort of databases was SQL Backup picking up when you used the wildcard backup i.e.

Code:
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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group