Chat now with support
Chat with Support

Toad for SQL Server 7.4 - Installation Guide

SQL Server Objects
Edit Permissions on Objects Databases Tables Create and Alter Views Indexes Programmability Create and Alter Synonyms Security Storage Create and Alter Sequences Table, Database and Server Triggers Management Agent

Back Up Databases

Back Up Databases

You can back up a version of your database to a tape or disk to ensure that your data is secure from hardware or software failure and user errors. You can also create a copy of the database on another server.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To back up a database

  1. Right-click a database from the Databases node in the Object Explorer and select Backup Database.

  2. Complete the General page. Review the following for additional information:

    Source

    Description

    Backup component

    Select whether to back up the entire database, or only files and filegroups in the database.

    Backup Set

    Description

    Backup set expires

    Select the number of days before the backup expires or select a specific date for expiration.

    Backup with LiteSpeed

    Description

    Compression level

    Select 0 for no compression or 1-11 to compress the file.

    Note: Higher compression levels result in smaller backup files, but they also take longer to complete.

    Secure backup device with encryption

    Select this checkbox to encrypt the backup. Then, select the encryption level and enter the encryption password.
    Advanced Options Description

    Priority

    Select the priority of the back up over other transactions or processes running on the same server.

    Backup threads

    Enter the number of threads used for the back up. The default is n-1 threads, where n is the number of processors.

    Logging level

    Select one of the following options:

    • None—LiteSpeed does not log backup activity.
    • Errors only—LiteSpeed logs errors during the backup process.
    • Verbose—LiteSpeed logs all backup activity. Verbose logging can cause large log files.
    • Verbose on failure—LiteSpeed creates verbose logs and only saves them if the back up fails. If it succeeds, LiteSpeed does not save the log.

    Note: Selecting Verbose on failure can significantly reduce the troubleshooting process if the activity fails.

    Optimize Object Level Recovery speed

    Select to create an index of objects in the backup file. This option is only available for LiteSpeed backups.

    Note: Before you can recover objects or execute a SELECT statement, you must read the backup file to create an index of restorable objects. If you select this option, LiteSpeed uses the index in the backup file to read the backup file, which makes the object level recovery process much faster.

    Throttle

    Enter the maximum percentage of CPU that LiteSpeed can use for the process.

    Buffer count

    Enter the number of SQL Server buffers available for a LiteSpeed operation.

    Note: These options are only available if LiteSpeed for SQL Server is installed.

    Destination

    Description

    Back up to

    Select whether to back up to a tape or disk. If you select disk, you must specify a directory or device for the backup.

    Attached Files and Directories Tab Description

    Add

    Click this to bundle items with the backup file that may be necessary to restore it. LiteSpeed appends the files to the data section of the backup file, and consequently the attached files are encrypted and compressed with the backup if those options are selected.

    Some examples of when to use this feature include the following:

    • Extended stored procedures packaged in DLLs may exist outside of the database, but you may need them to restore it. These external DLLs can be attached to the backup file.
    • If you are going to upgrade a database application, you can back up the database along with the current version of the application. If anything goes wrong during the upgrade, you can restore both the database and the related application files.
    • When sending a backup file offsite to another person (such as an auditor or consultant), you can attach schema diagrams, username and password lists, instructions, and other documentation.
  3. Complete the Options page.

    Overwrite Description

    Back up to existing media

    Select True to back up the database using an existing media set.

    Check media name

    Select True to verify the existing media set name and expiration date before backing up the current database. You can specify a name for the media set you want to check in the field.

    New media

    Select True to back up the database to a new media set and erase any existing backup sets. You can specify a name and description for the new media set in the fields.

    Tape drive

    Description

    Unload tape

    If you selected a tape drive as the backup device, select True to unload the tape when the backup completes.

    Rewind tape

    If you selected the unload tape checkbox, select True to rewind the tape before unloading it.

 

Related Topics

 

Add Backup Devices

To add a backup device

  1. Select a database under the Databases node in the Object Explorer.

  2. Click .

  3. Click Add under Destination.

  4. Select the Backup device option.

  5. Click New Device and specify the device name and destination.

 

Related Topics

Restore Databases

This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

Troubleshooting: For a valid script generation, make sure that the file paths to the destination database are correct.

To restore a database

  1. Select the Databases node in the Object Explorer.
  2. Right-click a database and select Restore Database.

    Default backup database is selected automatically.

    Tip: To load backup from File or external Device without specifying database (for example, if you want to create new database from a backup), right-click the Databases node and select Restore Database.

  3. Complete the Destination page. Review the following for additional information:

    Destination page

    Description

    Set database single user mode

    Select True to close other connections to selected Database.

    Replace existing database

    Select True to replace the current database with the restored database, overwriting any data entered since the last restore.

    Undo file

    Specify a location for a rollback file if you want to undo the restore operation.

    Note: This option is only available if you selected Restore with standby as the recovery state.

    Select the location of the database files and alter it if necessary. Click to change a directory. Review the following for additional information:

    • Destination Database Path—Defines database files location in current SQL Server instance where the backup will be restored.
    • Backup Path—Defines location of original database files that were used for backup using RESTORE HEADERONLY.

      Note: Be careful especially when restoring database from backup that was originally created on another SQL Server instance. Backup Path is stored in backup file and is defined using RESTORE HEADERONLY. The path may be invalid for the target database where you intend to restore a backup.

    • SQL Server Defaults—Defines default location for the database files in the current SQL Server instance.

    Select Generate File Names to generate new database file names based on the target database name.

  4. Complete the Options page. Review the following for additional information:

    Options page

    Description

    Logging Level

    Select one of the following options:

    • None—LiteSpeed does not log backup activity.
    • Errors only—LiteSpeed logs errors during the backup process.
    • Verbose—LiteSpeed logs all backup activity. Verbose logging can cause large log files.
    • Verbose on failure—LiteSpeed creates verbose logs and only saves them if the back up fails. If it succeeds, LiteSpeed does not save the log.

    Notes:

    • Selecting Verbose on failure can significantly reduce the troubleshooting process if the activity fails.
    • These options are only available if LiteSpeed™ for SQL Server® is installed. See LiteSpeed for SQL Server web page for more information.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating