patternsqlMinor
limiting sql server backups inside .bak file
Viewed 0 times
filebackupssqllimitingserverbakinside
Problem
I have a job scheduled to run a few times everyday. The job backups my databases, like:
I can track the backups in the file by running:
And then I can restore a database file by using something like:
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.
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.
Solution
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:
--====================================================================
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_fileCode Snippets
master.dbo.xp_delete_fileContext
StackExchange Database Administrators Q#29455, answer score: 3
Revisions (0)
No revisions yet.