We use the Ola Hallengren scripts for full/diff/tran log backups. Ola's site indicates that log shipping is compatible unless we are using transaction logs. Why is that?
Ola scripts = log shipping and transaction logs
ola-hallengren
Related Solutions
Do you know what version of each you are using? How close are you watching this in real time? I'm hoping the below quotes and links with the questions above will help spur you towards the right direction here.
When backing up to or restoring from Windows Azure storage, SQL Server acquires an infinite lease in order to lock exclusive access to the blob. When the backup or restore process is successfully completed, the lease is released. If a backup or restore fails, the backup process attempts to clean up any invalid blob.
The Lease Blob operation establishes and manages a lock on a blob for write and delete operations. The lock duration can be 15 to 60 seconds, or can be infinite. In versions prior to 2012-02-12, the lock duration is 60 seconds.
Starting in version 2012-02-12, some behaviors of the Lease Blob operation differ from previous versions. For example, in previous versions of the Lease Blob operation you could renew a lease after releasing it. Starting in version 2012-02-12, this lease request will fail, while calls using older versions of Lease Blob still succeed. See the Changes to Lease Blob introduced in version 2012-02-12 section under Remarks for a list of changes to the behavior of this operation.
Ola Hallengren's solution is based on beset practice and years of experience. There are some quirks though. Take a look....
Before you start
Before you run the initial script, check the first few parameters and the database used:
use [master]
@CreateJobs
@BackupDirectory
@CleanupTime
@OutputFileDirectory
@LogToTable
Hint: Because the tables and procedures are created in the database specified at line 20 in the script (
use [master]
), you might want to consider storing these objects in a different database, by modifying the script to point to e.g. themsdb
database, or some other user specified database.Hint: Read the description for each parameter and modify according to your requirements or restrictions.
Objects created
The solution is deployed as one script named MaintenanceSolution.sql
, which itself will create stored procedures, tables and jobs .
Following is a list of objects that are created:
Table
- [dbo].[ComandLog]
Stored Procedures
- [dbo].[CommandExecute]
- [dbo].[DatabaseBackup]
- [dbo].[DatabaseIntegrityCheck]
- [dbo].[IndexOptimize]
Getting started
Out of the box Ola's MaintenanceSolution.sql
script will create the following jobs:
- Database Backup - SYSTEM_DATABASES - FULL
- Database Backup - USER_DATABASES - DIFF
- Database Backup - USER_DATABASES - FULL
- Database Backup - USER_DATABASES - LOG
- DatabaseIntegrityCheck - SYSTEM_DATABASES
- DatabaseIntegrityCheck - USER_DATABASES
- IndexOpitmize - USER_DATABASES
- sp_delete_backuphistory
- sp_purge_jobhistory
- Output File Cleanup
- CommandLog Cleanup
Hint: Please be aware that Ola's solution does not create any job schedules! This is something you will have to plan for and create yourself.
Now if you know that these jobs belong to Ola's solution, then you can leave them as is. Otherwise if you would prefer to mark them, then you might consider prefixing a string inside the MaintenanceSolution.sql
before you execute the script. Do a regex search for the string SET @JobName[0-9][0-9] = '
with a suitable text-editor and add a prefix to the job's name
e.g. OLA Database Backup - SYSTEM_DATABASES - FULL
Consideration: Please keep in mind that if you update the scripts later on with a newer version from Ola and don't alter the names from the original to match your naming convention, then you will have duplicate jobs. You might want to keep the job names "as-is".
IndexOptimize
Ola created his IndexOptimize
stored procedure according to the following Microsoft recommendations:
- General Index Maintenace when data/index > 1000 pages
- Fragmentation >5% and <= 30%
- Do: Index Reorg
- Fragmentation > 30%
- Do: Index Rebuild
- Fragmentation >5% and <= 30%
Hint: Depending on your data and/or the amount of records in some of your tables, you might want to consider creating individual jobs with different parameters that suit your requirements for larger tables.
Database backups
The backup job create sub-directories for each level of the database's instance. So if you are running the script on a server MyServer
on an instance named MyInstance
and the database is named MyDatabase
and you are looking at a Full backup, then the backup file will be stored in the following directory with the following name:
H:\MyServer\MyInstance\MyDatabase\FULL\MyServer_MyDatabase_FULL_20170712_105500.bak
A DIFF backup will look like this:
H:\MyServer\MyInstance\MyDatabase\DIFF\MyServer_MyDatabase_DIFF_20170712_105500.bak
etc.
Best practice
Consider deploying the solution to a development server as is (ok, modify the database you will be using) and then have a look at the individual scripts, jobs and tables. Familiarise yourself with the solution and then modify according to your requirements.
And of course consider reading Ola's documentation on his site:
Related Question
- How to Take Backups on a Defined Directory in SQL Server
- SQL Server Backup – Deleting Transaction Log Backups with Ola Hallengren
- SQL Server – Understanding CleanupTime in Ola Hallengren’s Scripts for Backups
- SQL Server – Custom Backup Deletion with Ola Scripts
- Sql-server – Custom Deletion for Ola Hallengren Scripts
- SQL Server – How to Integrate Ola Hallengren Solution with Log Shipping
- SQL Server – LOG Backup Always ON with Ola Hallengren Script
Best Answer
I believe your answer lies in another answer here on StackExchange. Transaction log shipping together with backup job = conflict?.
According to Ola's Frequently Asked Questions
Does the SQL Server Maintenance Solution support log shipping?
So, if you implement Log Shipping, just keep in mind that THAT process will be taking your transaction log backups and NOT Ola's process.