Backing up before deployment - SQL Compare

The Configure Backup page of the Deployment Wizard allows you to perform a full backup using either Red Gate SQL Backup, or SQL Server native backups.

Backup name and location

For SQL Server native:

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.
Type the file name in the box to the right of the Backup folder box.

For Red Gate SQL Backup:

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.
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 in the SQL Backup online help.

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

Note that the file path is relative to the selected SQL Server. For example, if you have 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. Note that if a file of the same name exists already and you have not chosen to overwrite it, the backup will fail if you are using SQL Backup.

Backup compression (Red Gate SQL Backup only)

If you are 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.

Note that if SQL Backup Lite is installed on the SQL Server, you can choose only compression level 1.

Backup encryption (Red Gate SQL Backup only)

If you are 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.

If SQL Backup Pro is installed on the SQL Server, you can choose 128-bit or 256-bit encryption; if SQL Backup Standard is installed on the SQL Server, you can choose only 128-bit encryption; if SQL Backup Lite is installed on the SQL Server, you cannot encrypt the backup.

You must remember your password; if you do not, you will not be able to access the encrypted backup.

Using multiple threads (Red Gate SQL Backup only)

If you are 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. You are recommended to start with one thread fewer than the number of processors. For example, if you are 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 in the online help for SQL Backup.

Was this article helpful?

Search support