2

I have created a maintenance plan using the Wizard and this is the SQL produced, per SSMS:

BACKUP DATABASE [FOO] TO  DISK = N'C:\SQLBAKS\FOO\FOO_backup_2019_08_09_161656_6414445.diff'  
WITH DIFFERENTIAL , NOFORMAT, NOINIT,  
NAME = N'FOO_backup_2019_08_09_161656_6414445', 
SKIP, REWIND, NOUNLOAD, 
STATS = 10

The resulting files do not have a ".diff" extension but ".bak"

I am trying to differentiate FULL backups from DIFFERENTIALS in the filename.

0

1 Answer 1

1

When you go through the wizard you should

  1. Click the differential option and
  2. Specify diff in the Backup file extension on the Destination Tab (it's bak by default, even for differential)

Most people aren't fans of maintenance plans since you are kind of blindly turning over some control and they create SSIS packages which aren't fun to troubleshoot. Ola's scripts are super easy, well documented, and have a ton of options. You can always roll your own via Agent Jobs too.

2
  • Yes, Backup file extension on the Destination Tab was where I had specified diff originally. Unless that is not the actual script being executed, it's being ignored.
    – Tim
    Commented Aug 9, 2019 at 21:24
  • Quite possible, which is why most people dodge maintenance plans. However, I just followed the steps i produced above and it spits it out as .diff. Also, if you go into your maintenance plan, right above your "View T-SQL" button in the back up task you should see diff in the backup extension. So, your version has a bug or you are mistaken. I am on Microsoft SQL Server 2016 (RTM)
    – S3S
    Commented Aug 9, 2019 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.