We've been having this nightly backup job on SQL Server 2012 but it appends to the
.bak file instead of overwriting, so while googling I discovered
I noticed that I can delete several backups at once prior than
I'm thinking in keeping 3 days thus deleting like this:
declare @dt datetime select @dt=getdate()-3 EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\',N'bak',@dt,1
All seems ok, but for the backup, can I create filenames like this? (note the @today in CONCAT() )
declare today varchar(10); SELECT @today=CONVERT(char(10), GetDate(),126); BACKUP DATABASE perfMaster TO DISK = CONCAT('D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\perfMaster_',@today,'.bak' WITH NOFORMAT, NOINIT, NAME = N'Full Database perfMaster Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 20
Or what methods do you employ to accomplish backups with some days history?