Data Backup and Recovery Discussions

Highlighted

Any SQL/SMSQL gurus can answer this restore query?

SnapManager for SQL Full backups are taken and then SnapVaulted : 3 separate vol/lun are designated.


1. data [mdf]
2. log [ldf]
3. snapinfo

 

Out of these three, only data volume is available, 2 & 3 is gone.


Problem: When trying to import mdf in sql management studio, its complaining ldf are missing.

 

Any idea = Why is it asking for LDF files when this was a full backup model. Does that mean, the dirty buffers [or midified data] were still in cache basically in the log file, when the DB was snapshotted ? But, should that have not comitted considering full backup model.

 

many thanks in advance.
-Ashwin

7 REPLIES 7

Re: Any SQL/SMSQL gurus can answer this restore query?

Hi

 

can you provide the full error message and versions ?

 

are you sure it's not an option you have in the wizard that just allow you to append logs you have post the backups, rather an actual requirement to have such logs?

 

 

as you said. the backups should be full.

 

Gidi

Gidi Marcus (Linkedin) - Storage and Microsoft technologies consultant - Hydro IT LTD - UK
Highlighted

Re: Any SQL/SMSQL gurus can answer this restore query?

Hi,

 

Thanks for the response, appreciated.

 

Infrascture is moved from SnapManager suite to another backup product. All we have is snavaulted arhive, without log volumes [LDFs].

 

No Snapmanager, No log volumes. Only thing that can be mounted is Vol/LUN containing Data [MDF].

 

Error message :

File activation failure. The physical file name "L:\SQL\Log01\MIP_log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Could not open new database 'MIP'. CREATE DATABASE is aborted.

Highlighted

Re: Any SQL/SMSQL gurus can answer this restore query?

Hi

 

just to double check. you are mounting it from a snapshot created by SMSQL - not one that created by SV or browsing directly to the volume "active" file system, right?

 

i can't find a reason for you to see this message if the backup taken properly (search for it in the context of proper backup don't give much) and the VDI spec netapp use is suppose to freeze the IO so no in-flight transactions are expected https://technet.microsoft.com/en-us/library/cc966520.aspx 

 

 

let's face it. if that all you have - you need to force mount whatever you have... (with that or these 3)

you anyway lost some data that appended after the backup. so if you manage to mount it you will have to handle the consistency issues (if exists) manually.

 

G

Gidi Marcus (Linkedin) - Storage and Microsoft technologies consultant - Hydro IT LTD - UK
Highlighted

Re: Any SQL/SMSQL gurus can answer this restore query?

Hi G,

 

Thanks. I wish I could provide more information but that's all I have come to know, rest I am presuming.

 

1. SMSQL was the Application backup software

2. Each LUN was housed in a qtree

3. Vault was in place for the archival purpose.

 

So I am assuming, we have productions LUNs, mdf, ldf & snapinfo. SMSQL Full backup weekly - 

1. Created smsql snap

2. snap were vaulted [ Here is the question that you have raised - Is it really SMSQL snap, or Vault generated snap, snap that are available are in this format - sv_weekly.xx., does that indicate they are actually not-consistent ?, can I know how does vault SMSQL snap naming conventions looks like]

3. We NDMPCOPIED the LUN inside to another Volume

4. Presented the LUN to WIN Host, we found out this only contains - MDFs for various databases.

5. We tried to import the MDF and it failed.

Highlighted

Re: Any SQL/SMSQL gurus can answer this restore query?

hi

 

You NDMPCOPYed it from path like /vol/vol_name_/qtree/lun.lun ("live" file system) ? or from /vol/vol_name_/.snapshot/snapshot_name/qtree/lun.lun (snapshot)?

 

As for the convention. i don't have a system here with SMSQL to check if the naming could be legit for consistent SMSQL backup behind a snapvault.

From what i can see in the docs.  the only way to integrate SMSQL with snapvault is via DFM. did you had it in the organisation ? is that server still alive and can show the job that created that snapshot?

https://library.netapp.com/ecmdocs/ECMP1217281/html/GUID-C89B2358-05FC-4C4A-94D6-A0480F65D072.html

 

 

because. the name you have dosen't look like the names my DFM generating (either in format of "2018-03-25_2334+0100_daily_DS_" or the original snapshot name) i tend to think what you have there is very likely non-consistent snapshot from independent  scheduled snapvault.

is this volume still have it's snapvault config? (command: "snapvault snap sched"). if that matches to your snapshot name. we can very likely assume you have a non consistent one..

 

Gidi

Gidi Marcus (Linkedin) - Storage and Microsoft technologies consultant - Hydro IT LTD - UK

View solution in original post

Highlighted

Re: Any SQL/SMSQL gurus can answer this restore query?

Hi G,

 

Thanks for your prompy replies. 

 

No more production LUNs, they have been migrated to cDOT environment long back. Basically, to keep the historic data vault archive was kept secured. There is no source for this Vaulted arhived, it's been removed. All we have is a volume that has weeklys snapvault snapshots. I am pretty sure now that these are self generated snapshots. Hence, the snapshot is not application consistent. That's the reason why SQL is asking for LDFs.

 

I had NDMPCOPIED the LUN from a snapshot to the another volume.

 

I guess the bottom line is - Had it been production LUN with SMSQL snaps, MDF alone was enough. However, as this is a vaulted self generated non-application aware snapshot, the data is inconsitent sate and therefore we will need LOGs [ldfs]. I guess, had they been tested before removing the logs volume, they would have realized it and would have never removed it.

 

Is there anything you would like to add here ?

 

Thanks,

Ashwin

Highlighted

Re: Any SQL/SMSQL gurus can answer this restore query?

yep. seems right.

Gidi Marcus (Linkedin) - Storage and Microsoft technologies consultant - Hydro IT LTD - UK
NetApp Insights To Action
All Community Forums