Sql-server – Ola Hallengren Maintenance script – Full database backup delays

backupola-hallengrensql server

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.

The FULL backup takes around 90minutes to complete.
The backup jobs is scheduled to run at 2AM.
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)?

Best Answer

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

Related Solutions

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

Sql-server – Ola Hallengren backup failed

You need to ensure the SQL Server Service Account has the required Access Control for the paths used for storing your backups.

Check the SQL Server Accounts and Permissions page for details about what ACLs are required. See Configure File System Permissions for database engine access for details about how to change permissions.

Best Answer

Related Solutions

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

Sql-server – Ola Hallengren backup failed

Related Question