August 9, 2018 at 10:22 am
Our IT director is getting a license for a tool which will help us steup DB backups where we can manage it more properly. However; he did mention that this tool does incremental backups (which is not possible in SQL Server) as well as it takes block level backups (which I don't know what it is, will have to read about it) and it does table level backup. Is it possible in SQL Server because I am not aware of it. I thought SQL Server only allows full, dif and tlog backups . If someone can help me understand.
August 9, 2018 at 11:14 am
Nope, anyone doing this is going to get into trouble. You need transactionally consistent backups, which might be possible if the VSS service can pause the disk writes while you get a backup, but it could cause issues.
For tables, you would need snapshot isolation of some method of ensuring that if I start a backup, I get the state of the table at that point in time.
Example, I have an employee table with a self-referencing key for the managerID, that is a FK to the PK of the table. So maybe a simple example..
ID Name Manager
1 Victoria 0
2 Steve 1
3 Delaney 1
4 Kyle 2
5 Kendall 2
This represents
Victoria
|-----------|
Steve Delaney
|
|--------------------|
Kyle Kendall
Imagine there are lots of rows here.
I start a backup of the blocks of this table. I get rows 1 and 2 backed up. Then an update comes in (concurrent system, right?) that removes row Id = 1 and looks to update all rows. It manages to update row 3 before it's backed up, but row 2 has been backed up. a restore here is not consistent because this refers to a row that doesn't exist.
This can easily happen in systems, imagine order line items, or other multi-row constructs that could be half updated in a backup.
Block level backups that can't get a consistent snapshot of the db or table level backups that don't lock the table, can cause issues if they are restored.
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
August 9, 2018 at 11:54 am
NewBornDBA2017 - Thursday, August 9, 2018 10:22 AMOur IT director is getting a license for a tool which will help us steup DB backups where we can manage it more properly. However; he did mention that this tool does incremental backups (which is not possible in SQL Server) as well as it takes block level backups (which I don't know what it is, will have to read about it) and it does table level backup. Is it possible in SQL Server because I am not aware of it. I thought SQL Server only allows full, dif and tlog backups . If someone can help me understand.
It could also be that the manager misspoke about some of this. I've had that happen to me. Quite a few times I've had others in IT outside of the database world refer to differentials as incremental backups. And sometimes they confuse the server level backup features and the SQL Server backup features. There are a few third party backup products that can do object level restores from their backups so I would guess someone could confuse that will having done that restore from a table level backup.
Do you know which product? I'd probably start from there and read their documentation to understand it better. Eventually you'll probably need to read that anyway.
Sue
August 9, 2018 at 12:48 pm
https://www.acronis.com/en-us/
Just going to read about it.
August 9, 2018 at 1:29 pm
NewBornDBA2017 - Thursday, August 9, 2018 12:48 PMhttps://www.acronis.com/en-us/
Just going to read about it.
That one...was kind of a pain. And yeah...there was some bad information and confusion about server backup vs SQL Server backup. I know they had the object level restores and didn't have any such thing as table backups. That could have changed recently but I doubt it. Your manager likely heard "you can restore an object such as an individual table".
We ended up using native SQL Server backups to whatever location and the software just backed up those files. It doesn't support transaction log backups - you need to use native for those. They kind of push setting recovery model to simple. In the log run, it was going to be harder using only acronis for SQL Server backups plans.
They pretty much just had generic stuff on their site. Eventually I found the user guide and that's what I ended up using. It's the only thing that had much information and it still wasn't very clear on things.They did mention SQL server incremental backups but that was when doing backups of the data and log files. I remember the too long discussions about that. A lot of things didn't work as they thought they would in terms of SQL Server so you do need to figure things out and let them know how it works from a SQL Server standpoint.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply