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.
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.CheersMIK |
|
|
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 |
|
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 TRANSACTIONDECLARE @ReturnCode INTSELECT @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)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @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 OUTPUTIF (@@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=0IF (@@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=0IF (@@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=0IF (@@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=0IF (@@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=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave: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-objectGet-ChildItem -path "C:\projects\testing\laboratory"| select name,length,lastwritetime | sort-object -property lastwritetimehttp://www.sqlserver-dba.com/2013/04/list-files-in-creation-time-order-with-powershell.htmlJack 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,nameoffilefrom #tobdeltedWHERE (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 CURSORREAD_ONLYFOR SELECT[nameoffile] FROM #tobdelted2OPEN curDelFileFETCH 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 #tobdelted2END TRYBEGIN CATCH Print 'Tables do not exist'END CATCH --------------------------------------------------------------------------------------------- Diable xp_cmdshell proc-------------------------------------------------------------------------------------------exec sp_configure 'xp_cmdshell', 0reconfigureexec 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 :)CheersMIK |
|
|
|
|
|
|
|