Database Backup (SQL SVR)
Go Up to Database Backup
This wizard lets you generate a BACKUP DATABASE script to be executed immediately, scheduled, or opened in the SQL Editor.
To create a database backup
- Connect to the datasource where the database to be backed up is located and ensure that it is the selected datasource. For details, see Connected/Selected Datasource options.
- Select Utilities > Database Backup. The Backup Wizard opens.
- Use the following table as a guide to providing details as you pass through the pages of the wizard.
Step.Panel | Options/Description | ||
---|---|---|---|
Backup Operation |
New Backup Operation |
Select to define a new backup operation. | |
Previous Backup Operation |
Select to open a previously defined backup operation and use the associated controls to provide the location and name of the job file. | ||
Database Selection |
Here you select the database you want to backup from a drop-down list and name the backup operation. You can keep the default name for the operation or replace it with one you type. The job description is optional and you can accept the default or write another. | ||
Backup Type |
Full Database |
Backs up the entire database. The size of the database is listed in parentheses. | |
Partial Database |
Generates a BACKUP DATABASE statement that includes a READ_WRITE_FILEGROUPS option, dictating a partial backup of specified files and file groups. | ||
Partial Incremental |
Generates a BACKUP DATABASE statement that includes WITH DIFFERENTIAL and READ_WRITE_FILEGROUPS options, dictating that only specified files and file groups changed since last backup should be backed up. | ||
Incremental Database |
Generates a BACKUP DATABASE statement that includes a WITH DIFFERENTIAL option, dictating that only potions of the database changed since last backup should be backed up. | ||
File and File Group |
These controls are only available if the Database option trunc. log on chkpt. is deselected. For more information, see Databases (SQL Server) - Options. |
Select to specify a file or file group. | |
Transaction Log |
Full Truncate- Backs up the inactive portion of the transaction log. The size of the transaction log is listed in parentheses. With No Log (not available for SQL Server 2008 only) - Removes the inactive portion of the transaction log without making a backup of it. You should only use this command when you run out of room in the database and cannot issue DUMP TRAN WITH TRUNCATE_ONLY. Full No Truncate - Makes it possible to dump a transaction log even if the database is inaccessible. You can use this option when the data portion of your database is damaged and the transaction log resides on a separate device. With Truncate Only (not available for SQL Server 2008 only) - Removes the inactive portion of the transaction log without making a backup of it. You should use this command prior to backing up a database. | ||
Backup Location |
This panels lets you provide values used to create a TO argument for the BACKUP DATABASE statement generated for this operation. Use the Add, Remove, and Contents buttons to manage the disk devices, tape devices, and directory/filenames that are to be specified on the TO argument. | ||
Backup and Media Information |
Here, you specify the backup set name, backup description, backup set password, media name, media description, and media name password. | ||
Backup Options |
Overwrite all backup sets on the device |
When selected, a WITH INIT option is generated with the BACKUP DATABASE statement, dictating that existing backup sets are to be overwritten. When deselected, a WITH NOINIT option is generated with the BACKUP DATABASE statement, dictating that this operation is appended to the media set. Selecting this option enables the Format the entire media before backup operation and Specify when the backup set can be overwritten controls | |
Format the entire media before backup operation |
When selected, a WITH FORMAT option is generated with the BACKUP DATABASE statement, dictating that a new media set be created. When deselected, a WITH NOFORMAT option is generated with the BACKUP DATABASE statement, dictating that the existing media header and backup sets on the media volumes be preserved. | ||
Specify when the backup set can be overwritten |
Selecting this check box activates the Expire Date and Retain Days controls. The Expire Date control lets you add a WITH EXPIREDATE option to the generated BACKUP DATABASE statement, specifying when the backup set can be overwritten. The Retain Days control lets you add a WITH RETAINDAYS option to the generated BACKUP DATABASE statement, specifying the number of days before the backup set can be overwritten. | ||
Skip backup set expiration and media name checking |
When selected, a WITH SKIP option is generated with the BACKUP DATABASE statement, disabling checking of expiration and name for the backup set. When deselected, a WITH NOSKIP option is generated with the BACKUP DATABASE statement, ensuring that the expiration date is checked before overwriting backup sets. This control is only enabled if Format the entire media before backup operation is deselected. | ||
Copy-Only backup |
When selected, a WITH COPY-ONLY option is generated with the BACKUP DATABASE statement, specifying that this backup is a copy-only backup, with no effect on normal backup operations. | ||
Compression (SQL Server 2008 only) |
You can select among Use the default server setting, Compress (generate a WITH COMPRESSION option), and No Compress (generate a WITH NO_COMPRESSION option) | ||
Read and verify the integrity of the backup after finish |
If selected, a RESTORE VERIFYONLY is generated to follow the BACKUP DATABASE statement, verifying but not restoring the backup operation. | ||
Perform backup checksums |
When selected, a WITH CHECKSUM option is generated with the BACKUP DATABASE statement, enabling backup checksums. When deselected, a WITH NO_CHECKSUM option is generated with the BACKUP DATABASE statement, explicitly disabling backup checksums. | ||
Stop on checksum error |
This control is only enabled if Perform backup checksums is selected. When selected, a CONTINUE_AFTER_ERROR option is generated with the BACKUP DATABASE statement, ensuring that the backup will continue to run even if errors such as invalid checksums or torn pages. are encountered. When deselected, a STOP_ON_ERROR option is generated with the BACKUP DATABASE statement, instructing the backup to terminate i a page checksum does not verify. | ||
Rewind the tape before unload |
When selected, a WITH REWIND option is generated with the BACKUP DATABASE statement, forcing release and rewind of the tape. When deselected, a WITH NOREWIND option is generated with the BACKUP DATABASE statement, dictating that the tape remain open after the backup completes. | ||
Unload the tape after backup |
This control is only enabled if Rewind the tape before unload is selected. When selected, a WITH UNLOAD option is generated with the BACKUP DATABASE statement, forcing an unload of the tape after rewind. When selected, a WITH NOUNLOAD option is generated with the BACKUP DATABASE statement, leaving the tape loaded after the backup operation completes. | ||
No Recovery and Standby file (Standard SQL Server backup style only) |
These controls correspond to the mutually exclusive STANDBY = UNDO FILE NAME (letting you back up the tail of the log and leave the database in a read-only and standby state) and NO RECOVERY (letting you back up the tail of the log and leave the database in RESTORING state) clauses. | ||
I/O buffers |
Lets you provide a WITH BUFFERCOUNT = value for the BACKUP DATABASE statement. | ||
Max transfer size |
Lets you provide a WITH MAXTRANSFERSIZE = value for the BACKUP DATABASE statement. | ||
Report at each |
Lets you provide a WITH STATS = value for the BACKUP DATABASE statement. | ||
Backup Operation Summary |
Here you indicate whether or not you want to save the operation, and if so, where the file should be saved. Also displayed are the elements of the function you are creating. You can go back to earlier panels if you want to make changes before the job runs. | ||
Action Selection |
Open the backup script in an ISQL editor |
The backup script will be opened into a new ISQL session. NOTE: This option is not available for Sybase backups. | |
Perform the backup now |
The backup will be executed immediately. | ||
Schedule the backup for later execution |
The backup will be scheduled to run at a specified date/time. | ||
- 4. When ready, click Execute.