Subscribe

Using "crash consistent" snapshots for MSSQL backup/recovery?

[ Edited ]

Oracle has the ability to use crash consistent snapshots as valid backups (TR-3858) which means that entire volumes of Oracle databases can be easily backed up and restored with little intrusion into the guest OS at the time of the snapshot. 

I am doing my due diligence on MSSQL and was wondering if there is anything that can be done to provide the same capability for MSSQL.  While we do have the SMSQL software, we are looking for an easier methodology to deploy, since we have literally hundreds of MSSQL databases.

The details:

Running NFS datastores to VMware

All database/OS files are contained within VMDKs on the same volumes (no consistency groups needed)

Various MSSQL versions...

Average database size ~300Gb

Thanks!

-Fitz

Re: Using "crash consistent" snapshots for MSSQL backup/recovery?

BTW, I spoke with MS SQL Development, and, currently, there is no way of doing this, nor is there any means of doing this on the roadmap.

Re: Using "crash consistent" snapshots for MSSQL backup/recovery?

Hi Eric ,

I would like to inform you that SQL Server is application consistent. SMSQL does generate application consistent snapshots.

Regards,

Abhishek

Re: Using "crash consistent" snapshots for MSSQL backup/recovery?

I apologize - it seems that I left out a few details:  While we can get a crash consistent backup at any time via snapshots, when you restore MSSQL from a crash consistent backup (snapshot), the database opens without any chance of applying transaction logs.  If the ability to apply logs is required, the backup has to be application consistent (e.g., using.SnapManager for SQL).  Oracle and DB2 do *not* have this issue.

Sorry I omitted the important details.

-Fitz

Re: Using "crash consistent" snapshots for MSSQL backup/recovery?

Providing you will be lucky enough to recover from a crash consistent MS SQL snapshot - it doesn't have the same 4k block granularity / alignment as Oracle.