January 29, 2018 at 10:21 am
Hello. My company recently moved away from Redgate SQL Backup and Ola's scripts (my recommendation, and what we've used for many years, without a single recovery issue) to our MSP's recommendation of StorageCraft, which uses VSS. I fought it, but others higher up than me made the decision.
StorageCraft takes a VSS backup of each database every 30 minutes. It does not have any log backup functionality, so every backup is "BACKUP DATABASE [DBName] TO VIRTUAL_DEVICE='{B48E087F-600D-40A1-A6EF-A81E8A85F331}6' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024." My understanding is that a point in time restore from this system overwrites the MDF and LDF files and the db is available right away.
Our databases are in FULL recovery model, so without the BACKUP LOG being done, the log files are growing too large. We reindex every Sunday at 4:30am, and this job has always been 1) SET SIMPLE, 2) reindex, 3) shrinkfile the log, 4) SET FULL, 5) FULL backup, to reset the backup chain. With Storagecraft doing the VSS db backups, step 5 is no longer done. We have introduced a BACKUP LOG job at Sunday 2:00am, which assists in the log file size management and shrinking. How do these two jobs work with or conflict with what the VSS job is doing?
Should I be worried about the recoverability of the VSS point in time backups with the recovery simple/full changes thrown into the mix? Does a VSS db backup solution ignore the backup chain/LSN? Should our dbs be in SIMPLE recovery all the time, given the VSS backups?
Thanks!
January 29, 2018 at 11:16 am
A few things about the VSS solution are not clear.
It does not look as though it will do a point in time recovery to a Test or Dev DB.
I would be inclined:
1. to do a COPY_ONLY full backup once a day with CHECKDB and test restore.
(The COPY_ONLY bit means it will not interfere with anything VSS is doing.)
2. to take log backups every 10 mins. This should allow your log to settle at a reasonable size.
3. not to change the recovery model for the reindex.
(if you really want to change the recovery model look at bulk-logged as it will not break the log chain.)
This should allow you to easily copy the production DB to Test, Dev etc and will also provide a fall back if the VSS solution ever fails.
January 29, 2018 at 11:31 am
The VSS technology pauses the db so that a consistent, PIT copy can be made. This should give you useable files, though it is possible a restore will have inflight transactions. The backup with snapshot essentially pauses all activity, so you might have a transaction open while the backup occurs that needs a rollback when you restore. That's something to be aware of.
This might help you: https://blogs.msdn.microsoft.com/psssql/2009/03/03/how-it-works-sql-server-vdi-vss-backup-resources/
With regards to your worries, I think the backup chain is fine. If you're not doing log backups, the only thing that logs would help you with is the tail log in the event of a restore. Meaning, you backup at 1:00pm, db has issue as 1:25pm, if you get the tail log, you 1:00pm restore + tail log restore prevents data loss. The issue here is you need to restore with norecovery. Have to check that the tool does that and test this process.
The backup chain always exists. Even in simple mode, the log works the same, but you lose the ability to do backups because this mode cleans/clears/marks the log on checkpoints.
If you can't restore with NORECOVERY, then you might as well be in Simple mode.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 29, 2018 at 12:11 pm
Thank you for the replies and advice. Much appreciated! 🙂 I have more digging to do.
January 30, 2018 at 11:46 am
I ran into this issue when backing up virtual database servers with Microsoft's Data Protection Manager 2008 several years ago. At the time there was a registry fix for Server 2008 R2 but I don't know if the same would apply to any newer versions of Windows Server. This worked for me on several of my 2008 R2 servers in the past but I am not certain if the same fix would apply to a StorageCraft VSS backup.
https://social.technet.microsoft.com/Forums/en-US/c70cf685-0f7d-4010-86cb-599345fd66e8/exclude-sql-databases-from-dpm-image-level-backup?forum=dataprotectionmanager
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply