2

I'm using SQL Server's BACKUP command and I want the operation to generate an error if I attempt to write to an existing backup file.

By default, SQL Server seems to append to an existing file. There are various media options like INIT,SKIP,FORMAT - but no combination of those seem to do what I'm looking for, unless I'm reading it wrong: https://msdn.microsoft.com/en-us/library/ms186865.aspx

3

INIT will simply overwrite the existing file if it already exists, rather than append. However, you can prevent this from happening if you use the RETAINDAYS option, and set it to a really long time (you will have to maintain and manage these files as they get older, of course). If all of your backup files are initially set to last 10 years, and you always use INIT, you can't overwrite or append.

-- succeeds:
BACKUP DATABASE model TO DISK = 'c:\temp\m.bak'
  WITH INIT, RETAINDAYS = 3652;

-- fails:
BACKUP DATABASE model TO DISK = 'c:\temp\m.bak'
  WITH INIT, RETAINDAYS = 3652;

Error message:

Msg 4030, Level 16, State 1
The medium on device 'c:\temp\m.bak' expires on Apr 21 2025 9:12:27:000AM and cannot be overwritten.
Msg 3013, Level 16, State 1
BACKUP DATABASE is terminating abnormally.

If you want to avoid the extra maintenance involved with making your backups "last" extraordinarily long, you'll need to check for the file name yourself before specifying it (and possibly delete it first, or just pick a new name that doesn't conflict).

(The documentation suggests that you could do this using a unique NAME for the backup set, but on SQL Server 2014, I could not get that method to work.)

You could, of course, avoid the issue entirely by using a unique filename. Many people build the filename dynamically so that it is guaranteed to be unique and reflects - at least roughly - the time of the backup.

DECLARE @db SYSNAME = N'model', @filename NVARCHAR(255);

SELECT @filename = N'C:\temp\' + @db + N'_' 
  + REPLACE(REPLACE(GETDATE(), ' ', '_'),':','_') + '.bak';

SELECT @filename; -- C:\temp\model_Apr_22_2015__9_12AM.bak

BACKUP DATABASE @db TO DISK = @filename
WITH INIT; -- shouldn't need INIT but 
-- better to overwrite than append IMHO
  • Clever idea with the RETAINDAYS/INIT I'm actually planning to use unique file names, but the method that does the backup doesn't know that (it simply says "tell me what file to backup to"), and so I wanted to explicitly fail if a file already exists. In practice it would never fail, but the method would be resilient to misuse. Anyways, thanks for the awesome write-up! – SFun28 Apr 22 '15 at 14:01

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.