I note that for local SQL Express and I believe enterprise SQL Server there is the additional option of creating a Backup in SQL Express. I also note that from my reading Baks are considered preferable to bacpac files because they have enforced ACID compliance and that for large databases that are constantly being used they are recommended to bacpac files. SQL Azure doesn’t allow BAK file backups through SSMS from what I can see so if this is an issue for you consider temporarily disconnecting front ends from the database while a bacpac is conducted. If you need a bak file for some reason you can attach locally to a SQL Server instance and from there take a bak file.
Something to be aware of..
See this link for further information
Blobeater blog post
Connect to SQL Azure through SQL Server Management Studio
Select the database that contains the table you wish to script
Right click on the database and select Generate Scripts
The Generate Scripts dialog should appear choose
Select specific database objects
Hit Next and set the location of where you wish the file to go
Review the summary dialog to ensure you have everything correct
Next you want to decide whether you want to include data or not in the script file
Hit the advanced button and scroll down to Types of data to script
If you are wanting data to be included select schema and data
Hit the Next button and review your configuration
Hit Next and it will start the process of exporting your table with or without data
The dialog will update to show status of script generation
You will now be able to navigate to the script and open it in an editor of your choice to double check that things look ok.
Here I show a script that includes data – note you can’t see all of the insert information because the script is rather wide but I assure you it is there at the bottom.
I was contemplating a better way of storing our old emails and unhappy with some of the systems in place I started considering whether I could dump them into a database.
Thing is when you export from Outlook some of the standard fields in particular To and From are concatenated. Wouldn’t it be nice to separate those fields into their own table of names and addresses and reference them back to a main table of messages.
This is what I came up with.
For demonstrations purposes I will use two tables
pkid - autonumber primary key
ccaddresses - memo or long text
and the child table
pkid - autonumber primary key
ccaddress - string(150 should do it)
pkidt001 - number
and here is the blank second table
Next we create a user defined function
Public Function CreateChildTable()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim strSQL As String
Dim strField1 As String
Dim strField2 As String
Dim varData As Variant
Dim i As Integer
Dim intPKID As Integer
Set db = CurrentDb
'Select all the ccaddresses from the parent table
strSQL = "SELECT pkid,ccaddresses FROM t001parent"
Set rsTarget = db.OpenRecordset("t002newchildren", dbOpenDynaset, dbAppendOnly)
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
While Not .EOF
intPKID = !pkid
varData = Split(!ccaddresses, ";") ' Get all semi colon delimited fields
'add email addresses if there is only one email address there
!ccaddresss = Trim(varData(0)) ' remove spaces before writing new fields
!pkidt001 = intPKID
'loop through addtional email addresses and add them as children to table
For i = 1 To UBound(varData)
!ccaddresss = Trim(varData(i)) ' remove spaces before writing new fields
!pkidt001 = intPKID
Set rsTarget = Nothing
Set rs = Nothing
Set db = Nothing
After running this we should
We’ve just nicely split the parent table ccaddesses field into constituent emails and normalised it as a child of the parent table.
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
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.
Make sure Task Schedule Library is selected in the tree on the left then within Actions sub window on the right click Create
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.
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.
If we continue with the theme and run a vbs script to do a backup at 11:48 everyday
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.
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.
Best place to search for content particularly on mobile
- Recent Posts
- Archive by Month Year