Transaction log backup script

Question

Transaction log backup script

sqlstar2011

SSCertifiable

Points: 6000
More actions
June 30, 2006 at 12:40 pm

#118533

Just wondering if someone can share their wisdom on this. Need a script to backup transaction log every 15 minutes to a remote location on the network.
Maintainence plain trans log backup doesnt allow writing to a remote location on the network.

Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

What I do is write to the local machine and then in the next step of the SQL Agent Job I copy it out to my network location. I've found it's always nice to have soem of the most recent backups on the local machine that way they are handy for restoration purposes.

To help us help you read this[/url]For better help with performance problems please read this[/url]

James Luetkehoelter SSCommitted Points: 1733 More actions · Answer 2

That's also the safer way to do it. There are ways you can backup to a network share, but avoid the urge to do it directly. That will lengthen the transaction log process, and the longer it takes any single process to run, your odds of having some sort of failure increase - what if the network had some sort of hiccup right in the middle of your backup? The backup might not abort, but it could corrupt the file. If you follow the previous methodology, at least you'd still have the local one.

You may want to consider copying to a number of separate locations. In a production environment the transaction log is your lifeblood -- you can't have enough copies of a backup in my opinion.

(Incidentily, SQL 2005 supports mirroring a backup file to up to 4 locations)

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 3

Luke

How are you copying files using the agent? a cmd exec script to basically copy files?

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

since I apparently took too long posting my original answer and the page timed out you're goign to get a short sweet and to the point one. I compact my backups with winzip before I copy them accross the network. This can lead to corruption, although I've never expirirenced it. I restore a backup twice a week just to make sure to my test/dev environment. Below is an example .cmd file that you can use. This can be put in the next step of your backup plan, or be called via a dts job, windows scheduler or whatever. Use what suits your environment the best.

c:\"program files"\winzip\wzzip c:\Mybackupzip.zip (destination) d:\mydata.dmp (source)

REM MAP my network drive

NET USE z: \\otherserver\share

xcopy c:\Mybackupzip.zip(source) z:\mybackups(destintation) /E /Y

REM destroy network drive

NET USE z: /delete

To help us help you read this[/url]For better help with performance problems please read this[/url]

Sakthi-346937 Grasshopper Points: 18 More actions · Answer 5

HI

You can create a job in the server to backup the log files and store in some local drive which runs every 15 mins.

Then write a windows script that transfers the backup file from local system to the remote system.

Please update me if am right or wrong.

Thanks,

-Sakthi

Sakthi-346937 Grasshopper Points: 18 More actions · Answer 6

HI

You can create a job in the server to backup the log files and store in some local drive which runs every 15 mins.

Then write a windows script that transfers the backup file from local system to the remote system.

Please update me if am right or wrong.

Thanks,

-Sakthi