SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date of oldest file in a windows folder
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 04/22/2013 :  14:35:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/22/2013 :  14:57:31  Show Profile  Reply with Quote
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
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 04/22/2013 :  15:56:32  Show Profile  Reply with Quote
that's what i was afraid of.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/22/2013 :  21:41:33  Show Profile  Reply with Quote
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
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 04/23/2013 :  09:32:11  Show Profile  Reply with Quote
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

Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 04/23/2013 :  10:26:55  Show Profile  Visit jackv's Homepage  Reply with Quote
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

Edited by - jackv on 04/24/2013 06:46:32
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 04/24/2013 :  10:43:13  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/24/2013 :  10:53:01  Show Profile  Reply with Quote
Thats a good news... keep it up :)

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000