2

The DatabaseBackup job created by Maintenance Script will introduce delay for the last database being backed up.

What I mean is, there are 6 databases around 200GB each.

  1. The FULL backup takes around 90minutes to complete.
  2. The backup jobs is scheduled to run at 2AM.
  3. So, the last 2 databases to be backed up will potentially run on business hours (after 8AM)

1st DB will complete on 3:30 AM
2nd DB will complete on 5:00 AM
3rd DB will complete on 6:30 AM
4th DB will complete on 8:00 AM
5th DB will complete on 9:30 AM
6th DB will complete on 11:00 AM

All the data files are on E: drive
Does it take same amount of time even when the backups are made to run at the same time at 2AM(by different jobs) due to IO overhead?

Is this the normal way the FULL backups are made (with delays)?

  • If you make separate jobs running at the same time, you should be able to finish before. It depends on the setup of your server though. The server could be heavily used at the time all the backup jobs are running, but if that is outside business hours it should not matter anyway. – GHauan Apr 21 '17 at 12:07
2

It will depend on the resources available on the server.

Does it take same amount of time even when the backups are made to run at the same time at 2AM(by different jobs) due to IO overhead?

You can set up parallel jobs and test. You will need to watch, how many of full backups can run in parallel before other processes (if any) are effected (cpu, io, network bandwidth etc).

Recently there was another question where Brent Ozar explained the phases of backup and how you can test.

I will not repeat his words but you can check details here.

There are built in switches in SQL Server that you can use to speed up backup. See this blog post.

I did not understand this question? Did you mean if full backup run in serialized way? Depends how you job is set up. You can make them parallel as @Ghauan explained in comment, you can make multiple jobs to run in parallel.

Is this the normal way the FULL backups are made (with delays)?

2

Do you have backup compression enabled? Quickly check with this query:

select * from sys.configurations where name = 'backup compression default'

If you don't, enable it immediately, as I've yet to see a downside with this feature. This option became available with Standard Edition with SQL 2008 R2 and it's one of the first sp_configure options you should enable after setting up a new instance. This will help speed up backups dramatically.

Another option is to stripe your backups to multiple files. This will increase the I/O throughput of the backup and reduce the backup speed dramatically as well. One of the things I don't like with Ola's scripts is that this setting isn't dynamic or configurable based on the size of the database. What I will suggest though is to specify 4 paths for the DIRECTORY parameter and increase/decrease after testing it out.

From Ola's Backup Page about striping:

Specify backup root directories, which can be local directories or network shares. If you specify multiple directories, then the backup files are striped evenly across the directories. Specify multiple directories by using the comma (,).

So for example, in your Ola Job:

DIRECTORY='D:\Backup1,D:\Backup2,D:\Backup3,D:\Backup4'

Configuring backup compression and striping your backups will still allow you to keep your backups serialized, but should reduce the time for them to complete.

  • Backup compression will be enabled soon. Thank you. – Santhoshkumar KB Apr 24 '17 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.