Otdfbt

I have a MSSQL Express server running on a VM. Hosting provider I use has an option to create drive snapshots. The server is running all the time and is not stopped during the drive backup. Backup has no special functionality related to MSSQL. If I keep my data files on such drive and use snapshots in case of disaster recovery, are there any risks that database may not be restored?

Backup has no special functionality related to MSSQL.

Typically server backup programs will coordinate with SQL Server through the Windows Volume Shadow Copy Service (VSS) to ensure that SQL Server places the database files in a consistent state before volume snapshots are taken. At a minimum backup programs should create backups that are consistent on a per-volume basis.

However, even in an enterprise environment where you have trusted professionals performing server backups using a configuration you can verify, very few DBAs would actually rely on server backups. Remember backups alone are irrelevant. Restores are what you actually care about.

So how are you going to perform a restore if you rely on server backups? Not easily, that's for sure.

So take backups. Then download them regularly from your hoster, or upload them to a cloud storage provider, eg:

• Sign up for an Azure account


• Provision a storage account (LRS + Cool default tier)


• Use SQL Server's Backup to URL feature to take database backups directly to your storage account.

While you should certainly take note of the points that David Browne makes I have used the drive snapshot approach for backups and have successfully restored small SQL databases from them. But see the caveats at the end of my answer - I would be very wary of attempting this on a large database.

Most backup software of this type will allow you to mount the backup as a virtual drive. Once mounted you can just copy back the MDF file. You can either simply stop the SQL server service, replace the original file(s) then restart it, or attach the MDF file as a new database and copy back selected data from it.

But:

I have only ever used this for small databases set to simple logging. In this context small would mean the sort of size SQLExpress supports so this would apply to your system. When I'm using this sort of backup I use simple logging because the backup software we use takes a snapshot every 15 minutes so it gives me a pretty good granularity without needing full logging.

I use this approach mainly for the sort of small SQL databases used by apps. Lots of end user apps use SQLExpress as their data store these days, just as some years ago lots of apps used Access MDB files, and in these circumstances I tend to view the database in the same way I'd view any other data file. You don't say what your database is used for so I can't advise you on how well this would work for you. The usual advice for backups applies: test your backups regularly to make sure you can restore from them.

I agree with all the points mentioned above by David Browne and would like to add one more pertinent point here. Since you have not specified recovery model of database, I assume that it is Full recovery mode. Which means with each transaction log size will keep on growing until it occupies the whole disk(unless you have capped the size of Log auto increment).

Once you take log backup, following happens:

A transaction log backup allows you to backup the active part of the
transaction log. So after you issue a "Full" or "Differential" backup
the transaction log backup will have any transactions that were
created after those other backups completed. After the transaction
log backup is issued, the space within the transaction log can be
reused for other processes. If a transaction log backup is not taken,
the transaction log will continue to grow.

Taking snapshot of disk will never clear the log inside the SQL server and moreover, you also have feature of compressed backup, which can be useful for keeping the size of backup really small. In case of disaster, using Full, differential and log backup you can achieve point in time recovery.

One more thing to add here - Generally DBAs keep backup on different storage and not on the same storage where Data or log file resides. So, in case your disk of data or log file crashes, you are still safe because you have backups available on some other storage.

I hope above helps.