Restoring to a Point In Time
A point-in-time restore can be done using one of the following:
- A specific date/time within the transaction log backup
- A specific transaction name that was inserted in the log
- An LSN
ONLINE RESTORES
Online restores are new to SQL Server 2005. They allow a filegroup, a file, or a specific
page within a file to be restored while the rest of the database is online. The file or filegroup
that is being restored to must be offline during the duration of the online restore.
TIP
You should take a full backup of a database immediately before taking a read-only file
offline. This simplifies the online restore process and eliminates the need to apply a
bunch of transaction log backups prior to the online restore. This applies only to databases
that are in full or bulk-logged recovery.
The following example demonstrates how to take a read-only file offline:
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = ‘AdventureWorks_ReadOnlyData’, OFFLINE)
When the file is offline, you can perform a restore to that file without affecting the rest of
the database. The following example shows an example of an online restore of a readonly
file to the AdventureWorks database:
RESTORE DATABASE [AdventureWorks]
FILE = N’AdventureWorks_ReadOnlyData’
FROM DISK = N’C:\mssql2005\backup\AdventureWorks_ReadOnlyData.bak’
WITH FILE = 1, NOUNLOAD, STATS = 10, RECOVERY
Tags: Online Restore