SQL Server backup job last 5 days

Question

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?

Answer 1

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.)

Answer 2

I use INIT and then my full backups are separate (and include a timestamp to immediately see when they were taken). I can use any method I want to clear out old files (C#, PowerShell, what have you, without having to know the internals of the backup files at all), rather than using xp_delete_file - which is undocumented, unsupported, and could change or be removed in a future release or even a service pack. I don't understand any advantage to stuffing multiple backup files into the same file (which you can't do if you name the file differently using the date anyway). I also don't know why you need to use CONCAT here, but in any case, you can't define an expression in the DDL that way. Why not just:

DECLARE @backup_file VARCHAR(255) = 'D:\Program Files\Microsoft SQL '
  + 'Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\perfMaster_'
  + CONVERT(CHAR(10), GETDATE(), 126) + '.bak';

BACKUP DATABASE perfMaster TO DISK = @backup_file
  WITH INIT, NAME = N'Full Database perfMaster Backup', 
  SKIP, NOREWIND, NOUNLOAD, STATS = 20;

I would also suggest:

• using COMPRESSION if your edition supports it.
• not storing your backups on the local D:\ drive. If you lose your hard disk, you've lost your database and the backups.

Answer 3

If you want the simplest approach, just create a Maintenance Plan within Management Studio. Expand the Management node, right click Maintenance Plans, and choose Maintenance Plan Wizard. The plan can be configured to perform backups using a separate file for each backup, and the Maintenance Cleanup task will delete the old ones after a specified number of days. It's a bit limited in terms of flexibility, but in the majority of cases, it handles the backup needs fine.