I am attempting to backup the transaction logs of master, model in addition to the msdb.

MSDB transaction log backs up without any issues, but the master and model do not.

Here is the script that I have included in a new Agent job (copied from DatabaseBackup - System_Database - Full) which I have named DatabaseBackup - System_Database - Log.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES', @Directory = N'\\synologymb\sql backups' , @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y', @LogToTable = 'Y'" -b
  • Are master and model in full or simple recovery? Aug 15 '19 at 10:32
  • All of the system databases are in full, sorry I didn't specify that to begin with.
    – Bill
    Aug 15 '19 at 12:01
  • There is a typeo error: '\synologymb\sql backups' in the actual script is: '\\synologymb\sql backups'
    – Bill
    Aug 15 '19 at 12:14
  • Welcome to DBA.SE. What is the error message or output that you are getting?
    – Doug Deden
    Aug 15 '19 at 12:20
  • No errors. MSDB is the only database that has the transaction log backing up for system databases using Ola Hallengren script. Note: tempdb of course is not part of this equation. If it works for one system database, then it should work for the other two. Could I be possibly missing something in the script? Using the USER_DATABASES scripting for the log transactions works perfectly. Thank you for the welcome.
    – Bill
    Aug 15 '19 at 12:42

Per this Microsoft documentation:

For backwards compatibility with earlier versions of Microsoft SQL Server, the recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model.

So that's why you can't get a transaction log backup of Master.

From that same document:

Best practice: We recommend that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.

While that doesn't explicitly say that SQL will prevent a transaction log of Model from happening, it certainly recommends against it.

Note: That's an old version (2008 R2) of the documentation. New versions (I checked 2017) don't repeat the detail about Master, but they also don't contradict it.

  • 1
    I can accept that. Thank you for your input.
    – Bill
    Aug 15 '19 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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