Restore all transaction log backups in a directory (TRB, TRN)

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:
-Server Prod01
-Database MyDatabase
-RestoreLastBackup 0
-TransLogsToApply 0
-TargetServerInstance Test01
-RecoverDatabase $false

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;

'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'

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.

This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s