-
제목
Is a full SQL Server database backup enough for a point in time restore? -
설명
A SQL Server transaction log backup isn’t needed for a point in time restore!
Is this statement true? -
해결 방안
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
7RESTORE 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'
GOHere 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.
Find more information on the SQL Server transaction log topic in the 10 most important SQL Server transaction log myths article.