Limitations

You can use SQL Object Level Recovery Native to recover various object types from SQL Server Backup files, with some limitations. For example, dependencies between objects are not handled automatically, and some features of SQL Server tables are not supported. The following sections provide more detailed information about these limitations:

Existing objects
Object dependencies
Supported backup types
Supported object types
Supported CREATE TABLE arguments

Refer to your SQL Server documentation for detailed information about specific object types, and table arguments.

For more complex recovery scenarios, you should consider using Red Gate SQL Compare and SQL Data Compare. These enable you to compare the contents (object schema, and data) of SQL Server backup files (.bak) with a live database, and then synchronize the database with the backup file contents while maintaining object dependencies.

Existing objects

Objects that already exist in the destination database will not be modified or overwritten by SQL Object Level Recovery Native. Attempting to recover such objects results in an error.

It is usually safer to recover an object to a test or staging database first, and then transfer the object to its final destination database manually. If you want to recover an object directly from a backup file to its final destination database, you will have to drop the object first. Make sure you have a recent valid backup of the object before you drop it.

Object dependencies

Objects you attempt to recover may have dependencies on other objects in the destination database. For example, a view may refer to several tables; successful recovery of the view depends on these tables being present in the destination database.

SQL Object Level Recovery Native does not attempt to resolve dependencies automatically. To avoid dependency errors, you may need to recover multiple dependent objects together.

If you have selected objects of more than one type, they are recovered in the following order:

SCHEMA
TYPE (user defined type)
XML SCHEMA COLLECTION
FUNCTION
TABLE
VIEW
PROCEDURE (stored procedure)

Recovering objects in this order reduces the possibility of failures caused by dependencies on missing objects.

Supported backup types

SQL Object Level Recovery Native supports:

full backups
differential backups
If you need to recover objects from a differential backup, you will also need to provide the associated full backup.

You can use SQL Object Level Recovery Native with full backups and differential backups. To recover objects from a differential backup, you will also need to provide the associated full backup.

SQL Object Level Recovery Native does not support:

filegroup backups
transaction log backups
backups from databases that use Transparent Data Encryption (TDE)
SQL Backup (.sqb) backups
If you need to restore an object from a SQL Backup backup file, you can do so using SQL Backup Pro (from version 6.2). For more information, see the SQL Backup Product page.
restoring to a version of SQL Server that is earlier than the version of SQL Server used to create the backup

Supported object types

Object types marked can be recovered from SQL Server .bak files. Other object types are not supported.

Object type	Supported
ASSEMBLY
ASYMMETRIC KEY
CERTIFICATE
CONTRACT
DEFAULT
EVENT NOTIFICATION
FULLTEXT CATALOG
FULLTEXT STOPLIST
FUNCTION
INDEX
MESSAGE TYPE
PARTITION FUNCTION
PARTITION SCHEME
PROCEDURE (stored procedure)
QUEUE
REMOTE SERVICE BINDING
ROLE
ROUTE
RULE
SCHEMA
SERVICE
SYMMETRIC KEY
SYNONYM
TABLE
TRIGGER
TYPE (user defined type)
USER
VIEW
XML SCHEMA COLLECTION

Supported CREATE TABLE arguments

CREATE TABLE arguments marked are supported. All other CREATE TABLE arguments are ignored. For example, if the table to be recovered includes a FOREIGN KEY ... REFERENCES argument, this will not be created in the recovered table.

Argument	Supported
ALLOW_PAGE_LOCKS
ALLOW_ROW_LOCKS
CLUSTERED
COLLATE
computed_column_expression
CONSTRAINT
CONTENT
DATA_COMPRESSION
DEFAULT
DOCUMENT
FILESTREAM_ON
FOREIGN KEY ... REFERENCES
IDENTITY
IGNORE_DUP_KEY
NONCLUSTERED
NOT FOR REPLICATION
NULL
ON filegroup
ON partition scheme
ON DELETE
ON UPDATE
PAD_INDEX
PERSISTED
PRIMARY KEY
RANGE
ROWGUIDCOL
SPARSE
STATISTICS_NORECOMPUTE
TEXTIMAGE_ON
UNIQUE
WITH FILLFACTOR
XML COLUMN_SET FOR ALL_SPARSE_COLUMNS