Automatically overwrite database backup

SQL Server Administration
1

Hi to all,

Is there any way to overwrite the job itself everyday? I do not want to overfill the drive with space.
I just want to know. (I have a full database backup)

2

how do you perform the full backup currently ?

3

BACKUP DATABASE db_name
TO DISK = 'address'
WITH FORMAT;
GO

4

Use the option INIT to overwrite the existing contents of a backup file. See the example below.

-- CREATE INITIAL BACKUP
BACKUP DATABASE [TestDatabase] 
	TO  DISK = N'B:\TestDatabase.bak' 
	WITH  INIT,  
	NAME = N'TestDatabase Backup 1';
GO

-- SEE WHAT IS IN THE BACKUP FILE
RESTORE HEADERONLY 
FROM DISK = N'B:\TestDatabase.bak'  ;
GO

-- BACKUP A SECOND TIME INTO THE SAME FILE WITH *** NOINIT ***
BACKUP DATABASE [TestDatabase] 
	TO  DISK = N'B:\TestDatabase.bak' 
	WITH  NOINIT,  
	NAME = N'TestDatabase Backup 2';
GO

-- SEE WHAT IS IN THE BACKUP FILE. YOU WILL SEE TWO
RESTORE HEADERONLY 
FROM DISK = N'B:\TestDatabase.bak'  ;
GO

-- BACKUP A SECOND TIME INTO THE SAME FILE WITH *** INIT ***
BACKUP DATABASE [TestDatabase] 
	TO  DISK = N'B:\TestDatabase.bak' 
	WITH  INIT,  
	NAME = N'TestDatabase Backup 3';
GO

-- SEE WHAT IS IN THE BACKUP FILE. YOU WILL SEE ONLY ONE SET, Backup 3.
RESTORE HEADERONLY 
FROM DISK = N'B:\TestDatabase.bak'  ;
GO

Couple of things to note:

  1. I am showing only the bare minimum options. For example, if you are doing this backup as an ad-hoc backup (outsie of your regularly scheduled backups) you would want to add the "WITH COPY_ONLY" option.

  2. If this is your regular scheduled backup, initializing the existing backup would not be a good idea. If for some reason, the initialize succeeded and the backup failed, you would be left with no backup.

5

To add to what JamesK posted: if you initialize the backup file and you have not copied the file to another location before you perform that initialization you have lost any ability to recover to a point in time (assuming you have transaction log backups) - or to a previous day (simple recovery).

If - for example - you find out someone performed a delete operation that completed after your backup has completed and you need to get that information back you no longer have any backup files available to restore to recover that data.

6

Piling on...

Don't overwrite the last backup. If the new backup fails and something is wrong with the system, you're dead. Always create a new backup and only delete the old backup after the new backup has been saved.

Also, if you're going to take the time to do a backup to begin with (and you should), then consider doing proper log file backups to make point-in-time restores possible.

And always remember... you don't need a backup plan because backups alone aren't what you should plan for. Instead, plan for doing a restore because that's the end goal.