How long has this worked, and why didn’t anyone tell me?
A typical restore statement from a FULL backup and a LOG backup:
USE [master]
RESTORE DATABASE [XE_demo]
FROM DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 25, REPLACE
RESTORE LOG [XE_demo]
FROM DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn'
WITH FILE = 1, NOUNLOAD, STATS = 25
GO
Results:
100 percent processed. Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1. Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1. RESTORE DATABASE successfully processed 354 pages in 0.016 seconds (172.393 MB/sec). 30 percent processed. 60 percent processed. 90 percent processed. 100 percent processed. Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1. Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1. RESTORE LOG successfully processed 27 pages in 0.011 seconds (18.909 MB/sec). Completion time: 2022-05-11T09:26:44.3023396-05:00
Change the Restore Log to Restore Database:
USE [master]
RESTORE DATABASE [XE_demo]
FROM DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 25, REPLACE
RESTORE DATABASE [XE_demo]
FROM DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn'
WITH FILE = 1, NOUNLOAD, STATS = 25
GO
Results:
100 percent processed. Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1. Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1. RESTORE DATABASE successfully processed 354 pages in 0.015 seconds (183.886 MB/sec). 30 percent processed. 60 percent processed. 90 percent processed. 100 percent processed. Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1. Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1. RESTORE LOG successfully processed 27 pages in 0.006 seconds (34.667 MB/sec). Completion time: 2022-05-11T09:30:42.0225244-05:00
I had no idea the engine would determine the type of backup file and apply it appropriately. I knew it worked for Differential backups, but not Log.
20+ years and I still find new (to me) things every day. Technically Jon (t) found it but hey…we’re a team here 🙂
Thanks for reading!
