July 28, 2009 at 11:52 am
Hi,
I would like to copy the Transaction log backups .trn files to another server imediately after the log backup job finishes. Right now I have the log backup ruuning. Could you please give me the script to add as a job step in log backup job to copy those .trn files to other server.
July 28, 2009 at 1:23 pm
I suggest you use the provided log shipping utilities and just schedule the copy job to run with a slight delay, e.g if the backup job runs on the hour, run the copy job at 1 minute past. You could also run the copy job more frequently than the backup and restore jobs.
---------------------------------------------------------------------
July 28, 2009 at 4:15 pm
I suggest you use the provided log shipping utilities and just schedule the copy job to run with a slight delay, e.g if the backup job runs on the hour, run the copy job at 1 minute past. You could also run the copy job more frequently than the backup and restore jobs.
If we configure log shipping it will ask for Secondary server for secondary database (we do not have Secondary SQL Sevrer insatnce). Basically I do not want use log shipping and Is there any way to copy the log backups as soon as the log backup completes. Is it not possible to copy the log backups with out using log shipping?
July 28, 2009 at 5:52 pm
You can use the ROBOCOPY command in a job to mirror the backup directory to another server.
ROBOCOPY is a command line tool available as part of the Windows Server 2003 Resource Kit Tools:
July 28, 2009 at 6:55 pm
Query the backup history tables to find the physical file names of all the .trn files backed up from the source database. Add a "WHERE filename NOT IN" clause and write a similar query (using a linked server to the other system) to get the names of all the .trn files restored to the destination database.
Copy the files using a file task if in SSIS, or possibly xp_cmdshell 'COPY ...' if in T-SQL.
Create the required RESTORE WITH NORECOVERY commands and execute them on the remote system.
July 28, 2009 at 8:39 pm
Declare @src varchar(100)
Declare @dest varchar(100)
declare @str1 varchar(200)
--Declare source and destination paths
set @src='c:\backup\AdventureWorks_log_'+convert(varchar,getdate(),112)+left(convert(varchar,getdate(),108),2)+substring(convert(varchar,getdate(),108),4,2)+'.trn'
set @dest='c:\Develop\AdventureWorks_log_'+convert(varchar,getdate(),112)+left(convert(varchar,getdate(),108),2)+substring(convert(varchar,getdate(),108),4,2)+'.trn'
--Backup the log
backup log AdventureWorks to disk=@src
--Declare the string to copy the log file
set @str1='copy '+@src + ' ' + @dest
--copy the log file
exec xp_cmdshell @str1
See if this code works for you. This takes the backup of the log on a local machine and then copies the log to a destination path immediately.
Two things you need to make sure:
1. xp_cmdshell should be enabled
2. The login ID used for sql server service should have appropriate rights on the target server.
July 29, 2009 at 5:26 am
Robocopy is what I would recommend. I use it all over the place.
Has sync folder options, auto restart if the copy fails, can resume a copy that fails without recopying the entire file, different overwrite conditions.
I setup something similar and has been working well
1. create agent job to run robocopy with a proxy with permissions to local and remote UNC path.
2. create maintenance plan to backup TL.
3. (In the maintenance plan) add "Execute SQL Server Agent Job Task" to run the robocopy agent job after the TL backup task -- this separates the copy from the TL backup, so your maintenance plan won't fail if your copy fails (the robocopy job would though) and the credentials can be changed to copy the file to network location
4. Add email notification to both maintenance job and robocopy sql agent job (if fails)
July 29, 2009 at 6:14 am
I suggest backing up directly to the location you plan on copying the files to. Is that not an option? You should be able to backup to a UNC path.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
July 29, 2009 at 6:37 am
Timothy Ford (7/29/2009)
I suggest backing up directly to the location you plan on copying the files to. Is that not an option? You should be able to backup to a UNC path.
There is high possibility of a network fluctuation which will break the log backup and hence the chain. So this is generally not advised. Take a backup on the local disk and then copy the backup to target destination.
July 29, 2009 at 2:15 pm
ps (7/29/2009)
Timothy Ford (7/29/2009)
I suggest backing up directly to the location you plan on copying the files to. Is that not an option? You should be able to backup to a UNC path.There is high possibility of a network fluctuation which will break the log backup and hence the chain. So this is generally not advised. Take a backup on the local disk and then copy the backup to target destination.
Not quite right, although the recommendation to backup locally and copy is correct. Backing up across the network you can experience network latency which will cause your backup to fail. A failed backkup will not break the log chain - it just means you don't have a good backup. Not having the backup is going to expose you to a larger risk of losing more data than you can afford to lose (which is why you backup the logs on a frequent basis).
If you have a good network setup - you can easily backup across the network reliably. We have a very reliable network and backup more than 200 databases ranging from 1MB up to 800GB across that network to shared storage. We did experience issues at one point, but we then upgraded the switch to a 10GB switch and have not seen any netowrk issues since.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2009 at 6:41 am
We have never experienced issues in backing up over the network. If you have a reliable infrastructure the issues and risk is minimal. If risks are minimal it would be preferred to reduce the amount of steps required to accomplish the goal. A further question is what are you doing with your disk-retained backups once they are created? Do you have a tape-based storage solution that regularly sweeps the disks and takes files to tape for longer-term, offsite storage?
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply