use variable for database name in backup command

Comments

• petey Redgate › Posts: 2,358 New member

  June 25, 2012 9:41PM

  No, that isn't possible. If you don't want to maintain a hardcoded list of database names, consider the following options:
  
  - to back up all system databases
  You could use the SYSTEM option e.g.

  EXEC master..sqlbackup '-sql "BACKUP SYSTEM DATABASES TO DISK = [E:\Backups\<AUTO>]"'
  

  - to back up all user databases
  You could use the USER option e.g.

  EXEC master..sqlbackup '-sql "BACKUP USER DATABASES TO DISK = [E:\Backups\<AUTO>]"'
  

  - to back up all database except a subset
  You could use the EXCLUDE option e.g.

  EXEC master..sqlbackup '-sql "BACKUP DATABASES EXCLUDE [model, AdventureWorks] TO DISK = [E:\Backups\<AUTO>]"'
  

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• fredmut Posts: 7

  June 26, 2012 10:06AM

  This is a pretty severe limitation as it assumes a database hasn't been created, moved, taken offline, changed recovery model, put in single user mode or any number of other possibilities that could cause an error. So, for a server with, let's say more than a dozen or so databases I have to constantly monitor and adjust my backup jobs. Please add this to your feature request list.

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  June 26, 2012 11:47AM

  This is a pretty severe limitation as it assumes a database hasn't been created, moved, taken offline, changed recovery model, put in single user mode or any number of other possibilities that could cause an error.

  Taking the example of the EXCLUDE option, e.g.
  

  EXEC master..sqlbackup '-sql "BACKUP DATABASES EXCLUDE [model, AdventureWorks] TO DISK = [E:\Backups\<AUTO>]"'
  

  
  SQL Backup does the following:
  
  - get a list of databases from the sysdatabases table
  - discard databases in recovery, offline, or read-only mode from the list
  - discard the explicitly named databases from the list i.e. model and AdventureWorks
  - back up the remaining databases
  
  This addresses the following situations you mentioned:
  
  - databases that hasn't been created - SQL Backup obtains the base list of databases from SQL Server every time it runs, so even for new databases, as long as they are online, they will be backed up
  
  - moved - not sure what you mean, but if it's still online, it will be backed up
  
  - taken offline - as mentioned, SQL Backup ignores these databases
  
  - changed recovery model - has no impact on the outcome of a full database backup. If you use BACKUP LOGS instead, SQL Backup will only attempt to back up databases using the full or bulk-logged recovery models.
  
  - any number of other possibilities - we believe we have addressed the most common possibilities. We are always open to suggestions to further improve the reliability of this feature.
  
  While I mentioned that it isn't possible to use a variable containing a database name in the SQL Backup backup statement, it is however possible to form the command using 2 or more strings, and run them as a single command string e.g.
  

  DECLARE @dbname nvarchar(256)
  SET @dbname = N'pubs'
  
  DECLARE @sql nvarchar(1024)
  SET @sql = N'-sql "BACKUP DATABASE ' + @dbname + ' TO DISK = [e:\Backups\<AUTO>]"'
  
  DECLARE @exitcode INT
  DECLARE @sqlerrorcode INT
  
  EXEC master..sqlbackup @sql, @exitcode OUTPUT, @sqlerrorcode OUTPUT
  SELECT @exitcode, @sqlerrorcode
  

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter