When using a clone-backup command in SnapManager (SMSQL), we are not able to restore the most recent logs, because they are not included in the snapshot. Does anyone have any ideas to help get those logs into the snapshot to be restored?
We often restore the previous night's backup. This is done with -restoreLastBackup 0 and -TransLogsToApply 0 parameters.
We desire the ability to restore the most recent transaction logs, which are taken every 15 minutes. This seems to be something that is promised in the product. And let's face it, it would be a pretty terrible product if you couldn't restore your most recent data. So something is amiss.
In order to restore those logs, we simply remove the TransLogsToApply parameter. So the command is as follows:
clone-backup
-Server ProdServer01
-Database MyDatabase
-RestoreLastBackup 0
-TargetServerInstance TestServer01
So the command begins, and mounts a snapshot on TestServer01. But the problem is that the most recent logs are not in that snapshot. So some nasty errors are produced:
Applying transaction log backup – 62…
[C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint\MPDisk001\SMSQL_SnapInfo\VDISK_M&N\LogBackup\LOGINST_ProdServer01\LOGDB__MyDatabase\11-05-2013_14.55.56__MyDatabase.TRB]
Retrying to obtain new log backup path (2)…
[C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint\MPDisk001\SMSQL_SnapInfo\VDISK_M&N\LogBackup\LOGINST_ProdServer01\LOGDB__MyDatabase\11-05-2013_14.55.56__MyDatabase.TRB]
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.
Best Answer
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