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.

I have a server running Windows Server 2008 SP2, and SQL Server 2005 Express.

There is a daily backup which runs via the Windows Task Scheduler. It keeps the last 7 days of backups and deletes the earlier ones automatically.

How/where do I change the number of days of backups that it keeps ?

UPDATE: As per comments to the question, here is the contents of the script that is running:

exec expressmaint
   @database      = 'xxxx', 
   @optype        = 'DB',
   @backupfldr    = 'c:\sql_data\backup',
   @reportfldr    = 'c:\sql_data\backup\reports',
   @verify        = 1,
   @dbretainunit  = 'weeks',
   @dbretainval   = 1,
   @rptretainunit = 'weeks',
   @rptretainval  = 1,
   @report        = 1
go

I would now like to store 4 days of backups instead of 7

share|improve this question
    
@RandolphWest since he is using SQL Server Express it does not include SQL Server Agent service. –  Shawn Melton Jul 30 '12 at 7:50
    
We cannot really give you that answer. The scheduled task is probably configured to call a script, you will need to look through that script and figure out what it is doing to determine where to change the number of days. If you want to post the script or command it is executing we might be able to point you in the right direction. –  Shawn Melton Jul 30 '12 at 7:52
    
Shawn, good catch. I'll delete my comment(s). –  user3914 Jul 30 '12 at 7:57
    
@ShawnMelton - thanks - in Task Scheduler, it just shows the Task Name and when it was last run etc. I don't see where it shows the name of a script - could you point me in the right direction ? –  P Sellaz Jul 30 '12 at 11:02
    
What backup software are you using? Telling us that it runs via the task scheduler will make most assume it's Windows built-in backup but we shouldn't have to assume anything. –  John Gardeniers Jul 30 '12 at 12:00

1 Answer 1

up vote 1 down vote accepted

So your task is calling a T-SQL script that is being executed against the instance of SQL Server. It is calling a stored procedure that has the T-SQL commands to do the backups and cleanup, at least according to the variables (all the "@words"). I would guess this stored procedure expressmaint is stored in the master database. If you are familar with T-SQL you can open up SSMS, find this stored procedure, right-click on it and then left-click on "modify". This will open up the procedure and show you all the code it is executing. Making your changes and then clicking "execute" (or F5 key) will update the procedure, but don't do this until you are sure what you are changing.

From what the script looks like I would gather that @dbretainunit = 'weeks', @dbretainval = 1, are the variables that pertain to how long your backups are kept. You will need to determine in the stored procedure how it is handling these variables. It might be basic and just simply change it to @dbretainunit = 'days', @dbretainval = 4, per your requirement. However you will not know that until you verify what it does.

share|improve this answer
    
Thank for the pointers - you were quite right and it was that simple ! –  P Sellaz Jul 30 '12 at 21:40

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.