Backup log file issue

[jemckinney]

I have been tasked to find the best way to backup/move our log files. I have been using the database maintenance to setup the backup for my database and log files. In the log file backup job, i want to setup a second step to move those files to a different server. I've been able to move them to a different folder on the same server using xcopy and setting it as a cmdexec type, but that does not obviously work for a different server. We cannot do log file shipping because it is not a standby sql server for just one database. I have a .bat file that currently moves them on a schedule task, but they want the second step in sql db maintenance task. Any ideas or scripts I can use as step 2? I would be soooo greatful!

 

[SinThetik]

Howdy,

No problem!! Why not use xp_cmdshell?

You can use this inbuilt command extension to perform batch type operations in your scripts;

Backup Log MYDB to disk = 'D:\Backup\MYDB.log'

exec xp_cmdshell 'Net Use z: \\Server\Share'
exec xp_cmdshell 'Copy d:\Backup\MYDB.log z:\'
exec xp_cmdshell 'net use z: /d'

Turn this into a job and schedule away.
You can always make this into an encrypted procedure to hide the net use details (eg. if you need to pass credentials etc).

Best bet is a "safe" network share that you're not worried if other people connect - as procedures "can" be decrypted.

Cheers!

 

[jemckinney]

i like the idea and tried it but it appears top be hanging up on this step. Any ideas?

 

[JohnBates]

jimckinney,

If you use xp_cmdshell, it requires strong permissions... that may be why it hangs or fails.

Unfortunately, you will have to run it as 'sa' or create a special proxy account and let it run under that.

John

 

[jemckinney]

Thanks for your help! I was able to get this to execute exactly how I wanted:

exec master..xp_cmdshell 'd:\mmsql\backup\*.* to \\server\share /y'

Works like a charm. TSM!!!!!!