March 23, 2018 at 10:33 am
I am wondering if someone can help me out here. We have backups in place but since we don't have retention policy in place, we just kept all our backups. Now we decided to keep weekly full backups for 5 months, full+diff for 3 months and full+diff+log backups for a month. Let me share some screen shots on how those backups are stored so it will help. Also sharing the script in case if it helps. USE [CommonDB]
GO
/****** Object: StoredProcedure [dbo].[backup_database] Script Date: 3/23/2018 11:20:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[backup_database]
-- Add the parameters for the stored procedure here
@dbName sysname = null,
@folder nvarchar(2048) = null,
@diff_only tinyint = 0, -- 1 = yes, 0=no
@full_only tinyint = 0, /* 0=no, 1=yes */
@copy_only tinyint = 0 -- 1=yes, 0=no
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @path nvarchar(2048)
declare @now datetime
declare @sql nvarchar(max)
set @now = getdate()
if @folder is null
begin
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\MSSQLServer',
N'BackupDirectory',
@folder output;
end;
set @path = @folder+'\'+QUOTENAME(@dbName)+'\'
+RIGHT('0000'+CONVERT(nvarchar,DATEPART(yyyy,@now)),4)
+RIGHT('00'+CONVERT(nvarchar,DATEPART(ww,@now)),2)
exec master.sys.xp_create_subdir @path
set @sql = 'BACKUP DATABASE '+QUOTENAME(@dbName)+' TO DISK='''+@path+'\'
+QUOTENAME(@dbName)+'_'+CONVERT(nvarchar, @now, 112)+'_'
+RIGHT('0000'+CONVERT(nvarchar, DATEPART(hh,@now)),2)
+RIGHT('00'+CONVERT(nvarchar, DATEPART(mi, @now)),2)
+RIGHT('00'+CONVERT(nvarchar, DATEPART(ss,@now)),2)
+CASE WHEN @diff_only = 1 THEN N'.dif' ELSE N'.bak' END
+''' with stats=10'
-- Can't do a differential on master db
if @diff_only = 1 and UPPER(@dbName) <> N'MASTER' AND @full_only = 0
begin
set @sql = @sql + ', DIFFERENTIAL'
end;
if @copy_only = 1
begin
set @sql = @sql + ', copy_only'
end;
exec(@sql)
END
March 23, 2018 at 10:34 am
ok, so what's the question?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 23, 2018 at 11:44 am
Silly me. I am not sure if some one can help me write a script? I am not sure where to start from.
March 23, 2018 at 12:27 pm
NewBornDBA2017 - Friday, March 23, 2018 11:44 AMSilly me. I am not sure if some one can help me write a script? I am not sure where to start from.
So you want is to delete any .bak files older than five months, any .dif files older than three months and any .trn files older than one month.
Not sure what is allowable in your environment but you can think about how you would do that from the command line if using xp_cmdshell or how would you do that with Powershell. You could also use xp_delete_file with the usually warnings of it being an undocumented procedure.
You can find scripts out there using any one of those approaches - search on Delete Backups and add the word for which every approach you want to use (e.g. Delete Backups xp_cmdshell). If you search on just xp_delete_file you can find the parameters as well as examples of using that to delete backups.
Sue
March 23, 2018 at 2:22 pm
Thanks Sue. I actually found a script online and have been tweaking it but getting an error "Invalid Parameter" when I run it. I don't know what I am missing. Script is belowDECLARE @DBName VARCHAR(50);
DECLARE @path VARCHAR(256);
DECLARE @folder NVARCHAR(2048)
DECLARE @now datetime
DECLARE @DeleteDate DATETIME = DATEADD(wk,-1,GETDATE());
SET @path = @folder+'\'+QUOTENAME(@DBName)+'\'
+RIGHT('0000'+CONVERT(nvarchar,DATEPART(yyyy,@now)),4)
+RIGHT('00'+CONVERT(nvarchar,DATEPART(ww,@now)),2)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','LaunchPad','ImportedData','FarmReach','CommonDB','CRM');
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;
-- Loop through the list to backup each database.
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC master.sys.xp_delete_file 0,@path,'trn',@DeleteDate,0;
END
-- Clean up.
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
March 23, 2018 at 5:23 pm
Yeah...a few problems there. It probably starts having problems at this point:SET @path = @folder+'\'+QUOTENAME(@DBName)+'\'
+RIGHT('0000'+CONVERT(nvarchar,DATEPART(yyyy,@now)),4)
+RIGHT('00'+CONVERT(nvarchar,DATEPART(ww,@now)),2)
You haven't set your variables @folder, @Now, @DBName to anything so you have some nulls when you are concatenating, so @path is null. Resulting in passing NULL to xp_delete_file.
You also have an infinite loop more than a cursor 🙂 Remember you are doing that ugly row by row processing in a cursor so it's just doing fetches of each row. But your not fetching any next rows. So at the end of the the cursor block, you need to add the fetch before the END - something like:FETCH NEXT FROM db_cursor INTO @DBName
END
And then you are building the path outside of the cursor and using @DBName so that won't work as you are looping through the databases to get the name in the cursor so that is where you would need to build the path if that's part of the path to the backup files you want to delete.
It's pretty easy to do these in Powershell. A quick, rough example to give you an idea - if I want to delete any full backups in the path for all of the backups (so it applies when you do have the backups for each database in it's own folder) and delete any that are more than 30 days old, I can just do something like: $BackupLocation= "X:\SQLServer\Backup"
$FileExt = ".bak"
$DaysToKeep = 30
Get-ChildItem -Path $BackupLocation -Recurse|
where-object {$_.CreationTime -lt (Get-Date).AddDays(-$DaysToKeep) -and $_.Extension -eq $FileExt} |
Remove-Item
Sue
March 26, 2018 at 6:08 am
Sue_H - Friday, March 23, 2018 5:23 PMIt's pretty easy to do these in Powershell. A quick, rough example to give you an idea - if I want to delete any full backups in the path for all of the backups (so it applies when you do have the backups for each database in it's own folder) and delete any that are more than 30 days old, I can just do something like:$BackupLocation= "X:\SQLServer\Backup"
$FileExt = ".bak"
$DaysToKeep = 30
Get-ChildItem -Path $BackupLocation -Recurse|
where-object {$_.CreationTime -lt (Get-Date).AddDays(-$DaysToKeep) -and $_.Extension -eq $FileExt} |
Remove-ItemSue
+1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 27, 2018 at 2:19 pm
This script below seems to be working but I am wondering if someone can help out a little bit. I will try to be as thorough as possible when asking question because I tend to lose my audience because the way I ask questions or maybe question I am asking isn't providing enough information. In this script 201808 is the number of week in 2018 so I don't want to specify each week of the year like set @PATH = 'F:\SQL_BKP\['+@name+']\201809\'; set @PATH = 'F:\SQL_BKP\['+@name+']\201810\'; etc. So how do I get the value for each week like I am using the variable @Name which holds the DB name. Did I lose my audience?DECLARE @name VARCHAR(50); -- Database name
DECLARE @DeleteDate DATETIME = DATEADD(WK,-4,GETDATE()); -- Cutoff date
DECLARE @PATH VARCHAR(500)
select @name = name from sys.databases WHERE name IN ('Audit_User');
set @PATH = 'F:\SQL_BKP\['+@name+']\201808\';
EXEC master.sys.xp_delete_file 0,@PATH,'trn',@DeleteDate,0;
March 27, 2018 at 4:18 pm
NewBornDBA2017 - Tuesday, March 27, 2018 2:19 PMThis script below seems to be working but I am wondering if someone can help out a little bit. I will try to be as thorough as possible when asking question because I tend to lose my audience because the way I ask questions or maybe question I am asking isn't providing enough information. In this script 201808 is the number of week in 2018 so I don't want to specify each week of the year like set @PATH = 'F:\SQL_BKP\['+@name+']\201809\'; set @PATH = 'F:\SQL_BKP\['+@name+']\201810\'; etc. So how do I get the value for each week like I am using the variable @Name which holds the DB name. Did I lose my audience?DECLARE @name VARCHAR(50); -- Database name
DECLARE @DeleteDate DATETIME = DATEADD(WK,-4,GETDATE()); -- Cutoff date
DECLARE @PATH VARCHAR(500)
select @name = name from sys.databases WHERE name IN ('Audit_User');
set @PATH = 'F:\SQL_BKP\['+@name+']\201808\';EXEC master.sys.xp_delete_file 0,@PATH,'trn',@DeleteDate,0;
I'm still paying attention...unfortunately for you 🙂
But do you even have to do that finding the week number? Can't you change that last parameter to 1 and then point at the backup folder? That last parameter is to delete files in subfolders. I don't know how many levels deep it would go so that is why I asked if you even have to do that. Hopefully you have some test folder structure with backups in them for working with so I'd try to just point at the backup folder - sql_bkp or whatever that was in your folder structure.
It works when doing that with Powershell.....
To get week numbers, you just need to use datepart. Try this in your query: SELECT DATEPART(ww, @DeleteDate)
Sue
March 28, 2018 at 10:28 am
I am going to change the backup script to remove the week folder so it it will be 'F:\SQL_BKP\['+@name+']\backupfile.bak' which should make my life easier. Thanks SUE_H for the help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply