SQL Azure – Take Complete Backup of Azure Database (Structure and Data)

SQL Azure as part of the service offers a number of differing back up options as standard – however you may wish to take additional backups which for instance you can load onto a local version of SQL Server (Express or Enterprise). Here’s a quick rundown of one method of doing this.

Firstly open up your version of SQL Server Management Studio and navigate to the instance and database that you wish to backup

Highlight the database in this case DB001 and right click -Select Tasks and Export Data-Tier Application

Choose an appropriate location to put the backup file

Review the summary of items that will be backed up and then hit Finish

There will be a run down of how the export has worked

And just a check to see the exported file in the directory

For more information on SQL Azure see here
Mikhail Shilkov Azure and programming blog

Using Task Scheduler and Visual Basic Scripts to automate pretty much anything Win 7

This is hardly ground breaking but windows Task Scheduler can be used to run VBS scripts on a variety of events to automate repetitive and boring tasks. Backups for example or refreshing caches on web browsers before users come in.

Hit Window button

Type “Task Scheduler”

Press return you should get the following screen.

Schedule1

Make sure Task Schedule Library is selected in the tree on the left then within Actions sub window on the right click Create

Schedule2

Give the Task a name in the area above marked with a red arrow and then go to the actions Tab where you can use the New… button to navigate to the vbs file you wish to run.

Schedule3

Next use the browse button OR if you know the path and file name simply type in the path to your visual basic script you would like to run. Here I have put in an imaginary backup script. Next go to the Trigger tab and again hit the New… tab

The Begin Task selection list gives a good indication of when you want to trigger whatever tasks.

Schedule4

If we continue with the theme and run a vbs script to do a backup at 11:48 everyday

Schedule5

Rolling VBS save script

Want to backup files and don’t have a fancy Database Administrator or sysadmin on 40k that knows his way around SQL Server 08R2 and MS Access , windows server and fully complies with continuous backup?

This might prove useful.

With any of the standard bespoke internally created applications that I am responsible for I like to have three main copies or parts.

1.a Back End
2.a Development copy front end – (this is used to adjust and create runtimes )
3.a Front end run time

Of the above two I backup both the back-end and the development copy of the front end. I do not back up run times (No.3), personally if I have the others backed up I can re-create a run-time from whatever point I desire. If for some reason I can’t create a run-time from a development copy it is time to roll back on the development copy – this should exist because of this very post.

In terms of the two types of copies I consider both equally important. Back-ends are easy to create but very difficult to re-create increasing in value with time. Front ends have no value in the data but take an extra ordinary amount of time to figure out and likewise are very awkward to recreate increasing in value with time. Both are digital and can be copied multiple times often with little to no cost (size dependent for back ends). Many front ends are actually tiny but their value comes from the way in which things flow and the speed with which they execute.

So I backup both to date I’ve had some brushes with almost disaster but never actually lost an application. Yes there have been hiccups mainly due to my own error – getting confused with versioning and deleting the most up to date version rather than yesterdays version. Forgetting a password on an encrypted USB and having it wipe itself requiring recovery from backup – that kind of thing annoying but not really a problem. I now have a daily backup routine that at least means that I cannot really loose more than a days work. I use it all the time.

Option Explicit
Dim FSO
Dim vardatefile
Dim varmonthfile
Dim BDayFilePath
Dim BMonthFilePath
Dim Varnow

Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
vardatefile = "YourDatabaseBackEndCopy-Weekday-" & day(varnow) & ".accdb"
varmonthfile = "YourDatabaseEndCopy-Month-" & Month(varnow) & ".accdb"
BDayFilePath = "C:\" & vardatefile
BMonthFilePath = "C:\" & varmonthfile

FSO.CopyFile "C:\DatabaseTarget.accdb", BDayFilePath, "True"
FSO.CopyFile "C:\DatabaseTarget.accdb", BMonthFilePath, "True"
Set FSO = nothing

msgbox "Backup Complete" ,0, "Backup Script"

Save in simple word editor and change to vbs suffix , double click to run.
Note I have made this Option Explicit which is good practice although it is entirely possible to remove Option explicit and dynamically set the variables.