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 backedup 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,
- In Diskmanagement, attach the VHD and take the MDF and LDF files.
- Attach MDF, LDF files in my SQL Server.
- 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.
Let me know, if you need any further information on this.
Thanks