Controlling Creation of Added Files During Roll Forward
Generally, changes to files and filegroups after a full backup are handled transparently. If a file is added to a database after the last backup of the filegroup, when you restore the database, the easiest approach is to allow for restore to automatically re-create all files in the restored database. However, undamaged, read-only files might be re-created unnecessarily. When you use restore to repair a known, isolated problem, you can optimize restore performance by restoring only files in which the problem appears.
Note
If you restore a backup that contains files that the SQL Server Database Engine expects will be dropped, the SQL Server Database Engine skips the data copy and redo phases for those files. For information about these phases, see Understanding How Restore and Recovery of Backups Work in SQL Server.
Whether restore creates a file depends on whether you specify files or pages in a RESTORE DATABASE statement, as follows:
- If specific files or pages are not specified
This is the typical scenario. On encountering a log entry that adds a file to the database, restore automatically creates the file and adds it to the roll forward set. The exact behavior depends on whether you specify any filegroups in your RESTORE DATABASE statement. If you do not specify any filegroup, all new files are created and added to the roll forward set. If you specify any filegroups, a new file is created and added to the roll forward set only if the file belongs to a specified filegroup.
Best practice: For most restores, avoid listing specific files, to allow for any files that were created by logged transactions to be re-created automatically. - Specific files or pages are specified:
Listing specific files or pages in the RESTORE DATABASE statement defines the exact roll forward set. When you list files or pages, restore follows your instructions exactly and restores only these items.
Unlisted files are assumed to be fine and are not created from the log. Therefore, when restoring a log backup that contains an add-file operation, you must specify the name of the added file in your RESTORE statement.
The rest of this topic describes how to identify files that contain add-file operations and how to make sure that these files re-created during your restore sequence.
Identifying Which Log File Contains an Add-File Operation
You can identify which log file contains an add-file operation in the following ways:
- You can use the file-create LSN (create_lsn). This is available in both the sys.database_files and sys.master_files catalog views.
- You can obtain information about the file contents of backups from the backup history tables in msdb or by using RESTORE FILELISTONLY. For more information, see RESTORE FILELISTONLY (Transact-SQL).
Restoring Files or Pages Without a Full Backup
You can specify a file list, page list or a filegroup in a log or differential restore. This capability lets you restore files or pages for which no full database backup exists. Restoring a file or page from a differential or log backup is an exception to the rule that a restore sequence must start with a full backup.
The basic syntax for restoring from a differential file backup is:
RESTORE DATABASE database_name <file_filegroup_page_list> FROM <differential backup>
The basic syntax for restoring from a log backup is:
RESTORE LOG database_name <file_filegroup_page_list> FROM <log backup>
See Also
Concepts
Understanding How Restore and Recovery of Backups Work in SQL Server
Performing File Restores (Full Recovery Model)
Performing File Restores (Simple Recovery Model)
Restoring Renamed Files and Filegroups