We had a problem with NetApp SnapManager for SQL 5.2. It would not restore the most recent logs, because it did not copy them as part of the backup set.
It would have this error:
Failed to find the specified log backup file using new log backup path.
Error Code: 0xc00408c4
Unable to locate the specified transaction log backup dump file to restore.
This is still a problem, so I’m not completely satisfied. The IT department will upgrade to SnapManager 7.0 soon, so maybe that will resolve it as well. But I did find a viable way to restore the latest transaction logs. It’s inconvenient, but it works.
1. Clone last night’s backup with no recovery
Command to run in SnapManager Powershell:
2. Copy all relevant logs from Production to Test. The only ones needed were those taken after the full backup.
3. Run the following SQL, and change the path and the STOPAT parameter.
CREATE TABLE #DirectoryTree ( id int IDENTITY(1,1) ,subdirectory nvarchar(512) ,depth int ,isfile bit); INSERT #DirectoryTree (subdirectory,depth,isfile) EXEC master.sys.xp_dirtree 'B:\LogRestoreTest',1,1; SELECT *, 'RESTORE LOG [Admin__Clone] FROM DISK = ''B:\LogRestoreTest\' + subdirectory + ''' WITH NORECOVERY, STOPAT = ''2014-6-25 13:30'';' FROM #DirectoryTree WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.TRB' ORDER BY id;
4. Copy generated SQL to a new window and execute. This should run a few minutes.
5. Run RESTORE DATABASE [ MyDatabase__Clone] WITH RECOVERY to make the database available to query
This post was helpful in knowing how to use master.sys.xp_dirtree.