SnapManager point in time restore problem
This is still a problem, so I’m not completely satisfied.  The IT department will upgrade to SnapManager 7.0 soon (we have 5.2 at this time), 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.


Clone last night’s backup with no recovery
Command to run in SnapManager Powershell:
clone-backup
-Server Prod01
-Database MyDatabase
-RestoreLastBackup 0
-TransLogsToApply 0
-TargetServerInstance Test01
-RecoverDatabase $false
Copy all relevant logs from Production to Test. The only ones needed were those taken after the full backup.
Use SQL below to query file system where you copied the files. This will generate “RESTORE LOG” statements
Copy generated SQL to a new window and execute. This should run a few minutes.
Run RESTORE DATABASE [ MyDatabase__Clone] WITH RECOVERY to make the database available to query


SQL:
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 [ MyDatabase__Clone] FROM DISK = ”B:\LogRestoreTest\’ + subdirectory + ”’ WITH NORECOVERY;’
FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = ‘.TRB’
ORDER BY id;

This post was helpful in knowing how to use master.sys.xp_dirtree:

http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html