Feb 16, 2012

Prevent Non Copy Only Backups on SQL Server


I have a pretty standard backup scheme implemented: Fulls on Friday, Differentials nightly, Transaction Logs throughout the day. These are all automated tasks.

Occasionally we will need to take “one off” full backups for various reasons.

A lot of times, my users that are taking the one offs remember to use the copy only option, but sometimes they do not. This presents the obvious problem of the following nightly differential being based off of the “one off” full backups.

Is there a way to prevent any user (except for my backup operator) from making backups without the copy only option?

Asked by Rob


I’m not a SQL expert, but I’m going to throw this out there anyways. Can you create an automated task that the users can call that carries the copy only option?

Answered by Holocryptic

Related posts:

  1. SQL transaction log backups conflicting with full backups?
  2. SQL Server Backups using Volume Shadow Copy
  3. SQL Server 2008: Is it possible to store differential backups in a separate files?
  4. Concurrent backups in SQL Server?
  5. SQL 2005 Diff backups failing – no full backup exists?

Leave a comment