You can define a number of options for file management that are used throughout SQL Backup Pro. 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. 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 wizards. 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 file location tags in the path if required. For example, if you want the backup files for each database to be created in a separate folder, specify the <DATABASE> tag: C:\MyBackups\<DATABASE>

If you do not specify a location, SQL Backup Pro 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 format for backup files using file location tags. The default format 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_20120229_005042.sqb where the numbers show the date and time of the backup.

The settings are saved on your computer. You must have Full Control permissions for the following registry entries in order to make changes to these settings:

• HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<Instance_Name>\BackupFolder
• HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<Instance_Name>\BackupFileName

For details of the tags you can use to specify the backup folder and file name format, see File location tags.

SQL Backup log files

When SQL Backup Pro backs up or restores a database, the details of the operation are sent to a log file. You can specify the default folder for SQL Backup log files and set up SQL Backup Pro to delete the log files at regular intervals. These settings are used whenever you run a backup or restore process.

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 more information on tags, see File location tags.

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:

• %PROGRAMDATA%\Red Gate\SQL Backup\Log\<instance> (on Windows Vista, Windows 2008 and later), or 
• %ALLUSERSPROFILE%\ Application Data\Red Gate\SQL Backup\Log\<instance> (on Windows XP and Windows 2003).

If you want SQL Backup Pro to delete old log files automatically, select the Delete log files older than 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 Pro 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 and restore history

Select Delete backup and restore history to limit the amount of history stored in the msdb database and the SQL Server Compact database (created when the SQL Backup Pro server components are installed). This includes backup and restore activities that were not performed using SQL Backup Pro.

If the msdb database and Compact database contain a large amount of history, it may take some time to reduce the existing history when the setting is first applied. This setting also determines the amount of data stored in the user interface activity cache; the cache is populated with data from the msdb and Compact databases each time the graphical user interface is started.

SQL Backup Pro uses the stored procedure msdb..sp_delete_backuphistory to delete the history from the msdb database when the graphical user interface is running. If executing this stored procedure causes performance problems, you may find it helpful to add indexes to the backup and restore history tables in the msdb database. For more information, refer to your SQL Server documentation.

Alternatively, clear this option and run the stored procedure manually at a convenient time or as a scheduled task. For more information, see Deleting backup and restore history manually.