asked
DIFF BACKUP - HOW TO USE A VARIABLE FOR THE NAMING
Hi Team, Currently there is a backup job for the DIFF backup and the script used is; BACKUP DATABASE [SAMPLE] TO DISK = N'A:\Backups\Diff\SAMPLE_DIFF_1of3.bak', DISK = N'B:\Backups\Diff\SAMPLE_DIFF_2of3.bak', DISK = 'C:\Backups\Diff\SAMPLE_DIFF_3of3.bak' WITH DIFFERENTIAL , NOFORMAT, INIT, COMPRESSION, NAME = N'SAMPLE-Differential Database Backup', STATS = 5 GO This runs overnight and overwrites the previous nights diff backup, I need to retain the diff for 7 days, so I am trying to come up with a way to modify this script so that sql dynamically names the file, so if it already exists on the drive it can just add the days date to the end or something. Can anyone help or point me in the right direction in how to rewrite this script to enable a variable feature
Your clue is where you say "sql dynamically names the file". [Dynamic SQL][1]. What you need to do is write some code to build the backup command, and then calls exec or sp_executesql to run that command. This is the core of what almost all maintenance solutions do, such as those provided by [Ola Hallengren][2] as well as other third party backup tools. [1]:
https://msdn.microsoft.com/en-us/library/ms188001.aspx [2]:
https://ola.hallengren.com/
18 People are following this question.
