Only one copy of backup file

[purepest]

Hi

I have a script that backs up a database to a specified location. The problem is that is overwrites the previous file and I want it to maybe have 5 copies of the previous nights backup before it gets overwritten

Here is the script

CREATE PROCEDURE sp_FullNetworkBackup AS
BACKUP DATABASE [nice_cls] TO [nice_cls_remote] WITH INIT , NAME =
N'nice_cls backup', SKIP , STATS = 10, NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='nice_cls'and type!='F'
and backup_set_id=(select max(backup_set_id) from msdb..backupset where
database_name='nice_cls')
RESTORE VERIFYONLY FROM [nice_cls_remote] WITH FILE = @i
BACKUP DATABASE [nice_audit] TO [nice_audit_remote] WITH INIT , NAME =
N'nice_audit backup', SKIP , STATS = 10, NOFORMAT
select @i = position from msdb..backupset where database_name='nice_audit'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where
database_name='nice_audit')
RESTORE VERIFYONLY FROM [nice_audit_remote] WITH FILE = @i
GO

Thanks for any help

 

[ptheriault]

You want to keep 5 days worth or you want 5 copies of the current backup?

The "with init" option you have in your job is what is causing this job to overwrite the backup sets in your device.
Remove WITH INIT and it will keep adding backup sets to the device until you run a backup that specifies with init.

- Paul

- If at first you don't succeed, find out if the loser gets anything.