SQL Server – Using Same Filepath for Recurring Backups

backupsql serversql-server-2016

I need to set a back up plan to cover a dev/pre-production server for a bit until a real DBA comes along. My plan is to run 3 T-SQL Jobs that basically do run the following statements

  • Nightly:

    BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDB.bak'
WITH CHECKSUM;

  • Hourly:

    BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDBDiff.bak'
WITH DIFFERENTIAL;

  • Every 5 minutes

    BACKUP LOG [MyDB]
   TO  DISK = N'L:\MSSQLSERVER\Logs\MyDB.trn';

I am concerned that overwriting the last differential back up / log back up file is bad practice because I haven't been able to confirm that the new log/diff back up will cover the entire period from the last full db backup.

Can anyone confirm how this works in terms of what time range is covered by what back up file?

My hope is that in case of a 9:54 AM failure

  • MyDB.bak brings me up to midnight
  • MyDBDiff.bak Brings me up to 9
  • MyDB.TRN can bring me up to any time between 9 AM and 9:50 AM

Even though I have only one file from each job.

Am I crazy or on the right track?

Best Answer

As mentioned in the comments, backing up to an existing file name will, by default, append the latest backup to that file. For full and differential backups, at least, you don't want to do that.

For your purposes:

  • Once you have a new full backup, you don't need previous full, differential, or transaction log backups.
  • Once you have a new differential backup, you don't need previous differential backups or transaction log backups. Each differential backup contains all changes since the last full backup.
  • Once you have a new transaction log backup, you still need previous transaction log backups. Transaction log backups record new log entries since the last transaction log was taken.

Note that, for maximum safety, you should keep your previous full and differential backups until a new one is finished. If you overwrite your previous full backup with the new one, then as soon as SQL Server starts writing your full backup until it finishes you have no backup. With the full backup they are based on gone, differential and transaction log backups are useless.

If you really want to take that risk: To write to the same backup file, overwriting any existing backups in it, use the following options:

WITH INIT, SKIP, FORMAT

My recommendation: Just as noted by sp_BlitzErik in the comments, use Ola Hallengren's free maintenance scripts, as they will do all the "heavy lifting" for you.

Just to cover the option, depending on the edition of SQL Server you're using, keep in mind that you probably have the option to set up SQL Server maintenance plans to do these backups. Those include a task that will remove files older that however much time you specify. However, I'm assuming that you haven't mentioned those because you either don't have access to use those, or don't want to use them.

If both of those options are out, but you can write a script (in the language of your choice) to delete files more than 2 days old, and can hold up to two days' worth of your backup files on disk:

  1. Set up your jobs as planned, with one change: append the type of backup and the current date and time to the file names. For example:

    BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDB_FULL_'
          + REPLACE(REPLACE(REPLACE(CONVERT(varchar(30), GETDATE(), 120),'-',''),':',''),' ','_')
          + '.bak'
WITH CHECKSUM;

    This would create a full backup file named F:\MSSQLSERVER\BU\MyDB_FULL_20170928_191216.bak in your chosen directory (if run today, at 7:12:16 PM).

    I would use MyDB_DIFF_ as the prefix for differential backups, and MyDB_TRAN_ for transaction logs. Note that full and differential backups should be have the extension .bak, while transaction log backups would have the extension .trn.

  2. Then, set up a "clean-up" job to run right before you run your full backup. This will run your script that deletes any files in the F:\MSSQLSERVER\BU\ folder that are more than 48 hours old. This should ensure that you have at least one full backup, and the differential and t-log backups that depend on it, even if the script was accidentally run at noon instead of midnight.

    By running it right before you run your full backup, you also ensure that you have the maximum available space for that full backup.

    You can always choose to be fancier with the "clean-up" script; since each of the three file types starts with the same prefix, you could delete differential and transaction log backups more than (say) 3 hours old, and still have a good cushion.

Unless you already have a script that would do the deletions for you around to use, I again highly recommend you use Ola Hallengren's scripts. Keep in mind that, unlike whatever you could whip up on your own, they've already been tested by a lot of people the world over.

Related Question