2008 backup compression

[abodineau]

Hi,

It looks like it's not possible to get a zip file of the backup through the API (2008). Because I need to download the backup file and because it is not compressed, it takes me about 2 hours to download it which is not really ideal.
When I do the backup through the control panel, I can selet to get a 'zip' file which only takes a few minutes to download from your ftp server.

Any solutions?

Thanks,

Anthony.

[bruce]

unfortunately.. this is not available through the API.

The API is a synchronized call and CP uses a workflow mechanism to create the backup asynchronously.

[CrystalCMS]

Hi Anthony,

A while back I implemented a simple web application that uses the API to perform a backup of a SQL2008 db. It was written as an app intended to run automatically by hooking it up to the DASP scheduler and I have had it running daily on my account now for the last 4 months without any issues whatsoever. You can read more about that here : http://community.discountasp.net/showthread.php?t=7523

I see you have already found and Bruce has confirmed that compressed backups are not available via the API, however because the API backup method runs as a synchronous web service method call, writing a little bit of code to compress the resulting .bak file as a zip file on disk will be quite trivial (for me at least).

If I get enough interest from other users (yourself included) I might consider writing the code to do it, but as it stands my own .bak db backup only stands at 9Mb so that's OK for me for now.

All the best,
Joe

[Chuck]

Joe,

I can tell you we get asked about that. You might want to start a thread and ask how many people would like to see something like that. I think you would be surprised at the response.

"Says the man that hopes the resulting impact of peer pressure will motivate you" :D

[CrystalCMS]

Hi Chuck,

I couldn't be bothered with testing the requirement in the community for this feature so I just went ahead and wrote the code. It's an extension to my old web application in this thread http://community.discountasp.net/showthread.php?t=7523 so the attached zip file in that thread is now deprecated and I'll a message in there pointing to this thread.

The new code is here http://www.jjssoftware.co.uk/dbbackup/JJS.DASPApi.SQLBackup.zip. It uses the zip functionality provided by the ICSharpCode.SharpZipLib library. All the required binaries and complete source code is included in the zip file..so if anyone wants to implement their own method for doing this they can rip my code or alternatively it is ready to use as is.

The setup is basically the same as it was before and when zipping is enabled in the web.config using <add key="zipBackup" value="true"/>, the result will be both the .bak file and a zip file (containing the .bak) created in the root _database folder. This zip functionality provides something like an 85% compression ratio which isn't bad at all.

Cheers,
Joe

[Chuck]

You sir are awesome! :D

[abodineau]

Hi Joe. Thanks for this but it only works if you call the backup api from a webpage. In my case, I call it from a console application which does the backup + the download in one go.
It's not possible to call the ICSharpCode.SharpZipLib library from a console app to zip a file which is on a ftp site.
What I'm going to do is write a webservice which is going to do the backup + zip and then call this webservice from the console app which will also do the download. This should do the trick.

[CrystalCMS]

Hi Anthony,

You're right pure client side code will not work in this scenario. I developed my solution to the compression problem as a server side proggie (ASP.NET) because I wanted a DASP scheduler method of automated backup and as you've found, the compression operation needs to be done with server side code to enable writing the resulting zip file on the server's filesystem prior to download.

Your proposed variation on this design using a webservice will also work well because this is also server side code. You're welcome to use the zip class in my sample code which is a wrapper around the ICSharpCode.SharpZipLib.Zip functionality if it helps.

All the best,
Joe

[abodineau]

Hi Joe,

I've started using your code to do the backup through a webpage but I keep getting a timeout error message (when calling Sql2008CreateBackup). Any ideas why? I understand that the backup might take a few minutes (which is how long it takes when I call the api from a console app) but I don't understand why the webpage times out after a certain amount of time.

Thanks,

Anthony.

[CrystalCMS]

Hi Anthony,

If you're using my code from the zip file linked in this thread above then I'm not sure. The code includes a timeout on the web service call and the default value in the web.config as I included it in the final zip file is a very large number:

<add key="webserviceTimeout" value="3600000"/>
DASPApiWebservice.Timeout = (int)Application["webserviceTimeout"];

The WebClientProtocol.Timeout value is specified in milliseconds so in this case 3600000 equates to 3600 seconds or 60 minutes.

I'm sure the web service call is not taking more than an hour to execute so are you sure you are seeing a timeout on the web service call?

The next thing to think about is a asp.net http timeout on the page. This is again handled in the web.config as I included it in the final zip file as a very large number:

<httpRuntime executionTimeout="3600"/>

It's working fine in my account so I suppose it's possible you are overriding the httpRuntime executionTimeout value in another web.config in your account because web.config values can cascade through web applications..

Cheers,
Joe

[abodineau]

Thanks Joe. Yes the problem was in fact the asp.net http timeout. I had forgotten to change it to 3600 in the config file but I'm a bit worried to change its default value: if I do change it to 3600 (like in your example) then ALL the http requests in my web application could potentially run for 1 hour. I'm not sure I want to do that.

I don't think it's possible to change this timeout programmatically to 3600 just before doing the backup and reset it to its default value once the backup is done. The only option I can think of (if I don't want to change the http timeout of the whole application) would be to put the 'backup' webpage in its own subfolder which would have its own config.file. That would probably work.

Thanks for your help.

Anthony

[CrystalCMS]

Hi Anthony,

Yes you're right, the massive <httpRuntime executionTimeout="3600"/> will not be suitable for most web applications but it is required for this one because of the length of time it takes for the Sql2008CreateBackup API method call to return within the context of the HTTP GET.

Step (7) of the the readme.txt I included with the original zip file did mention: Deploy the application as part of your main app in the root of your DASP account or alternatively in a separate sub folder off the root.

I suppose with the benefit of hindsight, the best way in most cases for this application to be setup and to work as intended is to install it as a sub-application off the root - this is the way I have it setup in my own DASP account.

Feel free to improve on the code if you want to. At one point I was thinking that if the resulting zipped backup was a small enough file (based on some limit specified in the web.config), then it could be:

Attached to an email.
Automatically sent to some recipient(s).
Deleted from the web server when done.

I suppose another improvement would be to able to configure the API method call so that the application could also support SQL Server 2005 via the API. Don't worry if you don't have the time though ;-)

Cheers,
Joe

[abodineau]

Hi Joe,

I finally got it working! but.... I noticed any http requests done to the webserver (while the zipping is being done) time out. In my case, the zipping takes about 4 minutes (the backup file being over 300MB) which means that it's impossible to use the website during that time which is far from ideal. I imagine it's because the zipping takes all the ressources from the asp.net process which therefore cannot process any new http requests.
I should have probably checked that first but if the website is not accessible while the zipping is being done then I can't really do the zipping. I don't think there is much I can do about this unfortunately. The choice I have is either make the website inaccessible while the zipping is being done or not do the zipping but in this case it will take a few hours to download the backup file from the ftp server.

[CrystalCMS]

Hi again,

Zipping up a 300Mb file will put a load on the webserver for sure but there maybe something we can do about this problem - don't give up yet! As it stands, I'm not sure if this operation would cause problems for other users in the shared hosting environment. It will depend on how DiscountASP.NET have got their web servers configured or virtualized and also how the accounts are separated from each other in the environment.

Take a look at the Create method in the Zip class I developed. You'll see this code about halfway down:
do
{
sourceBytes = fs.Read(buffer, 0, buffer.Length);
s.Write(buffer, 0, sourceBytes);
} while (sourceBytes > 0);
This is almost certainly where all the work is being done zipping up your massive file. One thing I suggest you try is putting a Thread.Sleep(n) into the while loop (where n = some value in milliseconds). This will obviously extend the length of time it takes to complete the entire HTTP GET so you don't want the to put the loop to sleep for too long because you'll overrun the httpRuntime executionTimeout, but you do want it to be long enough to allow the web server to serve other threads whilst zipping the file.

If that doesn't work, then run this process on a day and at a time when you know your site is quiet - this will at least minimize the disruption and impact to your own service.

Let me know how you get on.