Knowledge Base

Using SQL Backup tags to automate backup file naming

Category: How do I?
Date: 14 Jul 2009
Product: SQL Backup
The SQL Backup extended stored procedure offers a selection of tags that can be used in backup scripts. These tags can dynamically fill in details such as the database name, date, and time, and substitute them dynamically when the backup is performed.

SQL Backup allows you to perform backups and restores out of the box, but there may be additional features or configurations that applied to your situation/configuration.
The help file has a 'toolkit' section that explains all the different options that are available for the syntax that can change the tools from being a simple tool to a powerful utility.
One of the most useful features is the use of TAGS. These can be used in the folder locations for backup and copy commands (to disk and copy to).
They allow you to specify multiple locations using one command.
For example, if you have a command to backup multiple databases in one go, you can use the tags <DATABASE> and <TYPE> to backup to directories names by the database name and backup type, for instance, consider the SQL Backup script command:

exec master..sqlbackup '-SQL "Backup Databases[*] TO DISK=[c:\backups\<DATABASE>\<TYPE>\<DATABASE>_<TYPE>_<DATETIME ddmmYYYY>.sqb]"'

The preceding command would backup all databases on the server into their own individual folders, sorted by database name and type. If one of your databases was called "MyDatabase", for example, this backup file would be created:

c:\backups\MyDatabase\Full\MyDatabase_Full_31012008.sqb

More information about his can be found in the help file in the 'Tips' chapter under 'File Location Tags'.