question

paranoid_junior_dba avatar image
paranoid_junior_dba 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
backupdynamicvariable
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
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/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.