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).
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
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.
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.
ble1
4 Operator
•
14.4K 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
•
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
4 Operator
•
14.4K 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.