Creating Split backups Question

Question

Creating Split backups Question

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

If any of you would like to write up a little bit about your testing here, both with striping and other options, it might be good to have a few writeups for comparison purposes, helping others understand how to test and what they might try in their goal of getting better/faster backups.

Be good to get before/after results, with the setup and knowledge of some of the physical setup. The absolute values are less important than the relative values of before after. Meaning if I use a single HDD v 2 mount points that are separate HDDs, I might be slower than a single SSD, but knowing that 1 v 2 physical files works better (or not) is good knowledge.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 2

I'll see if i get time to do that over next few weeks

will be able to test both with SAN based servers and with PC based SSD and SATA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Someone should also include some of the documented pitfalls of using striped backups...

https://www.sqlservercentral.com/blogs/the-problem-with-striped-backups

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

I gotta say, my life's been so much easier since we out-sourced the backup part of my DBA job. I just define the backup rules, and someone else insures the actual backups and retentions occur. That part of the job is such a grind.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

ScottPletcher wrote:

I gotta say, my life's been so much easier since we out-sourced the backup part of my DBA job. I just define the backup rules, and someone else insures the actual backups and retentions occur. That part of the job is such a grind.

When it came to a couple of the smaller companies that I've done work for, I used to think the same thing... right up until they needed to do some restores. That's when they found out that both the RPO and RTO was actually ridiculous and then the buggers couldn't actually do a viable restore.

So, with that anecdote in mind, have you good folks actually done any test restores?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Yes, we have automated test restores and DBCC checks running constantly. That's for native backups. Unfortunately some of our databases are backed up with a SAN-based snapshot technology, which we don't control and so when they go wrong we have to rely on others to put them right. Not only that, the PowerShell modules that come with it aren't fit for automating the restores.

John

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

Several. In our normal course of business, we do periodic restores, and I request periodic restores just to make sure they're working correctly. This particular company has done extremely well on the whole process. Which frees up my time for more active activity than the administrative process of managing backups / restores.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

John Mitchell-245523 wrote:

Yes, we have automated test restores and DBCC checks running constantly. That's for native backups. Unfortunately some of our databases are backed up with a SAN-based snapshot technology, which we don't control and so when they go wrong we have to rely on others to put them right. Not only that, the PowerShell modules that come with it aren't fit for automating the restores.
John

I went through a similar thing. The SAN-based snapshot technology failed when we really needed it. The tape that we ended up pulling the native backups from worked perfectly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

ScottPletcher wrote:

Several. In our normal course of business, we do periodic restores, and I request periodic restores just to make sure they're working correctly. This particular company has done extremely well on the whole process. Which frees up my time for more active activity than the administrative process of managing backups / restores.

I'm jealous. Thanks for the feedback, Scott. Glad to see that some companies get it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)