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
-
Right-click a database from the Databases node in the Object Explorer and select Backup Database.
-
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.
-
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.
Add Backup Devices
To add a backup device
-
Select a database under the Databases node in the Object Explorer.
-
Click
.
-
Click Add under Destination.
-
Select the Backup device option.
-
Click New Device and specify the device name and destination.
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
- Select the Databases node in the Object Explorer.
-
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.
-
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.
-
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.