1

We're currently using AppAssure to back up the SQL servers (2012/2014). It uses VSS snapshots. All databases are in simple recovery mode. Do I need to bother backing up the drive that contains the ldf files or is it enough to just back up the mdf files and let the log files recreate during a full restore?

SQL Writer Service explains how VSS can be used to take application consistent snapshot backups of the database files directly. It says it does not support log backups, which is why I asked this question.

Snapshot Backups are a feature of SQL server that allow hot backups without doing traditional dumps.

|improve this question
  • 1
    Are you also doing native SQL backups? I know VSS works but you can't be guaranteed that, in the event of a restore, the DB will come back up without some sort of error. We don't even snapshot our SQL servers, the systems guys let us handle everything with native SQL backups and they just take snapshots of the network share that holds the backups. – Kris Gruttemeyer Oct 11 '16 at 14:55
  • No, we aren't doing native SQL backups, other than when I do one manually for a specific purpose. Why can't I be guaranteed that the DB will restore without errors when using a supported backup method? If that's the case, then VSS doesn't work. – agermano Oct 11 '16 at 15:47
  • You're relying on a process outside of SQL server to take transactionally consistent backups of the mdf and ldf files. I don't know about you but, as a DBA, my employment rides on being able to recover my companies data in the event of a disaster. I would never rely on VSS/Veeam/AppSure for that. I know SQL native backup and restore backwards and forwards, it's baked in and works. We've tested VSS restores before and, sure they work most of the time, but we've also seen them come back with log file errors, causing us to have to rebuild the log file for the DB. – Kris Gruttemeyer Oct 11 '16 at 17:13
  • 2
    Have you ever tried restoring these VSS generated backups? – datagod Oct 11 '16 at 18:35
  • See links I added to question about SQL Writer Service and Snapshot Backups. Yes, I have restored them before, but I also restored the ldf files. As stated in the question, I'm in simple recovery mode, and I'm asking if there is anything wrong with rebuilding the log, since it isn't used for the backup anyway. – agermano Oct 11 '16 at 20:06
0

Quick answer would be that you'd better do full backup + differential backup. This will help you to restore quickly.

The problem that you back up mdf files only (while allowing ldf files recreated during full restore) is that you may have "dirty" transaction data in your mdf files. These "dirty" data usually can be cleaned out (i.e. rolled back) during normal restore process when using sql back up files.

|improve this answer
  • Please see link to SQL Writer Service in my updated question. These VSS snapshots are application consistent (unlike, say, a vmware snapshot which would only be crash consistent.) – agermano Oct 11 '16 at 19:56
2

Couple thoughts on this one: Do you have more than one database per-SQL instance? Are they all "owned" by the same application? If you've got multiple DBs for multiple apps, can your backup solution only restore ONE database (both the MDF and LDF) or will you have to restore all the DBs?

As Kris G pointed out, how do you KNOW your VSS snapshots are restorable to a working SQL DB? Have you test restored them somewhere?

Lastly, to cover your question, it's a bad idea to not backup the transaction log. Even in Simple Recovery, you can have "in flight" transactions that haven't gotten to the point of being written into the MDF yet. Not having the Trans Log when you restore one of these will make getting the DB back online and functional a much more difficult task.

Frankly, I'd take the safe way out, turn off the VSS snapshots of your SQL data and log drives, set up a backup process using the native SQL backup command on a set schedule to a location that is covered by the VSS. More work for you, a bit more work for whoever's in charge of the VSS snapshots, but you'll be much happier if / when you have to restore a database down the road.

|improve this answer
  • I updated my question with a link to the SQL Writer Service explaining how my backup methods are supported by Microsoft. Yes, there are multiple databases per instance, and I do have access to the individual files prior to restore. I have tested full restores of individual databases by detaching the database, restoring the mdf and ldf files from snapshot, and reattaching. According to (msdn.microsoft.com/en-us/library/ms189275.aspx) native transaction log backups are not supported in simple recovery mode (in fact, it's the defining feature.) – agermano Oct 11 '16 at 19:47
  • Tony, you still run the risk, if you don't back up the Transaction Logs, of an inconsistent database when you restore. Simple recovery doesn't mean the log doesn't get used, it only means that the log doesn't need to be backed up and that the information in the log gets "hardened" to the MDF automatically. About the only thing I could suggest at this point would be, take a backup without the TLogs and try restoring it. I'd also (if you haven't already) look at what's involved in attaching a DB without the LDF (because that's what you're doing) – Jason A Oct 17 '16 at 11:53
  • I'm pretty sure that VSS triggers the SQL Writer Service, which in turn creates a Snapshot Backup that puts the database in a consistent state and freezes IO until the backup is complete (this usually only takes a few seconds.) According to (mytechmantra.com/LearnSQLServer/…) if you attach the mdf without the ldf it will recreate it. Theoretically it should safely work, but I was really hoping to hear definitively from someone else that was actually doing backups in a similar way. I appreciate your response. – agermano Oct 17 '16 at 19:58
  • Looking over that article, the part that jumps out to me is "Important Note: -The above mentioned steps will work only when your database which was cleanly shutdown and you have the .MDF file of the database." To me, a clean shutdown means either the DB was detached or SQL Server was shutdown. I'm inclined to think based on that that a VSS snapshot of just the MDF would not qualify as a "cleanly shutdown." Best suggestion. Try it and see what happens on a test server. But I doubt you'll find many DBAs who would be doing backups the way you're looking to do it. – Jason A Oct 18 '16 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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