5

I have been advise by Commvault partner support to turn off the backup of the master database as the backup failed due to the log file being lock.

The following is the advise given:

"The message is caused by Commvault’s inability to backup the master database’s transaction log. If this is happening intermittently its possible that something is locking the transaction log, preventing SQL iData agent from accessing the log. Typically the master database is just a template and is not used by any applications (applications that do require the use of an SQL database create their own) so there should be no harm in preventing it from being backed up You can do this by nominating NOT to back it up in the primary copy for the SQL data agent"

The following is the error that I get.

sqlxx SQL Server/

SQLxx N/A/

System DBs 19856*

(CWE) Transaction Log N/A 01/08/2010 19:00:16

(01/08/2010 19:00:18 ) 01/08/2010 19:03:15

(01/08/2010 19:03:14 ) 1.44 MB 0:01:11 0.071 2 0 1

ITD014L2

Failure Reason:

• ERROR CODE [30:325]: Error encountered during backup. Error: [ERROR: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot back up the log of the master database. Use BACKUP DATABASE instead. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally.]

Job Options:Create new index, Start new media, Backup all subclients, Truncation Log, Follow mount points , Backup files protected by system file protection , Stop DHCP service when backing up system state data, Stop WINS service when backing up system state data Associated Events:

• 79714 [backupxx/JobManager] [01/08/2010 19:03:15 ]: Backup job [19856] completed. Client [sqlxx], Agent Type [SQL Server], Subclient [System DBs], Backup Level [Transaction Log], Objects [2], Failed [1], Duration [00:02:59], Total Size [1.44 MB], Media or Mount Path Used [ITD014L2].

• 79712 [sqlxx/SQLiDA] [01/08/2010 19:01:53 ]: Error encountered during backup. Error: [ERROR: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot back up the log of the master database. Use BACKUP DATABASE instead. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally.]

• 79711 [sqlxx/SQLiDA] [01/08/2010 19:01:51 ]: Query Result [[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot back up the log of the master database. Use BACKUP DATABASE instead. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally.].

• 79707 [backupxx/JobManager] [01/08/2010 19:00:15 ]: New backup request received for Client [sqlxx], iDataAgent [SQL Server], Instance [SQLxx], Subclient [System DBs], Backup Level [Transaction Log].

Files failed to back up:

• Backup Database[master] Failed

Please advise, thank you.

4 Answers 4

13

NOOOO, do NOT discontinue backup of master! Whereas most apps don't directly interface with the master database, master contains data for the current state of your server, databases, all kinds of things. That is horrible advice, to the point where I would seriously consider dumping their product. That really surprises me coming from a support agent of a backup product.

model is the template database, not master. master is crucial for your server to run properly (ie- at all). If something happens to it and you don't have a recent copy to restore from then your server is toast and you'll need to rebuild, reattach all of your db's, etc.

3
  • I was under the impression that most backup software have at least an option to backup locked files. I know backupexec and arcserve do.
    – GregD
    Jan 15, 2010 at 16:13
  • 3
    @GregD: most backup products do in fact have that option, but SQL Server and Exchange Server should never be backed up at the file level. The appropriate backup agent should be used. In fact, current versions of BackupExec (going back to at least version 11) have an active exclusion filter that will not backup SQL and Exchange database files directly even if you select their directories for backup.
    – joeqwerty
    Jan 15, 2010 at 19:16
  • Thank you everyone for your overwhelming comments...it is most helpful, very much appreciated. It is the 1st time I'm using Commvault backup after joining this new company and I'm by no means database admin expert. Commvault SQL Server agent is used in this case, so it seems that is not set to exclude master database log file by default. After reading every comments, I gather that all I need is to see if its possible to exclude that in the backup.
    – Imagineer
    Jan 18, 2010 at 3:57
5

+1. Wow! The recommendation from Commvault partner support is horrifying. The master database holds the keys to the kingdom (database info, login info, server info, etc., etc). Without it you're effectivley toast. As others have stated, the master, model, and msdb databases should be set to Simple recovery mode and as such, can not have transaction log backups performed, only database backups. If you attempt to backup the transaction log the backup job or maintenance plan will fail on that step. Also, when backing up SQL Server using third party backup software (BackupExec, Commvault, etc.), the appropriate application agent should be installed and used (SQL Agent) and the database files (mdf, ldf) should not be backed up directly at the file level.

1
  • 2
    Their advice is truly scary. The idea of not taking backups of master makes my head hurt. Jan 15, 2010 at 20:05
4

From the log messages, it looks like they're trying to run a transaction log backup (BACKUP LOG) on the master database. Master (like model and msdb) is required to be in the simple recovery model, which prohibits transaction log backups. You only need to do a full backup (BACKUP DATABASE) on the system databases.

I would definitely make sure it is backed up regularly - master stores all of the configuration information for the server, and without it, you would have to configure nearly everything from scratch if you did have a major failure. I have heard some people say that master does not need to be backed up daily (it only changes when you make a system-level change), but I do have it in all my daily maintenance plans so I don't forget to back it up.

2

This advice is exactly like if someone told you to not back up your system state, because you don't need it to rebuild a server if you have all the data. Just plain stupid.

You can of course rebuild a server without the master DB, but you'll then need to reconfigure everything from scratch; this not only means attaching DBs and setting system parameters, but also re-creating logins and assigning permissions.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .