Log Replay on top of SQL database files captured during VSS snapshot failed

Question

I am having a SQL Server and want to perform hourly VSS based backups (will store the VHD separately in a disk) and perform log backups in every 10 min interval. And with the help of backed-up VHD and particular log dump, I want to go to the specific point-in-time.

When I take VSS based backup (using VSS - application consistent snapshot), I can see it trigger database backup for SQL server as well (trace found in SQL Server ERRORLOG).

T-SQL query in trace for database backup during snapshot:

BACKUP DATABASE [sample_db] TO VIRTUAL_DEVICE='{C52EF9FB-9C98-46AF-9981-0496D18F29F6}5' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024
GO

Then I have taken a SQL log backup. And try to replay the logs on top of database files captured during snapshot. SQL server would not let me replay logs over database.

Steps followed for log replay,

1. In Diskmanagement, attach the VHD and take the MDF and LDF files.
2. Attach MDF, LDF files in my SQL Server.
3. Replay the log file by using the below commands,

BACKUP LOG [sample_db] TO DISK = 'c:\sqlLogBackup\tailLog.bak' WITH NORECOVERY;
GO
RESTORE LOG [sample_db] FROM DISK = 'c:\sqlLogBackup\log001.bak' WITH NORECOVERY;
GO
RESTORE DATABASE [sample_db] WITH RECOVERY;
GO

ERROR - Msg 3456, Level 16, State 1, Line 7 Could not redo log record (34:1080:3), for transaction ID (0:881), on page (1:337), allocation unit 281474978938880, database 'sample_db' (database ID 11). Page: LSN = (34:936:18), allocation unit = 281474978938880, type = 1. Log: OpCode = 2, context 2, PrevPageLSN: (34:1064:18). Restore from a backup of the database, or repair the database. Msg 3013, Level 16, State 1, Line 7

As I said earlier, during VSS backup, I can see the backup command with target as a VIRTUAL_DEVICE. But I can't see any db dump in my machine. Will there any dump will be created? or how to use that during recovery.

Also, regarding log replay error, please let me know what I am missing and how to achieve my requirement.

Thanks.

Answer 1

Attach MDF, LDF files in my SQL Server.

When you attach the database it goes through recovery. There is no option to attach without recovery.

Once it's recovered, you can take a tail log backup to put it into "Recovery Pending" mode, but it's at a later point-in-time than the MDF was before you attached it, and you can't use log backups that cover the LSN at which the database file was attached.

Rolling forward from a snapshot backup is not possible through TSQL, but it's possible using the VDI API for backup vendors.