Date of oldest file in a windows folder

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date of oldest file in a windows folder

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-04-22 : 14:35:35

I have a sql agent job which looks at the folder where backups are being stored and, using the forfiles command, deletes files in that folder which are older than a specified date.

I want to include TSql code in a seperate job which also looks at that folder and reports back using the following code to send emails to users:


DECLARE @TodaysDate DATETIME;
DECLARE @EmailBody VARCHAR(250);
DECLARE @EmailSubject VARCHAR(100);
DECLARE @EmailRecipients AS VARCHAR(250);
DECLARE @SQL AS NVARCHAR(2000);
DECLARE @Parm AS NVARCHAR(2000);
DECLARE @Error_Msg AS NVARCHAR(2000);
DECLARE @Name as VARCHAR(50);
DECLARE @ServerName AS VARCHAR(30);
SET @TodaysDate = GetDate();

SET @ServerName = @@ServerName;

SET @Name = @ServerName  + ' Full Back Up'

SET @EmailSubject = 'Success - ' + @Name + ' - ' + CAST(@TodaysDate AS VARCHAR(30));

SET @EmailBody = @EmailSubject;

SET @EmailRecipients = 'someone@email.com'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Send DBMail',
    @recipients = @EmailRecipients,
    @subject = @EmailSubject,
    @query = @SQL,
    @body = @EmailBody;

What type of sql code would i use to look at the date of the oldest file in that folder to include the data returned in the code above?

thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-22 : 14:57:31

I don't think this can be done directly using a sql code, rather you'll need to create a batch script and then execute it using the xp_cmdShell utility in sql server.

Cheers
MIK

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-04-22 : 15:56:32

that's what i was afraid of.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 21:41:33

If you are lucky enough to be on SQL 2012, you might give file tables a try. All my knowledge about it is academic, so I can't offer anymore insights: http://msdn.microsoft.com/en-us/library/ff929144.aspx

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-04-23 : 09:32:11

Actually, i may have found a new route to get something done with this. Take a look at the routine i'm using to purge the old backups. As it stands now, that exists as a standalone Agent job. If i move that TSQL script into a maintenance plan, then attach the above email script to this, one for success, one for failure, at least i can get a report of that via email, indicating that the purge did or did not happen.

What i would REALLY like, is for the script below to either report the number of files it deleted through the FORFILES command, or, better yet, report the filenames that it deleted. I'm thinking that getting those filenames that it's touching to drop into a temp table, then i can extract the data from that temp table and attach it to the success email as an excel or txt file, might be the direction i need to go.

Suggestions?



USE [msdb]
GO

/****** Object:  Job [Purge_Backups]    Script Date: 04/16/2013 12:16:48 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/16/2013 12:16:48 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Purge_Backups', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'NT AUTHORITY\SYSTEM', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Purge Differential Backups on W]    Script Date: 04/16/2013 12:16:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge Differential Backups on W', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'forfiles -p "W:\Backup\DIFF" -s -m *.* -d -3 -c "cmd /c del @file"', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Purge Full Backups on W]    Script Date: 04/16/2013 12:16:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge Full Backups on W', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'forfiles /p "W:\Backup\FULL" -s -m *.* -d -3 -c "cmd /c del @file"', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Purge Full Backups on X]    Script Date: 04/16/2013 12:16:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge Full Backups on X', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'forfiles -p "X:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Databases" -s -m *.* -d -7 -c "cmd /c del @file"', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Purge System Backups on X]    Script Date: 04/16/2013 12:16:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge System Backups on X', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'forfiles -p "X:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\System" -s -m *.* -d -31 -c "cmd /c del @file"', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Purge Transaction Log Backups on Y]    Script Date: 04/16/2013 12:16:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge Transaction Log Backups on Y', 
		@step_id=5, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'forfiles /p "Y:\Backup\TRAN" -s -m *.* -d -2 -c "cmd /c del @file"', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'backup_purge_sched', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20120426, 
		@active_end_date=99991231, 
		@active_start_time=30000, 
		@active_end_time=235959, 
		@schedule_uid=N'baf18d27-4e38-40a0-a8e5-c8843734ab41'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-04-23 : 10:26:55

You can use Powershell and SQL Server Agent to execute this process
#An example of listing the items, filtering name and lastwrite and using sort-object
Get-ChildItem -path "C:\projects\testing\laboratory"| select name,length,lastwritetime | sort-object -property lastwritetime

http://www.sqlserver-dba.com/2013/04/list-files-in-creation-time-order-with-powershell.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-04-24 : 10:43:13

As of now, the following script is getting me close. At this point it's only a matter of attaching the data from the final temp table as an excel sheet and emailing it to dba's.


-------------------------------------------------------------------------------------------
--                            Declare all the variables to be used
-------------------------------------------------------------------------------------------
Declare @FileName varchar(100),
        @DynDelete varchar(100),
        @path varchar(100),
        @DifDays varchar (100)=1  --specify number of days
-------------------------------------------------------------------------------------------
--                            enable the xp_cmdshell
-------------------------------------------------------------------------------------------
exec sp_configure 'show advanced options', 1 reconfigure exec sp_configure 'xp_cmdshell', 1 reconfigure
-------------------------------------------------------------------------------------------
--                            Create temp tables
-------------------------------------------------------------------------------------------
create table #dir
(dir varchar(255))
create table #tobdelted
(nameoffile varchar(255),filedate datetime) create table #tobdelted2 (nameoffile varchar(255),filedate datetime)

-------------------------------------------------------------------------------------------
--                            Insert files found into temp table (Please change path)
-------------------------------------------------------------------------------------------
insert into #dir exec master..xp_cmdshell 'dir "C:\Program Files\Microsoft SQL Server\MSSQL11.MATDATACENTRE\MSSQL\Backup"' 
set @path= '"C:\Program Files\Microsoft SQL Server\MSSQL11.MATDATACENTRE\MSSQL\Backup\"'
--change the folder path, do not forget the "\" at the end of the path
-------------------------------------------------------------------------------------------
--                            Insert files to be deleted into appropriate table
-------------------------------------------------------------------------------------------
insert into #tobdelted
            (filedate,nameoffile)
select convert(date,(select SUBSTRING(dir,1,10)),110),( SELECT SUBSTRING( (SELECT SUBSTRING( (SELECT SUBSTRING( (SELECT SUBSTRING(DIR, CHARINDEX(' ', DIR) + 1, 255)), CHARINDEX(' ', DIR),255)), CHARINDEX(' ', DIR),255)), (CHARINDEX(' ', DIR)-2),255)) from #dir where (dir like '%/%%/%') and (dir like '%.trn' or dir like '%.bak')

-------------------------------------------------------------------------------------------
--                            Insert files to be deleted into appropriate table
-------------------------------------------------------------------------------------------
insert into #tobdelted2
            (filedate,nameoffile)
select filedate,nameoffile
from #tobdelted
WHERE (DATEDIFF(month, (select convert(varchar(100),filedate,110)), GETDATE()) = 0 and DATEDIFF(day,(select convert(varchar(100),filedate,110)),getdate())>@DifDays) or ( DATEDIFF(month, (select convert(varchar(100),filedate,110)), GETDATE())!=0
)

-------------------------------------------------------------------------------------------
--                        use Curso to delete the files
-------------------------------------------------------------------------------------------
DECLARE curDelFile CURSOR
READ_ONLY
FOR

    SELECT[nameoffile]
    FROM #tobdelted2

OPEN curDelFile
FETCH NEXT FROM curDelFile INTO @FileName WHILE (@@fetch_status <> -1) BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        SET @DynDelete = 'DEL '+@path + @FileName + ' '
        EXEC master..xp_cmdshell @DynDelete
 -- print 'EXEC master..xp_cmdshell'+@DynDelete
    END
    FETCH NEXT FROM curDelFile INTO @FileName END CLOSE curDelFile DEALLOCATE curDelFile
-------------------------------------------------------------------------------------------
--                            Delete temp tables
-------------------------------------------------------------------------------------------
BEGIN TRY
 drop table #dir
    drop table #tobdelted
    drop table #tobdelted2
END TRY
BEGIN CATCH
 Print 'Tables do not exist'
END CATCH
 
-------------------------------------------------------------------------------------------
--                            Diable xp_cmdshell proc
-------------------------------------------------------------------------------------------
exec sp_configure 'xp_cmdshell', 0
reconfigure
exec sp_configure 'show advanced options', 0 reconfigure

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-24 : 10:53:01

Thats a good news... keep it up :)

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources