Sql-server – limiting sql server backups inside .bak file

sql serversql-server-2012

I have a job scheduled to run a few times everyday. The job backups my databases, like:

BACKUP DATABASE [databasename] 
TO DISK = N'd:\sqlserver\backup\databasename.bak'

I can track the backups in the file by running:

RESTORE HEADERONLY FROM DISK = N'd:\sqlserver\backup\databasename.bak'

And then I can restore a database file by using something like:

RESTORE DATABASE databasename 
FROM DISK = N'd:\sqlserver\backup\databasename.bak' 
WITH FILE = 1455, 
RECOVERY, 
MOVE 'databasename_data' TO 'c:\...', 
MOVE 'databasename_log' TO 'c:\...'

But I have thousands of backups, and I'm reaching my storage limit.

Because I don't need so many backups, I'm wondering if there is a easy way to limit the backup file to keep only the last 90 backups.

Any tips?

UPDATE:

I have just one .bak file with n backups inside. I can read a specific backup using the withfile option in the restore statement. The bellow answers are supposing I have multiple .bak files. What I need to delete is a outdated backup inside the .bak file.

Best Answer

The easiest solution would be using a maintenance plan task to clean up old backup files. However, you could consider scripting your way out of this, using something like the following script:

--====================================================================
CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
@path nvarchar(256),
@extention nvarchar(10),
@age_hrs int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @DeleteDate nvarchar(50)
DECLARE @DeleteDateTime datetime

SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())

SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1
END
--============================
-- xp_delete_file information
--============================
-- xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such
-- as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is
-- not documented by MS. Just be aware that it will not delete just any file type

-- First argument is:
-- 0 - specifies a backup file
-- 1 - specifies a report file
-- (I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files
-- you're deleting with the third argument.)
--
-- Fifth argument is whether to delete recursively.
-- 0 - don't delete recursively (default)
-- 1 - delete files in sub directories
--====================================================================

Credit: http://www.sqlservercentral.com/Forums/FindPost1034057.aspx

There's also xp_delete_file:

master.dbo.xp_delete_file
Related Question