April 23, 2015 at 10:49 am
declare @rc int, @dir nvarchar(4000)
exec @rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @dir output, 'no_output'
Select @dir AS [Backup Path] -- Gives the BAckup Path.
DECLARE @cmd1 sysname, @cmd2 sysname, @cmd sysname
Declare @extPAth varchar(max)
SET @extPAth = '\\servername\folder1\folder2\'
SET @cmd = '''copy ' + @dir + '\*.*'+ ' '+ @extPAth +''''
--SET @cmd2 = @extPAth
--SET @cmd = @cmd1 + ' '+ ''+@cmd2 + ''
SELECT @cmd
--EXECUTE master..xp_cmdshell @cmd, 'no_output'
EXECUTE master..xp_cmdshell @cmd
Thanks.
April 23, 2015 at 10:59 am
Make the following change to your code above:
SET @cmd = '"copy ' + @dir + '\*.*' + ' ' + @extPAth +'"'
April 23, 2015 at 11:02 am
Worked. Many thanks Lynn...........Good Day
Thanks.
April 27, 2015 at 9:43 am
Hi Lynn,
I am tryoing to duplicate the same file with a different name (basically want to add timestamp to it and want to keep the oputput file for 5 days and then want to delete it. BAsically, do not want to disturb the original file as thats the job output file.
Somehow, the copy command does not work. NO error...But files are not renaming.
Can you please suggest what I am doing wrong here? 🙁
SET nocount ON
DECLARE @path VARCHAR(max)
DECLARE @CMD VARCHAR(4096)
DECLARE @rc INT
DECLARE @DEFAULTS TABLE
(
value VARCHAR(max),
data VARCHAR(max)
)
INSERT INTO @DEFAULTS
EXEC @rc = master.dbo.Xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory'
--SELECT *
--FROM @DEFAULTS
SELECT @path = data
FROM @DEFAULTS
WHERE value = 'BackupDirectory'
SELECT @path = @path + '\JOBOUTPUT'
-- SELECT @CMD = 'xp_cmdshell ''del "' + @PATH2 + '\*.TXT*"''' + ',no_output'
--SELECT @CMD
--EXEC(@CMD)
--SET nocount OFF
declare @chkdirectory as varchar(4000)
declare @folder_exists as int
set @chkdirectory = @path
declare @file_results table
(file_exists int,
file_is_a_directory int,
parent_directory_exists int
)
insert into @file_results
(file_exists, file_is_a_directory, parent_directory_exists)
exec master.dbo.xp_fileexist @chkdirectory
select @folder_exists = file_is_a_directory
from @file_results
--script to create directory
if @folder_exists = 0
begin
print 'Directory is not exists, creating new one'
EXECUTE master.dbo.xp_create_subdir @chkdirectory
print @chkdirectory + ' created on' + @@servername
end
else
print 'Directory ('+ @chkdirectory+') already exists'
--SET nocount OFF
Print @chkdirectory
declare @dir VARCHAR(4096)
SET @dir = 'DIR ' + '"' + @chkdirectory + '"'
Print @dir
CREATE TABLE #tempList (Files VARCHAR(500))
INSERT INTO #tempList
EXEC MASTER..XP_CMDSHELL @dir
--delete all directories
DELETE #tempList WHERE Files LIKE '%<dir>%'
--delete all informational messages
DELETE #tempList WHERE Files LIKE ' %'
--delete the null values
DELETE #tempList WHERE Files IS NULL
--get rid of dateinfo
UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))
--get rid of leading spaces
UPDATE #tempList SET files =LTRIM(files)
--split data into size and filename
SELECT LEFT(files,PATINDEX('% %',files)) AS Size,
RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FileName
FROM #tempList
drop table #tempList
declare @out_fileDate VARCHAR(1024)
DECLARE @fileNameNew VARCHAR(1024) -- filename of new error report
DECLARE @fileNameOld VARCHAR(1024) -- filename of old error report
SELECT @out_fileDate = Cast(Datepart(yyyy, Getdate()) AS CHAR(4)) +
+ RIGHT(Cast(100+Datepart(mm, Getdate()) AS CHAR(3)
),
2)
+
+ RIGHT(Cast(100+Datepart(dd, Getdate()) AS CHAR(3)
),
2)
+ '_'
+ cast(DATEPART(hour, GETDATE()) as varchar) + '_' + cast(DATEPART(minute, GETDATE()) as varchar)
--Print @out_fileDate
DECLARE curs_oldfiles1 CURSOR FOR
SELECT a.output_file_name
FROM msdb.dbo.sysjobsteps a
WHERE a.output_file_name <> '[NULL]'
ORDER BY a.output_file_name
OPEN curs_oldfiles1
FETCH curs_oldfiles1 INTO @fileNameOld
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileNameNew = @fileNameOld + '_' + @out_fileDate + '.TXT'
set @cmd = '"copy ' + Ltrim(Rtrim(@fileNameOld)) + ' ' + Ltrim(Rtrim(@fileNameNew)) + '"'
--exec (@cmd)
print @cmd
-- EXECUTE master..xp_cmdshell @cmd
FETCH curs_oldfiles1 INTO @fileNameOld
END
CLOSE curs_oldfiles1
DEALLOCATE curs_oldfiles1
Thanks.
April 27, 2015 at 9:52 am
I don't have time right now to look at this with a critical eye (I am at work), but from what you are describing, this almost sounds like something PowerShell could easily handle. Have you considered looking at this as an alternative to T_SQL and xp_cmdshell?
April 27, 2015 at 9:59 am
Need to write in T SQL alone.
Thanks.
April 27, 2015 at 10:01 am
Only these section have some logical error, I suppose:-
--Print @out_fileDate
DECLARE curs_oldfiles1 CURSOR FOR
SELECT a.output_file_name
FROM msdb.dbo.sysjobsteps a
WHERE a.output_file_name <> '[NULL]'
ORDER BY a.output_file_name
--SELECT ltrim(rtrim(left(a.output_file_name, len(a.output_file_name) - charindex('.', reverse(a.output_file_name))) ))
-- FROM msdb.dbo.sysjobsteps a
-- WHERE a.output_file_name <> '[NULL]'
-- ORDER BY a.output_file_name
-- FOR READ only
OPEN curs_oldfiles1
FETCH curs_oldfiles1 INTO @fileNameOld
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileNameNew = @fileNameOld+ '_' + @out_fileDate + '.TXT'
set @cmd = '"copy ' + Ltrim(Rtrim(@fileNameOld)) + ' ' + Ltrim(Rtrim(@fileNameNew)) + '"'
--exec (@cmd)
print @cmd
--EXECUTE master..xp_cmdshell @cmd
FETCH curs_oldfiles1 INTO @fileNameOld
END
CLOSE curs_oldfiles1
DEALLOCATE curs_oldfiles1
PRINT Statement produced output as:-
exec master..xp_cmdshell "copy C:\Backups\MSSQLSERVER\OUTPUT\BackupDatabases.TXT C:\Backups\MSSQLSERVER\OUTPUT\BackupDatabases.TXT_20150427_10_58.TXT"
Thanks.
June 15, 2016 at 10:30 am
I am getting the same error
''copy' is not recognized as an internal or external command,
--Here is my code what am i doing wrong
DECLARE @date VARCHAR (200)
DECLARE @DATABASENAME VARCHAR (200)
DECLARE @DESTINATIONLOCATION VARCHAR (200)
DECLARE @cmd VARCHAR (150)
DECLARE @SOURCEFILE VARCHAR (150)
SET @date = (SELECT CONVERT(char(10), GetDate(),126) +'.ls'+'"')
SET @DATABASENAME='netFORUMProd_Full_'
SET @DESTINATIONLOCATION='\\EVDVNFDBV1\Databases'''
SET @SOURCEFILE=' "\\NFPRODDBSQL\BackupetFORUMProd\'
SET @cmd= '''copy' + @SOURCEFILE + +@DATABASENAME + @date +space(1)+ @DESTINATIONLOCATION
select @cmd
EXEC master.dbo.xp_cmdshell @Cmd;
June 15, 2016 at 12:59 pm
In DOS, don't put your quotes (") around the whole command. Put them around the source and destination filenames.
Example:
copy "C:\Backups\MSSQLSERVER\OUTPUT\BackupDatabases.TXT" "C:\Backups\MSSQLSERVER\OUTPUT\BackupDatabases.TXT_20150427_10_58.TXT"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply