I think Standby option during Restore is very good option especially for testing backups.Testing backup by doing a full restore is always recommended. Of course, the RTO’s and RPO’s should define the backup schedule but it is typical to take several log,differential backups between Full backups.
So, while restoring your databases for validation, if you do not use “STANDBY” option, you will have to restore all your backups up to point in time, every single time.
The StandBy option gives the ability to restore the next successive backup on top of the existing database without having to restore all the backups. You will be able to apply over the existing one as the database will be in Read-Only Mode.
When using STANDBY option,there will be a file created during this process and typically contains all the in-flight(uncommitted) transactions since the earliest active transaction when log backup was started and when the next backup is applied over the database, it will check with the transactions in the standby file and either apply or rollback transactions accordingly.
Create Database CDC
Alter Database CDC Set Recovery FULL
Go
Use [CDC]
Create Table Stest(sno int identity(1,1),Sname varchar(20) default ‘Pollo’)
Insert into Stest(sname) Default values
Backup Database CDC to Disk =’E:\SSMS2012\CDCFB.bak’ with Init
Insert into Stest(sname) Default values
Backup Log CDC to Disk =’E:\SSMS2012\CDC_Log1.bak’ with Init
Insert into Stest(sname) Default values
Backup Database CDC to Disk =’E:\SSMS2012\CDCFB2.bak’ with Init
RESTORE DATABASE [CDC2] FROM DISK = N’E:\SSMS2012\CDCFB.bak’ WITH FILE = 1,
MOVE N’CDC’ TO N’E:\MSSSQL11.EXP2012\CDC2.mdf’,
MOVE N’CDC_log’ TO N’E:\MSSSQL11.EXP2012\Log\CDC2_1.ldf’, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [CDC2] FROM DISK = N’E:\SSMS2012\CDC_Log1.bak’
WITH STANDBY = N’E:\MSSSQL11.EXP2012\ROLLBACK_UNDO_CDC_log2.BAK’
RESTORE DATABASE [CDC2] FROM DISK = N’E:\SSMS2012\CDCFB2.bak’
WITH STANDBY = N’E:\MSSSQL11.EXP2012\ROLLBACK_UNDO_CDC2.BAK’
UPDATE: If the purpose of doing the restores is only to validate the backups,then we can leave the databases in ‘no recovery’ mode and still be able to restore other successive backups on top of it. Also, the backup ‘checksums’ are still validated even when leaving the DB in ‘No Recovery’ mode.
However, to run DBCC Checks such as DBCC CHECKDB, the DB is required to be in either online or stand by mode. I got this tip after posting a question on automating test restores on twitter #SQLHelp
SQL Server Repository 