File management options - SQL Backup

You can define a number of options for file management that are used throughout SQL Backup. For example, you can specify the default folder for backup files.

On the Tools menu, click Server Options. The File Management tab is selected by default.

In the SQL Server box, select the name of the SQL Server instance for which you want to set the options.

Note that if the selected SQL Server does not have the server components installed, a warning is displayed and you must install the server components to proceed.

Backup file locations

You can set up a default folder and file name format for your backups, so that you do not have to enter the same information repeatedly when you create backups or backup jobs using the graphical user interface.

The settings are also used when you use the BACKUP command in the command line or an extended stored procedure.

In the Backup folder box, type the path for the folder in which you want your backups to be created by default, or click to browse to the location. You can use tags in the path if required. For example, if you want the backup files for each database to be created in a separate folder, you can specify the <DATABASE> tag:

C:\MyBackups\<DATABASE>

For details of the tags you can use, see File location tags.

If you do not specify a location, SQL Backup uses the default SQL Server backup folder, which is usually C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP.

In the File name format box, define the default file name for backup files using the tags. The default is:

<TYPE>_<INSTANCE>_<DATABASE>_<DATETIME yyyymmdd_hhnnss>

For example, for a full database backup of the SalesData database running on the default SQL Server instance, the form of the backup file name would be:

FULL_(local)_SalesData_20090329_005042.sqb

where the numbers show the date and time of the backup. To reset the file name format to its default value, click Reset.

Note that you cannot include directories (for example, \<DATABASE>\) in the default file name format.

Your settings will be used the next time you use a backup wizard or the BACKUP command. The settings are saved on your computer.

SQL Backup log files

When SQL Backup backs up or restores a database, the details of the operation are sent to a log file. You can use the Server Options dialog box to specify the default folder for SQL Backup log files, and set up SQL Backup to delete the log files at regular intervals. You can also set up SQL Backup to delete the SQL Server backup history at intervals.

These settings are used when you run a backup or restore process using the graphical user interface or the BACKUP and RESTORE commands.

In the Log file folder box, type the path for the folder in which you want your log files to be created by default, or click to browse to the location. You can use tags to define the path. For optimal performance, you are recommended to use a dedicated folder for the SQL Backup log files. By default, the primary log file is created in the folder:

C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log

If you want SQL Backup to delete old log files automatically, select the Delete log files in this folder check box, then type or select the age (in days or hours) of the files that you want to delete.

If you are scripting a SQL Backup job, you can automatically create a copy of the log file in a different location using the LOGTO argument with the BACKUP or RESTORE command; to delete these files in addition to the primary log files, select the Delete log files in LOGTO folders check box.

By scripting SQL Backup jobs you can also prevent any SQL Backup log files from being created on disk (using the NOLOG option), or specify that log files should only be created if errors (LOG_ONERRORONLY option) or warnings and errors (LOG_ONERROR option) occur during the backup or restore process.

For more information, see The BACKUP command and The RESTORE command.

SQL Server backup history

If you want SQL Backup to delete the SQL Server backup history in the msdb database, select the Delete old backup history check box, then type or select the age (in days or hours) of the files that you want to delete.

SQL Backup uses the stored procedure msdb..sp_delete_backuphistory to delete the history. Note that this deletes all backup and restore operation history, including backup and restore activities that were not performed using SQL Backup.