Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
5  
Any reason not to use Ola's scripts instead of rolling your own? –  vonPryz Sep 6 '13 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 '13 at 15:32

3 Answers 3

up vote 5 down vote accepted

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

share|improve this answer
    
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 '13 at 15:34
    
Glad it helped you. You should also know that Ola is quite responsive to problem reports –  RLF Sep 9 '13 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', 
  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.
share|improve this answer
    
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 '13 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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