I have a requirement of instantiating a job which will copy and move the
newly created file once a file has been created in a folder. How can I
perform this in SQL Server. Can you please give your thoughts about this.
What I was planning was to write a visual basic code which will pool the
files getting created into the folder and accordingly copy and paste into
another folder. I feel this is not a better method.
I really wanted a queuing system to be implemented.Hi, there's a script I am using to copy files:
USE [msdb]
GO
/****** Object: Job [CopyToCluster] Script Date: 03/20/2006 15:24:31 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 03/20/2006
15:24:31 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database
Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL',
@.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'CopyToCluster',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=0,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'Copies all files rom the local backup directory to
\\datacluster\\Backup',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'YOUR_USER', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Copy files] Script Date: 03/20/2006 15:24:31 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Copy
files',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=2,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'CmdExec',
@.command=N'c:\copybackups.cmd $(DATE)$(TIME)',
@.flags=4
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'CopyBackupFilesSched',
@.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=20060106,
@.active_end_date=99991231,
@.active_start_time=50000,
@.active_end_time=235959
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:
and this is copypackups.cmd:
rem PR 2006-01-09
rem This batch file copies daily backups of databases to network location
rem
@.echo . > c:\copybackups.log
@.echo %1 Copying files from c:\SQLBackup to \\datacluster\Backup... >>
c:\copybackups.log
xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /L /R /H /D /V /Y /F /C >>
c:\copybackups.log
xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /R /H /D /V /Y /F /C >>
c:\copybackups.log
@.echo done >> c:\copybackups.log
@.echo . >> c:\copybackups.log
Note that this file does not use date and time passed by the job. I have
another job that removes older files from local directory.
HTH
Peter|||Thank you very much for your response.
What I need is watch for newly added files to a folder. The moment a new
file is added, I need to copy and paste into another location. This is
something like a service which keeps on monitoring a folder for new files
coming in.
Finally once the day is fininshed I will have a copy of the master folder in
another location also. I need to do this on receipt of each file, not finall
y
at the end of a day.
Thanks & Regards,
VB Babunath
"Rogas69" wrote:
> Hi, there's a script I am using to copy files:
> USE [msdb]
> GO
> /****** Object: Job [CopyToCluster] Script Date: 03/20/2006 15:24:31 ******/
> BEGIN TRANSACTION
> DECLARE @.ReturnCode INT
> SELECT @.ReturnCode = 0
> /****** Object: JobCategory [Database Maintenance] Script Date: 03/20/2006
> 15:24:31 ******/
> IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Databa
se
> Maintenance' AND category_class=1)
> BEGIN
> EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL',
> @.name=N'Database Maintenance'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> END
> DECLARE @.jobId BINARY(16)
> EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'CopyToCluster',
> @.enabled=1,
> @.notify_level_eventlog=2,
> @.notify_level_email=0,
> @.notify_level_netsend=0,
> @.notify_level_page=0,
> @.delete_level=0,
> @.description=N'Copies all files rom the local backup directory to
> \\datacluster\\Backup',
> @.category_name=N'Database Maintenance',
> @.owner_login_name=N'YOUR_USER', @.job_id = @.jobId OUTPUT
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> /****** Object: Step [Copy files] Script Date: 03/20/2006 15:24:31 ******/
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Co
py
> files',
> @.step_id=1,
> @.cmdexec_success_code=0,
> @.on_success_action=1,
> @.on_success_step_id=0,
> @.on_fail_action=2,
> @.on_fail_step_id=0,
> @.retry_attempts=2,
> @.retry_interval=0,
> @.os_run_priority=0, @.subsystem=N'CmdExec',
> @.command=N'c:\copybackups.cmd $(DATE)$(TIME)',
> @.flags=4
> 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'CopyBackupFilesSched',
> @.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=20060106,
> @.active_end_date=99991231,
> @.active_start_time=50000,
> @.active_end_time=235959
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_nam
e
> = N'(local)'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> COMMIT TRANSACTION
> GOTO EndSave
> QuitWithRollback:
> IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
> EndSave:
>
> and this is copypackups.cmd:
> rem PR 2006-01-09
> rem This batch file copies daily backups of databases to network location
> rem
> @.echo . > c:\copybackups.log
> @.echo %1 Copying files from c:\SQLBackup to \\datacluster\Backup... >>
> c:\copybackups.log
> xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /L /R /H /D /V /Y /F /C >>
> c:\copybackups.log
> xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /R /H /D /V /Y /F /C >>
> c:\copybackups.log
> @.echo done >> c:\copybackups.log
> @.echo . >> c:\copybackups.log
>
> Note that this file does not use date and time passed by the job. I have
> another job that removes older files from local directory.
> HTH
> Peter
>
>
