Database Reference
In-Depth Information
Recover to a marked transaction use
WITH
STOPATMARK
or
WITH
STOPBEFOREMARK
clause,
assume that application code consist the naming of marked transactions using
BEGIN
TRANSACTION
<Name>
WITH
MARK
.
1.
To perform marked transactions recovery, use the following TSQL statement:
RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE = 14,
RECOVERY,
STOPATMARK = 'StockPricesat1500';
2.
Now, let us work on recovering the transaction to a particular
Log
Sequence
Number
(LSN),
as follows:
--RESTORE the transaction log until a particular LSN
RESTORE LOG AdventureWorks2008R2 FROM DISK = 'G:\backups\Tlogs\
AdventureWorks2008R2_log.bak'
WITH STOPATMARK = 'lsn:12000000010000038'
GO
3. A
BULK-LOGGED
recovery model will work as an adjunct of a full recovery model that
permits high-performance bulk operations by reducing log space usage with minimal
logging for most bulk operations. The point-in-time recovery is not supported, but it
can help to recover to the end of any backup. Now, we are at the point of managing
backup media where the backup plans include provisions for managing backup
media.
4.
Prepare a schedule for overwriting backup media and use essential backup media. A
backup device can be a disk, tape, or physical backup device that is provided by the
operating system. To backup to a backup-file, perform the following TSQL steps:
--BACKUP to a specific physical location or default backup
location
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'G:\Backups\DB_Backups\AdventureWorks2008R2.bak';
BACKUP DATABASE AdventureWorks2008R2
TO DISK='AdventureWorks2008R2.bak';
--The default backup directory will be
--C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\
MSSQL\Backup or
--user defined folder which is specific during the setup.
--BACKUP to a network file location using UNC (Universal Naming
Convention)
BACKUP DATABASE AdventureWorks2008R2
TO DISK = '\\FileBackupServer\Backup\DB_backups\
AdventureWorks2008R2Data.Bak';
Search WWH ::
Custom Search