2008 backup compression

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.

 

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

 

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"

 

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

 

You sir are awesome!

 

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.

 

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

 

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.

 

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

 

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

 

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

 

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.

 

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:

Code:

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.

 

The sample code has been relocated here: http://www.jjssoftware.co.uk/wp-content/uploads/2012/02/JJS.DASPApi.SQLBackup.zip

 

Hi,

Just finished implementing the backup and zip web app.

Implemented it as a separate web app in a sub folder as suggested.

Had a couple of problems with various things being inherited from the main web.config file. Chiefly some 3rd party Dev Express dlls.

Tried the following link to no avail ....

http://www.aspdotnetfaq.com/Faq/how...ld-applications-in-subfolders-in-asp-net.aspx

In the end I had to add the dlls to this web app, even though they are not used!!

Whilst the backup was being zipped my website seemed fine and all pages loaded as normal.

Question
One question to stop the trace information being printed to the webpage do I just set Trace="false" on the webpage?

Many thanks for making this available, I learnt a lot today!

Thanks,

Will

 

Yes the sample app switches on tracing for it's one and only aspx page in the @Page declaration in the markup of default.aspx. It's just a debug switch and it can safely be removed from the @Page declaration. It was a useful option to have switched on during original development and testing the app on the DASP server because it allowed me to easily see how long a request to the page would take to backup and zip a database.

There's a bit more info about the 'Trace' page declaration switch here (msdn) including links to further detailed info about asp.net tracing if you were interested in that.

 

Running the backup and the zip together

Hi,

I tried running the backup and the zip together and it timed out on the zipping part.

The backup took a minute or two and then the zipping timed out after an hour.

However when I commented out the backup process (and the deleting of the previous backup file) and ran the code it zipped in 2 minutes?

If this is the case then surely the backup and zipping should work.

I have not changed any of the timeout values.

They are still <add key="webserviceTimeout" value="3600000"/> and <httpRuntime executionTimeout="3600"/>

Any ideas?

Thanks,

Will

 

The symptoms you've described sound suspiciously like there was a file lock on the database backup .bak file in the file system when the app started trying to zip the file. That's really all I can think of based on what you've described and this is the case then I don't know why that's happened. I can advise that I had a variant of this program running on the DASP web server backing up my DASP SQL2008 db on a daily basis pretty much flawlessly for 4 years.

The ball's in your court to debug this for your own use case.

 

Does anyone have a copy of the source for scheduling a nightly MS SQL Server backup from my DASP site? Joe's site: http://www.jjssoftware.co.uk/ seems to have gone offline. I just want to run a nightly backup of my small SQL Server DB hosted on DASP and have it dropped into the _database folder. I've enabled the Control Panel API. and want to hit the DASP Scheduler once a day at 2 AM Eastern. I've done a little C# and Dot.Net programming, but most of my web development is in Classic ASP, but it does not look like I can support the calls to the CP API from this format. Thanks!

 

Hi Martino,

Unfortunately, this solution seems to be an installed app on a local server that would need to be online 24x7 or at least at 2 AM .

What I'm looking for is a way to schedule the backup to run at the same time each morning. It looks like the schedule utility in Control Panel can call a web page on my site, so I need a way to call the API from a web page.

 

Hi Peter the original source code for the schedulable database backup web app is still available here: http://community.discountasp.net/threads/2008-backup-compression.7780/#post-51888

Feel free to do whatever you want with it. If you eventually go global and float an IPO with this, I trust you to remember where you got it

 

Thanks Joe! I'll check it out