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:
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:
|
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. |
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.
Troubleshooting: For a valid script generation, make sure that the file paths to the destination database are correct.
To restore a database
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:
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.
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:
Notes:
|
© ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center