Sql-server – SQL Server backup job last 5 days

backupsql server

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 master.dbo.xp_delete_file

I noticed that I can delete several backups at once prior than date parameter

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?

Best Answer

I use Ola Hallengren's scripts these days to support some 50 SQL Servers. See: http://ola.hallengren.com/ and download his MaintenanceSolution.sql. These scripts have won several awards and are quite widely used.

The documentation is quite good, for guidance on backups and other database maintenance feature. You can choose frequency of backups, types of backups, retention of backups, and on and on. (In the past we used SQL Server's Maintenance Plans, then some of our own code, and now Ola's scripts.)

I have a one month retention of backups, some have more and some less. The backup retention period is specified in hours, not in days, so you do have a lot of flexibility.

One comment on deleting files is that you ideally should do each backup (or perhaps each day's backups) to an individual file. That way when you delete a backup, you only delete one backup (or one day's worth of backups.)

Related Question