WARNING: Some of the following instructions may not be best practice. This is a personal database so I can afford to take some shortcuts and not do thorough research. If you do use these tips and sample code in production check back in a week and a month to see if there are any changes or comments.
Objective
I wanted to create a method of backing up a personal SQL Server database from within Access. In a production environment you’d have tasks which ran on a regular basis, i.e. at midnight or whatever, that would do this. But here I might update some records every few days and wanted to do this on my schedule.
Note that, as the names of the stored procedures indicate, I will be modifying these as I add more functionality such as database checks, log truncation, etc.
General SQL Server setup tips
I chose to create a new folder on the C drive of my SQL Server OS called SQL Server Files to store the files. No good reason to do so because the master and other SQL Server databases are stored elsewhere but I figured what the heck. From within SQL Server Management Studio Object Explorer right click on the Server node which is the top most node. Then choose Properties and the Database Settings tab. Then change the Database Default Locations as desired.
Just for grins and giggles I decided to change the port number that SQL Server uses. I used the SQL Server Configuration Manager to update the TCP/IP port. Note that this is not a good means of securing SQL Server. That is you have to go through through all the other security measures too.
Backup Stored Procedure (SP)
As I was unable to execute the SQL Server backup code directly within Access VBA I created the following stored procedure (SP) which I placed in a Utilities database in SQL Server.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BackupAndMaintenance]
@DatabaseName nvarchar(255),
@FileName nvarchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BACKUP DATABASE @DatabaseName
TO DISK = @fileName
WITH NAME = 'Full Backup';
END
To copy and paste the above into SQL Server Management Studio 2012 connect to the database and in the Object Explorer expand your master database items >> Programmability >> Stored Procedures and click on New Stored Procedure. Then change the above code by changing ALTER to CREATE. Click the red ! Execute icon to execute the T-SQL code which will create the stored procedure (SP). Close the SP window. Hit F5 to refresh the list of Stored Procedures on the Object Explorer. Then open the SP by right clicking on the SP and click on Modify. You will now see the CREATE has been changed to ALTER.
I chose to create these SPs in the master database as they will be used to backup other databases
Note that when you close SPs you will get a warning prompt very similar to the following:
Save changes to the following items?
SQLQuery10.sql.
You can ignore all such messages for SPs only because, by executing the SP wrapper code, you are actually altering the SP in the SQL Server database. Do *NOT* ignore these warning messages for other objects such as Tables or Views. I can remember to ignore these because they have non meaningful name.
I named this SP BackupAndMaintenance but I might change that later as I might want to put the maintenance functions in their own SP run by another account. I’m going to think about that one a bit.
Creating the backup login/account
Expand the Security item in the Object Explorer and create a new Login called whatever you want but I chose backup. I used SQL Server authentication and used KeePass to store the userid and generate and store the password. (I’ve been using KeePass for a number of years to store my userids and generate random passwords for all my accounts. For most websites it will, if desired, automatically fill in the user name and account for you when you hit Ctrl+Alt+A. )
Then you need to add the backup login as having backup privileges to each of the databases. Ignore the logical sounding Server Roles page on the Login Properties page. Instead go to the User Mapping page, click on each of the databases you wish to backup, including master, model and msdb and click on db_backupoperator.
There was a note somewhere about needing additional privileges if you want to verify the backup but I’ll leave that for another day.
And now you have an account which can only do backup and nothing else.
Granting the backup login/account permissions to the backup stored procedures.
At this point the backup login/account can’t actually execute the stored procedures. It can only do backups. So we need to explicitly add the backup account to the SPs with execute privileges.
Right click on the backup SPs, click on Properties and select the Permissions tab on the left hand side. Click on the Search… command button and enter backup in the Object Names box. Click on Ok to return to the SP Permissions screen and click the Grant Execute check box.
If you get the following message
-2147467259 - Microsoft OLE DB Provider for SQL Server
Cannot open database "Utilities" requested by the login. The login failed.
then you forgot this step. Ask me how I know.
Even worse maybe you missed an ‘i’ or a ‘t’ in the Utilities database name when you created it in SQL Server Management Studio but you spelled it properly in the VBA code because the VBA code window has a larger font. Rather frustrating figuring that one out. Time for a visit to the optometrist.
The key here to figuring out this last problem was to use the SA userid and password to run the backup SP. When that failed I was pretty sure the problem was not in my permissions but elsewhere.
VBA code within Access
Within a new Access database file I created some ADODB code to execute the SP.
Global Const ConnectString As String = "Provider=SQLOLEDB;data Source=<system name>\<instance name>,<optional port number>;" & _
"Initial Catalog=<database name>;" & _
"Encrypt=Yes;DataTypeCompatibility=80;"
Global Const BackupUserIDPwd As String = "User ID=backup;Password=<I’m not that stupid. Usually.>"
Global Const strSQLServerLocalBackupPath As String = "X:\SQL Server Backup Files\"
Sub RunBackupSP(BackupPathAndFileName As String)
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strPathAndFileName As String, strDateTime As String
On Error GoTo tagError
con.Open ConnectString & BackupUserIDPwd
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "BackupAndMaintenance"
cmd.Parameters.Append cmd.CreateParameter("@DatabaseName", adVarChar, adParamInput, 255, gblDatabaseName)
cmd.Parameters.Append cmd.CreateParameter("@FileName", adVarChar, adParamInput, 255, BackupPathAndFileName)
Set rs = cmd.Execute()
Exit Sub
tagError:
Dim errLoop As ADODB.Error, i As Long, strTmp As String
Dim strError As String, errErrors As ADODB.Errors, vbErrorNbr As Long
i = 1
' Process
vbErrorNbr = Err.Number
strTmp = strTmp & Str(Err.Number) & " - " & Err.Source
strTmp = strTmp & vbCrLf & " " & Err.Description
' Enumerate Errors collection and display properties of
' each Error object.
Set errErrors = con.Errors
For Each errLoop In errErrors
With errLoop
' don't display duplicate error
If vbErrorNbr <> .Number Then
strTmp = strTmp & vbCrLf & "Error #" & i & ":"
strTmp = strTmp & vbCrLf & " ADO Error #" & .Number
strTmp = strTmp & vbCrLf & " Description " & .Description
strTmp = strTmp & vbCrLf & " Source " & .Source
i = i + 1
End If
End With
Next
MsgBox strTmp
End Sub
Notes:
- I used the Microsoft ActiveX Data Objects 2.8 library as the ADO reference as that is the most recent ADO library which will work on Windows XP as per my blog article I've never quite trusted ADO because …
- The above error handling code will display all the nested ADO/ODBC errors. Which is also why I put the SP in it’s own subroutine so the error handling routine wouldn’t be so ugly elsewhere.
- The path that you use must be a hard drive local to the SQL Server OS and can’t be a network path as the Windows account which is used to run the SQL Server service does not have any network privileges.
- When you run the backup yourself using SQL Server Management Studio you will only see the local hard drives, or tape devices, available on that system and not your hard drives. It’s a bit confusing at first but makes some sense once you think about it and reread the previous note.
- I chose to keep the code which creates the backup path and file name along with the embedded date time in Access VBA code as I figured I might as well keep all such logic in one place rather than having some in VBA and some in the SP.
Further refinements
I chose to update a single record table in the database with the backup progress so should I restore the database elsewhere I have an internal record of the backup date and time. There was a different datetime field for each of the three phases of the backup. Hmm, now that I look at that the fields aren’t named all that well. Oh well, I’ll change them tomorrow.
I created a status textbox on my main backup program menu so I could see it working.
From my standard development system I used some VBA code to copy the .bak file created to my local hard drive for daily backup purposes and also copy the file to my NAS (Network Attached Storage) device. Now the path visualizing of the file gets a bit strange because the backup SP which runs on the SQL Server system only knows about local hard drives. But then the VBA code running on my development system now needs to see that networked hard drive backup file from the perspective of the machine executing the file copy VBA code.
I then run an API ShellExecute against the above three network shares and paths. Again from the perspective of my development system and not the perspective of my SQL Server OS. I only want to keep the latest copy of the backup file on my local hard drive which is the Q partition you see above. So by running Windows Explorer I can easily see any old backup files and delete them as required. I also only want to keep some of the backups on my NAS but not necessarily all of them.
I then created one SP that did a backup for the Master, Model and msdb databases again with date and time as part of the backup file name. Among other things this will backup the backup login and it’s permissions I just created.
Maybe I’ll create a folder named by date and time and place all these backup files within that folder but that’s for another day.