Unsolved
This post is more than 5 years old
100 Posts
0
8702
MSSQL VDI backup vs. MSSQL VSS backup
Hi,
I was playing with MSSQL VDI and MSSQL VSS backups on the same machine, and would like to discuss with you which type of backup are "better" and what are cons and pros.
Let me describe my configurations of both types of backups.
VDI:
Save Set - MSSQL:
Backup Command: nsrsqlsv -A host.com -S 4
VSS:
Save Set - APPLICATIONS:\SqlServerWriter
Backup Command: nsrsnap_vss_save.exe
Application Information: NSR_SNAP_TYPE=vss
While doing VDI type od backup save sets of Full backup of MSSQL were about 1,5 GB big. As mentioned in NMM Application Guide, while doing VDI type of backup level Full, backups of transaction logs is not performing (truncating also), am I right? It only truncate logs while using "-l incr" option, or by specifying Incremental Level in Schedule configuration, or executing nsrsqlsv using -T option. It seems that it does just that.
What about VSS type of backup? Full backup of MSSQL were about 69 GB big. You see the difference and discrepancies when comparing it with VDI type of backups? And what about backing up transaction logs? Do those logs truncate? I tried incremental backup also, and it gives the same 69 GB big backup, so, the question is, does incremental backup make any sense in VSS type of backups?
And, what about restores?
Tried VSS restore, and it restores databases and logs, but for the restore procedure I had to have double the size of the disk, because, restore procedure first create some temporary folder, lets say /nsr_temp/nsr_recover, and if I do not have disk large enough, restore fails.
Tried VDI restore, and it gives me option only to restore databases (no logs?). I don't get it... which one is good for me, if you put in mind following:
1. I want to backup databases and transaction logs.
2. After backing up, I want that transaction logs truncate as well.
3. I want to restore, both databases and transaction logs.
I hope I was clear about what is my dilemma.
Thank you,
Zlatko
ble1
2 Intern
2 Intern
•
14.3K Posts
1
February 28th, 2013 07:00
Reason why people use VDI today is because with VSS you could not address transaction logs. Not sure if that changed in SQL 2012 as I didn't touch it yet. Truncate is not something MS recommended - search their own site (because after truncating you loose PiT capabilities). You can restore both DB and TLOG with VDI (just make sure no truncating is done). For more information, check MS SQL docs (and I mean MS, not EMC as all this happens from API and what is capable of).
CarlosRojas
1.7K Posts
0
February 28th, 2013 08:00
Hi Zlatko,
First mots important points:
VSS will NEVER truncate logs. This is a VSS limitation and not backup solution problem. Any SQL backup using SQL writer will never truncate the logs
Having this said, note that VSS is just an snapshot of the DB itself, and will include absolutely all the data at the PIT where you request the snapshot, while VDI will only backup the files required for the backup, either full or incremental.
You are right when affirming that only Incremental level will truncate the logs (if you don't have set SIMPLE recovery model on the DB's, otherwise Incr will be promoted to full) or using nsrsqlsv -T
If you are backing up SQL DB's not belonging to a SharePoint farm, I would say you should use VDI backups, as this uses less backup storage, and offers the option for incremental level and logs truncation.
Now, for what you are asking for, I think you need to engage the SQL DBA, and discuss around the approach you want to take. VSS will indeed restore DB and logs at the PIT you took the snapshot while VDI offers you different backup and restore options (please refer to the documentation for further information), but again, bear in mind that most of the times, Backup admin and DBA mind set don't match, so that's why my suggestion of you both (I suppose you are not the DBA) get together and discuss about the backup plan
Thank you
Carlos
Thierry101
2 Intern
2 Intern
•
326 Posts
0
March 18th, 2014 01:00
Hi Carlos/Hrovje
If my memory serves me right, was SQL DBA 9 years ago , if the trans log truncated during incr backup then you can get PiT by recovering full followed by replaying all the trans logs(depends if incr or diff). Also DB recovery mode matters. I might be wrong. Been too long ago.
Zlatko
What have you decided on or have you done further tests with db recovery models?
Can you please share on your outcome?
1. I want to backup databases and transaction logs.
2. After backing up, I want that transaction logs truncate as well.
3. I want to restore, both databases and transaction logs.
ble1
2 Intern
2 Intern
•
14.3K Posts
1
March 18th, 2014 06:00
Thierry,
Truncate does create PIT (sorry, I was referring more to shrinking of log which is different). In VSS world, shadowcopy is not considered to be log backup so even if you have snapshot of LOG volume, virtual logs are not truncated. This means you need to have extra action using MgmtStudio if using VSS to backup logs manually to some volume and take it from there.