use variable for database name in backup command

fredmut · June 25, 2012 1:26PM

Is it possible to pass in a TSQL like variable for the database name so I don't have to maintain a hard coded list of databases to backup or exclude? i.e.
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE @dbname ...... " '

I'm not finding that addressed in the documentation.

petey · 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 = &#91;E:\Backups\&lt;AUTO&gt;&#93;"'

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

EXEC master..sqlbackup '-sql "BACKUP USER DATABASES TO DISK = &#91;E:\Backups\&lt;AUTO&gt;&#93;"'

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

EXEC master..sqlbackup '-sql "BACKUP DATABASES EXCLUDE &#91;model, AdventureWorks&#93; TO DISK = &#91;E:\Backups\&lt;AUTO&gt;&#93;"'

fredmut · 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.

petey · 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 &#91;model, AdventureWorks&#93; TO DISK = &#91;E:\Backups\&lt;AUTO&gt;&#93;"'

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&#40;256&#41;
SET @dbname = N'pubs'

DECLARE @sql nvarchar&#40;1024&#41;
SET @sql = N'-sql "BACKUP DATABASE ' + @dbname + ' TO DISK = &#91;e:\Backups\&lt;AUTO&gt;&#93;"'

DECLARE @exitcode INT
DECLARE @sqlerrorcode INT

EXEC master..sqlbackup @sql, @exitcode OUTPUT, @sqlerrorcode OUTPUT
SELECT @exitcode, @sqlerrorcode

use variable for database name in backup command

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk