February 25, 2012 at 9:52 am
i have 2 servers set up with nightly backup and restores going across
using SQL server agent, all zip and unzip is with winzip command line
tool.
server 1 has production database a and report database b, server 2 has
production database b and report database a.
I have a job on server 1 to backup, zip and copy database a to server 2. a job is then scheduled on server 2 to unzip and restore to the report database a. The job on server 2 fails when scheduled but can be manually run (SSA Run Job) and every thing works fine.
what makes this more frustrating is database b works in the opposite direction. server 2 backs up and zips database b. job on server 1 copy's unzips and restores to report database b
im using the following on both servers to unzip
DECLARE @CMD Varchar(2000)
DECLARE @Call Varchar(100)
DECLARE @Zip Varchar(100)
DECLARE @File Varchar(100)
SET @Call = '"path\to\wzunzip" -o'
SET @Zip = ' path\to\backup.Zip '
SET @File = 'path\to\backupfolder'
SET @CMD = @Call + @Zip + @File
EXEC master..xp_cmdshell @CMD
GO
to zip i use similar code changing it to wzzip and the appropriate VARS. it was set up this way to have server 1 control all copying of zip files.
on monday im going to try and switch the jobs so server 2 copies from server 1 but wanted to throw out to the forums if there might be any other way.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
February 27, 2012 at 1:07 am
capn.hector (2/25/2012)
I have a job on server 1 to backup, zip and copy database a to server 2. a job is then scheduled on server 2 to unzip and restore to the report database a. The job on server 2 fails when scheduled but can be manually run (SSA Run Job) and every thing works fine.
This usually means that the account which owns the scheduled job doesn't have the right permissions. Assuming you're running the job under the "sa" account, check if the SQL Agent service account has read\write access to the folder which contains the zip file and execute permission on the folder where WinZip is located.
Also check the Eventlog for any error messages which may point you in the right direction.
[font="Verdana"]Markus Bohse[/font]
March 1, 2012 at 2:02 pm
after trying to figure out what was going on i stayed up and watched the zip files be created at 3 am. and after a face palm realizing the files had not finished copying from server to server when the restore task ran i moved the time and every thing works just fine. now i feel kinda stupid posting this here but last friday was at the end of my patience.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply