2 added 764 characters in body
source | link

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 nameNAME for the backup set, but on SQL Server 2014, I could not get that method to work.)

If you want toYou could, of course, avoid the issue entirely by using a unique filename. Many people build the filename dynamically so that, you'll need it is guaranteed to check forbe unique and reflects - at least roughly - the file name yourself before specifying ittime 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

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.

(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.)

If you want to avoid that, you'll need to check for the file name yourself before specifying it.

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
1
source | link

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.

(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.)

If you want to avoid that, you'll need to check for the file name yourself before specifying it.