Backing up User and System databases at the same time

Question

Backing up User and System databases at the same time

Mike Scalise

SSCrazy Eights

Points: 8145
More actions
September 28, 2016 at 2:18 pm

#313585

Hi,
Is it generally a bad practice to have jobs that back up user and system databases that have the potential to be running at the same time? In other words, is it OK if user and system database are being backed up at the same time during a maintenance window?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com

Viewing 8 posts - 1 through 7 (of 7 total)

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

rvsc48 SSCertifiable Points: 7685 More actions · Answer 1

Hi Mike, Sql Server allows this, but, as a good practice due to the I/O and disk loads, it is better to schedule your system backups at a different time as your user backups. Perhaps a good interval could be 15 or 30 minutes between the two. For example, you could schedule your System backups at 9:45 pm and your user ones at 10:00 pm assuming your system ones complete before the user ones start.

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 2

Thanks for the response, RVSC.

See, I don't know exactly when either backup will finish and I want to make the most of the maintenance window. I'm considering creating one job with the user and system database backups as individual steps so that I don't need to think about the scheduling--I would just have one start after the other (not sure which order I'll do just yet). Any reason not to do it that way?

Thanks again.

Mike

Mike Scalise, PMP
https://www.michaelscalise.com

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

I'd just do them one after another.

I did set up a Powershell script once that spawned threads to run multiple backups simultaneously. I'll never do that to a production system again. Huge mistake. Seemed like a good idea at the time.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 4

Grant Fritchey (9/29/2016)
I'd just do them one after another.
I did set up a Powershell script once that spawned threads to run multiple backups simultaneously. I'll never do that to a production system again. Huge mistake. Seemed like a good idea at the time.

Thanks, Grant. I suppose that's how we learn, right.........?

Mike Scalise, PMP
https://www.michaelscalise.com

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

Mike Scalise (9/29/2016)
Grant Fritchey (9/29/2016)
I'd just do them one after another.
I did set up a Powershell script once that spawned threads to run multiple backups simultaneously. I'll never do that to a production system again. Huge mistake. Seemed like a good idea at the time.
Thanks, Grant. I suppose that's how we learn, right.........?

Seems that how I learn anyway. Ha!

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Mike Scalise (9/29/2016)
Grant Fritchey (9/29/2016)
I'd just do them one after another.
I did set up a Powershell script once that spawned threads to run multiple backups simultaneously. I'll never do that to a production system again. Huge mistake. Seemed like a good idea at the time.
Thanks, Grant. I suppose that's how we learn, right.........?

Many have a lot more have to learn than a few others which are not at the top of the food chain like Grant.:-D

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Grant Fritchey (9/29/2016)
I'd just do them one after another.

Same here. I've worked on many systems that do "parallel" backups of one form (two or more concurrent jobs) or the other (individual split backups to multiple files) and have found that, unless you can guarantee that each job or file is writing to separate physical spindles, such parallelism slows the backups down due to head thrashing. You just can't change physics.

--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)