Backup without "Copy Only"

Marty P 21 Reputation points
2020-09-22T21:05:54.18+00:00

Hi,

I wonder if a manual backup of an SQL server database without "Copy Only" mode will interrupt a scheduled backup plan set up for the database? If so, is there a way to fix it?

Thanks in advance!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,175 questions
Accepted answer
  1. Erland Sommarskog 72,891 Reputation points MVP
    2020-09-22T21:48:54.293+00:00

    It will not affect the backup schedule. Well, in one situation maybe: you start the backup just before the scheduled time, so that your backup runs when the backup jobs starts. I don't know what happens here. But in that case, COPY_ONLY or not does not matter.

    However, failing to use COPY_ONLY could still damage the backup scheme, if it includes differential backups, the next differential backup will be based on your manual backup. This can cause some panic if you get a disaster and you are not able to restore the database from the schedule backups.

    1 person found this answer helpful.

3 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Marty P 21 Reputation points
    2020-09-23T04:07:23.37+00:00

    Thanks for the reply! This was a full backup so I don´t have to worry then?

  2. Shashank Singh 6,216 Reputation points
    2020-09-23T05:00:12.253+00:00

    I wonder if a manual backup of an SQL server database without "Copy Only" mode will interrupt a scheduled backup plan set up for the database? If so, is there a way to fix it?

    I dont know exactly what you mean by interrupt but are you saying that you run manual backup(full, log and differential) and the scheduled backup at same time ?. If that is the case yes you can run 2 backups concurrently and MS has documented what all operations can run concurrently.

    BUT please note you have to keep the backup handy because manual backup can break the restore sequence of log backup. Also a new differential backup can break your full backup chain in terms new differential backup will point to new full backup taken. To avoid this I would suggest you to use copy_only

  3. tibor_karaszi@hotmail.com 4,291 Reputation points MVP
    2020-09-23T09:03:03.003+00:00

    You have some replies already, I'd just like to summarize, perhaps clarify a bit and also expand a bit on what has been posted already:

    COPY_ONLY (I'll call it CO from now on) can affect restorability in two scenarios:

    One is when you take a full backup. A subsequent doff backup is based on the most recent (non-CO) full backup. So if you want to take a full backup without affect restorability of diff backup, then do the full backup as a CO backup.

    The other is a log backup. When you restore log backups you need an unbroken chain of log backups. You can take an out-of-bands log backup which won't be needed for a subsequent restore process by doing that log backup as a CO log backup. Basically, it won't empty the log.

    A full backup does not affect restorability of log backup. CO is not needed here.

    Another aspect, though, is if you want to rely on SSMS and its backup history to do restore. In some cases, CO backups are not in the proposed restore sequenct. But in other cases, a restore sequence is based on a CO backup and if that backup doesn't exists on disk, then you won't be able to do the restore from SSMS based on the backup history. It is a bit of a mess and I've reported this to MS several time, but I have no hopes for this being fixed.