0

I would like to backup most of the databases except the database DBA. I use below syntax in the job step:

EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES', '-DBA'
    @Directory = N'G:\BACKUP',
    @BackupType = 'DIFF',
    @Verify = 'Y',
    @CleanupTime = 336,
    @CheckSum = 'Y',
    @LogToTable = 'Y'

But it gives an error:

Msg 102, Sev 15, State 1, Line 3 : Incorrect syntax near '@Directory'. [SQLSTATE 42000]

I also tried:

@Databases = 'USER_DATABASES', -'DBA' 

does not work either.

Can anyone help? Thanks.

closed as off-topic by mustaccio, Erik Darling, LowlyDBA, RDFozz, Philᵀᴹ Jun 28 '18 at 20:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Erik Darling, LowlyDBA, RDFozz, Philᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

5

it has to be

@Databases = 'USER_DATABASES, -Db1'

See https://ola.hallengren.com/sql-server-backup.html

Assuming you want to backup all User Databases except one named DBA, your statement should be:

EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES, -DBA',
    @Directory = N'G:\BACKUP',
    @BackupType = 'DIFF',
    @Verify = 'Y',
    @CleanupTime = 336,
    @CheckSum = 'Y',
    @LogToTable = 'Y'
  • @kin USER_DATABASES is a command in Ola Halengren's scripts that backs up all user databases, not a database called USER_DATABASES. I'm not sure how you'd use it to backup just the database called USER_DATABASES – Greg Jun 29 '18 at 2:08
  • @Greg that means USER_Databases - substitute it with names of user dbs that you want to backup. Hope that is clear. – Kin Shah Jun 29 '18 at 2:09
  • @kin - ola.hallengren.com/sql-server-backup.html says that if you specify 'USER_DATABASES' then it will backup all user databases, the -databasename will exclude that from the list of all user databases – Greg Jun 29 '18 at 2:13
  • 4
    If you actually would have a database called USER_DATABASES, and you only want to perform a backup of that database, then you could do like this: @Databases = '[USER_DATABASES]' – Ola Hallengren Jun 29 '18 at 7:28

Not the answer you're looking for? Browse other questions tagged or ask your own question.