I had a request from a business user to create a job that they want to run on an ad-hoc basis to backup a database to a static file name. And they backup file needed to be deleted/overwritten every time the sql job ran. I used Ola Hallengren’s backup code to take the database backup with a static file name.
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DatabaseName',
@Directory = N'E:\DeployBackups',
@BackupType = 'FULL',
@Verify = 'Y',
@CheckSum = 'Y',
@Compress = 'Y',
@CleanupMode = 'BEFORE_BACKUP',
@Init = 'Y',
@FileName = '{DatabaseName}.{FileExtension}'@Databases = specify the name of the database
@Directory = the directory where the backup file will be written to
@BackupType = what type of backup – FULL, DIFF or LOG
@Verify = verify the backup file after it is completed
@CheckSum = check the checksum of the database file
@Compress = compress the backup file
@CleanupMode = when should the old backup file will be deleted – before or after the current backup job is completed. If you have limited space on the drive then it is a good option to delete the old backup file before the new backup is started but you run the risk of losing all the backup files incase the current backup job fails.
@Init = ‘Y’, or ‘N’ specifies if the current backup files will be deleted. It is important to note that we need to use this option since we are backing up the database to a static file name. If you do not use this option then the backup will be appended with the new backup and the size of the backup file will be growing.
@FileName = ‘{DatabaseName}.{FileExtension}’ this option specifies the exact name of the database file every time the backup runs.
Hope this helps, good luck.