May 30, 2006 at 2:50 pm
Hi all,
How can i copy the log file and make it run everytimne log gets back up. I have the server on STD edition so have to copy manually.
the back up is done by maitainance plan.
and back up of log is like
test_tlog_200605241600.TRN
I am trying to run command like bellow but not able to make it work
EXEC master..xp_cmdshell 'copy Y \\actualserver\f$\logs\test\test_tlog_'+ +getdate()+'.TRN'
\\standbyserver\d$\MSSQL\BACKUP\LOG\test_LOG.TRN'
it happens every 30 min so how can i copy this files.
Thanks
May 30, 2006 at 3:10 pm
Hi,
I do have file copy as a second step to some of my jobs. I don't use xp_cmdshell explicitly. I create a step of Operating System Command type. As a command I enter:
copy c:\myfolder\myfile \\myserver.mycompany.com\myshare\myfile /Y
The share is set up to give write access to both SQL Server and Agent domain startup accounts.
/Y at the end of Copy command stays for Yes to overwrite files
Regards,Yelena Varsha
May 30, 2006 at 3:41 pm
yeah that is true but how can i make it automated for job:
because the log file i want to copy has the format like
test_tlog_200605241600.trn and then next one like test_tlog_200605241630.trn
May 31, 2006 at 1:58 am
Hi,
I wrote my own log shipping (with a little help from some of the guys on this site)
Step 2 (after the database backup is as follows)
(G:\backup\ is my shared drive...)
declare @filepath varchar(255)
SET @filepath='xcopy /M /Y "'+(SELECT TOP 1 physical_device_name
FROM MSDB.DBO.backupmediafamily
ORDER BY media_set_id DESC)+'" "G:\backup\"'
select @filepath
DECLARE @result int
EXEC @result = master..xp_cmdshell @filepath
David
If it ain't broke, don't fix it...
May 31, 2006 at 8:41 am
is it be possible to save it as different name than the file sucha as test_tlog_200605241600.trn
Can i save it as test_log.trn insteade test_tlog_200605241600.trn!!
if then how?
May 31, 2006 at 8:56 am
Yes, it is possible if you create your own transaction log backup job. If you rely on maintenance plans, SQL Server will name your files for you. Create your own job and run:
BACKUP LOG DatabaseName
TO DISK = 'C:\mssql\backup\Test_Log.trn'
Then create a second step to copy your file off to the second server. At this point, you can then rename the file to include the date/time before the next backup runs so you avoid overwriting the backup file.
May 31, 2006 at 9:40 am
Returning to your original method, here is one way to do it:
DECLARE @filepath varchar(300)
, @cmd varchar(300)
, @prevPos int
, @pos int
--EXEC master..xp_cmdshell 'copy Y \\actualserver\f$\logs\test\test_tlog_'+ +getdate()+'.TRN'
-- \\standbyserver\d$\MSSQL\BACKUP\LOG\test_LOG.TRN'
SET @filepath = '\\actualserver\f$\logs\test\test_tlog_' + getdate() + '.TRN'
SET @cmd = 'copy /Y "' + @filepath + '" "\\standbyserver\d$\MSSQL\BACKUP\LOG\'
-- Strip path portion
WHILE CharIndex('\', @filepath) > 0
BEGIN
SET @filepath = Stuff(@filepath, 1, CharIndex('\', @filepath), '')
END
-- Strip file extension (from rightmost period), leave filename only
SET @pos = CharIndex('.', @filepath)
WHILE @pos > 0
BEGIN
SET @prevPos = @pos
SET @pos = CharIndex('.', @filepath, @prevPos + 1)
SELECT @pos, @prevPos
END
IF @prevPos > 0
BEGIN
SET @filepath = Left(@filePath, @prevPos-1)
END
PRINT @filepath
-- Format and run the statement
SET @cmd = @cmd + @filepath + Convert(varchar(8), GetDate(), 112) + Stuff(Convert(varchar(5), GetDate(), 8), 3, 1, '') + '.TRN' + '"'
PRINT @cmd
DECLARE @result int
EXEC @result = master..xp_cmdshell @cmd
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply