SQL Compare 15

Backing up before deployment

On the The Configure backup page of the deployment wizard, you can choose to back up the target before deployment. Backup commands are included at the top of the deployment script.

You can back up using either Redgate SQL Backup Pro or SQL Server native backups.

Backup name and location

For SQL Server native:

  1. Type the file path in the Backup folder box or click  to specify the path using the folder browser.
    By default, Backup folder is set to the default backup folder for the SQL Server instance.
  2. Type the file name in the box to the right of the Backup folder box.

For Redgate SQL Backup:

  1. Type the file path in the Backup folder box or click  to specify the path using the folder browser.
    By default, Backup folder is set to the folder specified in the SQL Backup options for the SQL Server instance. If no backup file locations have been set up, SQL Backup uses the SQL Server instance's default backup folder.
  2. Specify the file name in the box to the right of the Backup folder box.
    By default, the file name is set to <AUTO>.sqb; SQL Backup uses the SQL Backup options to generate the backup file path and file name. If no backup file locations have been set up, SQL Backup uses the SQL Server instance's default format for file names.
    To change the file name, clear the Name file automatically check box, and type the required file name. You can use SQL Backup tags, if required. For information about tags, see File location tags (SQL Backup documentation).

To specify a network path in the Backup folder box, type the full path, including the server name, for example \\ServerName\MyFolder

The file path is relative to the selected SQL Server. For example, if you've chosen to back up a database on a remote SQL Server instance called ServerA and you specify a local path such as C:\Backups, the backup files will be created on the C: drive on ServerA, not on the local computer.

Select the Overwrite existing backup files of the same name check box if you want to overwrite any files of the same name that exist for the file path you specified in the Backup Folder box.

If a file of the same name exists already and you haven't chosen to overwrite it, the backup will fail if you're using SQL Backup.

Backup compression (Redgate SQL Backup only)

If you're using SQL Backup to back up the target database, you can choose from the three compression levels described below. Generally, the smaller the resulting backup file, the slower the backup process.

To compress the backup, select the Compress backup check box and select the compression level by moving the slider.

  • Compression level 3
    Compression level 3 uses the zlib compression algorithm. This compression level generates the smallest backup files in most cases, but it uses the most CPU cycles and takes the longest to complete.
  • Compression level 2
    This compression level uses the zlib compression algorithm, and is a variation of compression level 3.
    On average, the backup process is 15% to 25% faster than when compression level 3 is used, and 12% to 14% fewer CPU cycles are used. Backup files are usually 4% to 6% larger.
  • Compression level 1
    This is the default compression level. It is the fastest compression, but results in larger backup files. On average, the backup process is 10% to 20% faster than when compression level 2 is used, and 20% to 33% fewer CPU cycles are used. Backup files are usually 5% to 9% larger than those produced by compression level 2. However, if a database contains frequently repeated values, compression level 1 can produce backup files that are smaller than if you used compression level 2 or 3. For example, this may occur for a database that contains the results of Microsoft SQL Profiler trace sessions.

Backup encryption (Redgate SQL Backup only)

If you're using SQL Backup to back up the target database, you can encrypt the backup by selecting the Encrypt backup check box, then typing a password for the backup in Password, and again in Confirm.

You can choose 128-bit or 256-bit encryption.

You must remember your password. If you forget your password, you won't be able to access the encrypted backup.

Using multiple threads (Redgate SQL Backup only)

If you're using SQL Backup to back up the target database and you are using a multi-processor system, using multiple threads can speed up the backup process. Select the Use multiple threads check box and type or select the number of threads up to a maximum of 32. We recommended you start with one thread fewer than the number of processors. For example, if you're using four processors, start with three threads.

For details of how you can find out the most effective number of threads to use for your setup, see Optimizing backup speed (SQL Backup documentation).


Didn't find what you were looking for?