SMSQL restore with SnapVault backups (not using Protection Manager)


My customer is using SMSQL and SnapVault'ing their database and snapinfo LUNs, but (for reasons I will not go into here) they are not doing this via the Protection Manager integration with SMSQL....they just have a script that vaults the SMSQL snapshots over to the SnapVault secondary.  Are there any good documents out there that cover the procedures for doing restores from SnapVault secondaries when not using Protection Manager?  The two main use cases that I can think of are:

1.  If they need to restore all the data for a given SQL instance from secondary storage back to primary storage.

2.  If they need to restore a single database (assuming they have multiple small databases per LUN) from a snapshot on the SnapVault secondary, without disrupting the databases still running off primary storage.

The other caveat for this customer environment is that they are doing their transaction log management outside of SMQL, since they do some SQL log shipping, so we need to leave the databases open for additional log replay (which their DBAs will take care of) after a restore.  Any pointers would be much appreciated!  Thanks,


Re: SMSQL restore with SnapVault backups (not using Protection Manager)

I've been doing a bit more research on this, and I see that you can use the "Restore from Unmanaged Media" option in the SMSQL restore wizard to restore from backup sets that have been moved off primary storage (which is done via SnapVault, in my case).  This looks good, until I hit the requirement that the LUNs need to be reconnected back on their original drive letters in order to accomplish a restore from unmanaged media.  If this is a requirement, how could a DBA ever restore a single database from a backup on unmanaged media, without disrupting all the other production databases that may be sharing the LUNs with the database requiring a restore?  Am I missing something, or is SMSQL just not smart enough to handle this use case?  Thanks for any pointers.


Re: SMSQL restore with SnapVault backups (not using Protection Manager)

Hi this is what we do with our production systems and I will skip over why we do not use PM...

We have 3 sites with Snapshots and snapmirror used to protect the main systems in the 1st and 2nd data centesr.  We use snapvault for archive data and restore this to a 3rd SQL server in the 3rd data centre.  If we ever had a VERY rainy day and lost the 2 main data centers we would have to copy the snapvault data (luns) back to the production site via vol copy (snapmirror) or NDMP.

So in our environment, snapshot and snapmirror are used for system restores and snapvault is used for long term archive and is used to restore data and normally only a sub set of the whole.

Hope it helps


Re: SMSQL restore with SnapVault backups (not using Protection Manager)

Thanks Brandon. But how do you restore a "sub set of the whole" with SMSQL, given that SMSQL requires the LUNs to be put back in place at the same location as they were located when the backup was taken? Or are you doing individual database restores on a different SQL server than the original SQL Server? Thanks!

Re: SMSQL restore with SnapVault backups (not using Protection Manager)

I'd like to know as well.

Situation here: production -> snapvault -> tape.

We restore LUNs from tape to the backup filer.

But then SMSQL documentation states the LUNs need yp be mounted as original drive letter.

This doesn't work when we want to restore only 1 database.



Re: SMSQL restore with SnapVault backups (not using Protection Manager)

Using “Restore from unmanaged media” is the right procedure to do restore from snapvault backup if not using PM. If there are more than one database sharing LUN(s), you only want to restore some of them, you should reconnect the LUN to different location using different drive letter, then copy the data files and log files of SQL Server database back to its original location, then perform restore from unmanaged media.

In any case, “restore from unmanaged media” means the user will need to manually restore the database files by whatever means. The user should know how to avoid overwritten other production databases by using copy file method, instead of restoring whole LUN.

When entering SnapInfo path, you should use the path such as …\SMSQL_SnapInfo\VDISK__L&M\FG__\02-04-2011_10.00.07\ to specify a particular backup.