- How can we configure Ola Hallengren's solution on a managed instance? (We want output file for each run, storing it is an issue)
- If we want backup retention longer than 35 days for the database in managed instance, what are the possibilities? (
copy_onlybackups to blob storage?)
Ola Hallengren for Managed instance
azureazure-sql-databaseola-hallengren
Related Solutions
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'
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
This is a list of random things that come to mind, I can't guarantee any are the problem.
- DateTime on Server has been knocked out
- DateTime on the Backup store has been knocked out
- Someone has changed the @CleanupTime on your agent job (potentially accidentally)
- There is a backup of your backup location occurring while the cleanup is trying to run locking the files (have had this one myself)
Since you've said that this is a recent problem not a just happened I'm presuming you can still delete the files manually which would indicate they are not in permanent use.
It may be worth running the -o command at the end to write the output of the command to a file ( where it is '-b' chance to '-b -o C:\Logs\backuplog.txt') for example and consult that for if it is bringing back any problems
Related Question
- Set Retention for SQL Backup to URL
- SQL Server – Ola Hallengren DatabaseBackup Error on Locating Windows Directory
- SQL Server Backup – Deleting Log Backups Older Than Last Full Backup with Ola Hallengren
- Sql-server – Ola Maintenance Solution and striped Azure URL backups – possible
- SQL Server 2012 – Fixing @MirrorDirectory Not Supported Error in Ola Hallengren Backup Script
- Sql-server – DIFFs promoted to FULLs
Best Answer
Ola Hallengren's maintenance solution has several parts:
Database backups - you don't usually want to use these in Managed Instances because you can't restore the backups to another location, like on-premises. The only use for database backups is to restore them into another managed instance - like if you're doing it for disaster recovery, backing them up to blob storage, and then syncing that blob to a different Azure account (in case you're worried about a malicious actor closing your Azure account, and leaving you with no backups.) If you do want to use backups for that purpose, you can use the @Url parameter for the DatabaseBackup proc, and back up to a URL.
Integrity checks - these work the same way they work for normal SQL Server - no change for Ola's scripts here.
Index and statistics maintenance - these also work the same way they'd work on-premises, but be aware that with the limited transaction log throughput of Azure SQL DB and its relatives, you probably want to use the @Delay parameter when you rebuild/reorg indexes so that you slow down the amount of transaction log throughput you're generating, and leave some DTUs for user transactions.
Parameters common to all procs - don't try to log them to the file system, because you simply don't have access to the file system.