This myth comes from using the RESTORE command with STOPAT clause to restore from a full database backup. The STOPAT clause specifies a point in time for the RESTORE LOG command, and it works well when it’s used with a transaction log backup. The fact that it can be used with a full database backup makes us believe that transaction log backups are not needed to recover to a specific point in time.
Which cannot be further from the truth, since it is crucial we have full database backup chain and its not enough to have only full database backup to recover everything that is lost since full backup is not doing backup of all transactions from the online transaction logs.
Regularly created transaction log backups are mandatory here to ensure no data is lost.
An example of T-SQL code for restoring the AdventureWorks database to December 31st 2013 10:59 PM
1 2 3 4 5 6 7 | RESTORE DATABASE AdventureWorks FROM DISK = 'D:\AdventureWorks.bak' WITH NORECOVERY GO RESTORE LOG AdventureWorks FROM DISK = 'D:\AdventureWorks.bak' WITH RECOVERY, STOPAT = 'Dec 31, 2013 10:59:00 PM' GO |
Here we see that the SQL Server database cannot be restored to a point in time, due to a lack of transaction log backups, and SQL Server doesn’t clearly identify the problem, since it allows us to use the STOPAT clause without a transaction log backup specified.
© ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center