Sql-server – Append to Media Vs Overwrite an existing media

backupsql serversql server 2014

I have close to 2 TB Database PROD and the database backup size is too huge.

Everytime I take a backup it gets appended and when I check on it Microsoft sites says

By appending another backup set to existing media, the previous
contents of the media remain intact, and the new backup is written
after the end of the last backup on the media.

What is the major use of this two options.

Append to Media Vs Overwrite an existing media

Best Answer

WITH NOINIT allows you to retain multiple backups in a single file. WITH INIT makes SQL Server overwrite the contents of the backup file, if it contains an existing backup. See the Microsoft Docs for more details about the various WITH options for BACKUP DATABASE.

Personally, I prefer to keep multiple files with unique names if I'm required to keep multiple backups. Typically, I'd append the date to the name of each file so I can easily determine which backup files are now considered "out of date". Whether you do that should be dictated by your recovery point objectives and recovery time objectives.

So my backup command would look like:

DECLARE @date date;
DECLARE @filename varchar(260);
SET @date = GETDATE();
SET @filename = 'F:\Backups\xyz\xyz-' + CONVERT(varchar(30), @date, 112) + '.bak';

BACKUP DATABASE [xyz] 
TO DISK = @filename
WITH INIT;

Each day, a uniquely named backup file will be created in F:\Backups\xyz similar to:

F:\Backups\xyz\xyz-20170411.bak

Appending to media can make restoring multiple log backups easier since you won't need to programmatically determine the file names during the restore process.

Related Question