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' 
NAME = N'Full Database perfMaster Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 20

Or what methods do you employ to accomplish backups with some days history?

  • 6
    Any reason not to use Ola's scripts instead of rolling your own?
    – vonPryz
    Sep 6, 2013 at 12:22
  • I just found out about them. Already ran the Maintenace Script and checking the created jobs now. I'mre pretty happy! Thanks
    – cusco
    Sep 9, 2013 at 15:32

3 Answers 3


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

  • Thank you! I wasn't aware of ola scripts. I just installed them and am prety happy reading the jobs it created. I'm also reading trhough the website FAQ and others. Thank you!
    – cusco
    Sep 9, 2013 at 15:34
  • Glad it helped you. You should also know that Ola is quite responsive to problem reports
    – RLF
    Sep 9, 2013 at 15:58

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', 

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.
  • Thank you for your comments. I have read them and gave them some thought. D: is actually a old disk with old stuff in it. I ended up creating a D:\BACKUP Anyway, I'm going for the OLA script set. Seems to be thought and proven to do the job. Thank you again for your input.
    – cusco
    Sep 9, 2013 at 15:31

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.