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

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

The settings are saved on your computer. Note that you must have Full Control permissions for the following registry keys 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 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.

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:

%PROGRAMDATA%\Red Gate\SQL Backup\Log\<instance> (Windows Vista, Windows 2008 and later)

%ALLUSERSPROFILE%\ Application Data\Red Gate\SQL Backup\Log\<instance> (Windows XP and Windows 2003)

If you want SQL Backup 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 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 server components are installed). This includes backup and restore activities that were not performed using SQL Backup.

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.

Note that this setting will determine the amount of history available when you choose Select from backup history on step 1 of the Restore wizard. If the backup history has been deleted, you can select specific backup files by choosing Browse for backup files to restore from the drop-down box.

SQL Backup 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. Alternatively, clear this option and run the stored procedure manually at a convenient time or as a scheduled task. For more information, refer to your SQL Server documentation.