SQL Server Blog

We use Quest’s SQL LiteSpeed in our environment and its a fantastic tool.

I initially started off using the maintenance plans which look very similar to the maintenance plan designer that you will be used to in SQL Server 2005. It was a very basic plan consisting only of a full backup of all databases and then delete any backups over 1 day old.

However, we would occasionally run out of disk space before the backups completed which meant some manual backups and deletions the following morning. This is where you you may ask me why I didn’t just run the delete process before I took the backups. Well, call it paranoia, but I prefer to have a full backup in place before I delete the old one.

In the end, I decided on a single scheduled job. Within the job, I would have two steps for each database. The first step would be to backup the database and the second to delete any backups over 3 days.

To do this, I use the extended procedure xp_slssqlmaint. The full syntax would be:

EXECUTE master.dbo.xp_slssqlmaint N’-MAINTDEL -DELFOLDER “S:\MSSQL\Backups\master” -DELEXTENSION “BAK” -DELUNIT “3″ -DELUNITTYPE “days” -DELUSAGE’