Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Let's say I have a daily maintainance task that:

  1. Backups all databases and then
  2. removes backups that are older than 3 days.

Now let's say the first backup at day 1, starting at 10:00, results in the following files

db1.bak 2012-01-01 10:04
db2.bak 2012-01-01 10:06

Now let's say at day 4 the first step of the maintainance task (backup the DBs) happens to finish at 10:05. Will SQL Server

  • delete db1.bak and keep db2.bak (would be logical, but might be surprising for the user) or
  • keep both or
  • remove both?
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted
+50

The maintenance plan will remove backup files using the time that the cleanup task is started as it generates the SQL at that time. So in the three options you described the first case is what would happen.

I created a example plan that removes files over 3 days old and ran the job. From the log output below you can see that the job ran at 12:25 on 5/7/2013 and it removed files older than 12:25 on 5/4/2013. The T-SQL below shows an approximate command for how the date might be generated.

query:
select GETDATE()
select DATEADD(DAY, -3, GETDATE() )

results:
-----------------------
2013-05-07 12:51:52.073

(1 row(s) affected)

-----------------------
2013-05-04 12:51:52.073

(1 row(s) affected)

Maintenance Plan: MaintenancePlan Duration: 00:00:02 Status: Succeeded. Details:

Backup Database on Local server connection Databases: DBA Type: Full Append existing Task start: 2013-05-07T12:25:03. Task end: 2013-05-07T12:25:05. Success Command:BACKUP DATABASE [DBA] TO DISK = N''N:\Apps\MSSQL10.INSTANCE\MSSQL\Backup\DBA_backup_2013_05_07_122503_6738713.bak'' WITH NOFORMAT, NOINIT, NAME = N''DBA_backup_2013_05_07_122503_6738713'', SKIP, REWIND, NOUNLOAD, STATS = 10

GO

Maintenance Cleanup on Local server connection Cleanup Database Backup files Age: Older than 3 Days Task start: 2013-05-07T12:25:05. Task end: 2013-05-07T12:25:05. Success Command:EXECUTE master.dbo.xp_delete_file 0,N''N:\Apps\MSSQL10.INSTANCE\MSSQL\Backup'',N''BAK'',N''2013-05-04T12:25:05''

GO

share|improve this answer
    
Thank you. Unfortunately, xp_delete_file is intentionally undocumented. So, what is the conclusion? Can we say with certainty that, of the three options presented in my question, option 1 correctly describes the behaviour of the SQL Server maintenance plan? –  Heinzi May 8 '13 at 17:53
    
That code is what the Microsoft Maintenance Plan generates; I think they'll make an exception for themselves on using undocumented stored procedures. I've updated the answer to more specifically answer your question. –  Jason Cumberland May 10 '13 at 3:24
add comment

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.