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.
- Code: Select all
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.
- Code: Select all
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