About restoring SQL databases and file groups

Article:HOWTO24044  |  Created: 2010-01-01  |  Updated: 2011-07-21  |  Article URL http://www.symantec.com/docs/HOWTO24044
Article Type
How To

Product(s)

Subject


About restoring SQL databases and file groups

You can restore a database by using one job or using multiple jobs to restore all of the backup sets. The number of jobs you decide on depends on the types of backup jobs that protect the database or the file group. If you use one job to restore a database, select all the backup sets that you want to apply. Include the full backup, any differential backups, and any log backups. Also select the Leave the database ready to use option. Additional transaction logs cannot be restored. Single-job restores and multiple-job restores can both be used in redirected restore operations.

Some restore operations must be completed using separate restore jobs to recover data.

These operations include the following:

  • Restoring a database or a primary filegroup from a filegroup backup. Separate restore jobs must be used to restore the primary filegroup, to restore the rest of the filegroup backup sets, and to restore the transaction logs.

  • Restoring a nonprimary filegroup. After running a Log No Truncate backup, separate restore jobs must be used to restore the missing filegroup from full and differential backups of the filegroups, and to restore the transaction logs.

If you use multiple jobs to restore a database, ensure that you specify the recovery completion state Leave the database nonoperational. Additional transaction logs can be restored for all the jobs except the last one. For the last job, you should specify the recovery completion state Leave the database ready to use. If you use this recovery state, additional transaction logs cannot be restored.

SQL database files contain unused space so that the disk file does not have be grown every time a small amount of data is added to the database. SQL fills the unused space with zeros. When SQL databases are restored, it is not known how much of the file will actually be used by the restored data, so SQL creates the required database files on disk and then fills them with zeros.

With very large databases this process can take several hours to complete. During this time Backup Exec reports that no data is being transferred, and the Byte count field in the Job Monitor view is not updated. When SQL has completed filling the files with zeros, the restore job continues. This occurs for all database restores but is noticeable only on very large databases.

In a mirrored configuration, the primary SQL database cannot be restored. To restore the primary SQL database, you must stop database mirroring of the primary database.

See Restoring data by setting job properties

See Restoring from SQL transaction logs up to a point in time

See Restoring from SQL transaction logs up to a named transaction

See About restoring from SQL filegroup backups

See Redirecting restores for SQL


Legacy ID



id-SF700168034_be2010_adm


Article URL http://www.symantec.com/docs/HOWTO24044


Terms of use for this information are found in Legal Notices