Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
i've never had a good experience with netapp or snapmanager and would recommend sticking with a native backup strategy unless you used the other features such as mirroring and cloning. the "promised features" were not flexible enough to even do the DR things that we needed to do. we engaged professional services on many occasions and were told that it was not possible or was not a good use case for smsql. please do yourself a favor and stick with a native DR strategy. i promise you that you wont regret it. hopefully you're not in a DR situation. –  swasheck Nov 5 '13 at 21:53
    
We're not in a DR situation thankfully, but having trouble preparing for one. –  GlutenFreeSQL Nov 5 '13 at 22:12
2  
Less rant-y version: The specific logs in their correct chain sequence have to be available. There was some NetApp voodoo that would need to be done in order to find these logs (or you'd need to allocate a LOT of storage to your netapp volume). In the end, sticking with native backups handled by the storage was the best thing for us. We were always able to count on the backups being there –  swasheck Nov 5 '13 at 22:20
2  
That's what I love about native backup. You just store the files, and you always know (notwithstanding corruption) that you can restore. I never trust snapshots for anything other than a quick test in dev/QA. –  Max Vernon Nov 5 '13 at 22:28

1 Answer 1

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.

  1. 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

  2. Copy all relevant logs from Production to Test. The only ones needed were those taken after the full backup.

  3. Use SQL below to query file system where you copied the files. This will generate “RESTORE LOG” statements

  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

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

share|improve this answer
1  
I appreciate your dogged pursuit in answering this question, but it seems like a lot of workarounds for a product that's supposed to work –  swasheck Nov 7 '13 at 17:24
1  
You are right, it's more of a workaround than fixing the root problem. But it did relive some stress, so hopefully it can help others. If a SnapManager upgrade solves the issue, I will come back and update this. –  GlutenFreeSQL Nov 8 '13 at 20:22
    
Awesome! I admire your courage :) –  swasheck Nov 8 '13 at 20:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.