How to use MonTel to backup an SQL Server database

The SQL Server (or MSDE) will typically be running 24 hours a day, and so most back up systems will have problems accessing the open data files. Probably, the best way around this, is to exclude MSDE and the data files from the back up, and use the one of the methods specified below to regularly run the BACKUP command to move or create a backup copy of the database on a network drive that is regularly backed up.

Changing Service Login

In both the scenarios below, the service running MSDE or SQLServer will by default NOT have permission to use the network. So to enable a backup to be created on a network drive you should change the account used by the service to a user who has the correct network access. Using the Administrator account would not be a good security move, instead, use an account with just the necessary network folder access.

You can change the services by starting Services control panel applet (it is usually in Administration tools).

Right Mouse Click on MSSQLService to change the properties.

You will need to stop and start the service again to the account login to change.

As an alternative you may wish to backup to a local drive, and have a later batch file copy that file to a network drive.

SQL Backup from MonTel

You can create a backup each time you run either Monex or Monimp32 (2.9.53 onwards) by specifying AutoImportSQLBackUpMasterClientDB or AutoExportSQLBackUpMasterClientDB depending of which utility you want to use and optionally SQLBackupPath for the location.

If SQLBackupPath is not specified, Monex or MONIMP will attempt to create an SQLBu folder in the Master Client Database location on the network. Note that the SQLBackupPath is the path as seen by the SQL Server and NOT by monex or MONIMP, so it is possible that they are not pointing to the same location.

Please note that this will fail UNLESS you change the MSSQLServer to login as a user who has the relevant network access. The Local System account (the default) does not allow the Service to use the network.

SQL Backup from batch file (as part of overnight processing)

Alternatively, you can use OSQL. OSQL is a command line tool supplied with both MSDE and SQL Server. In your batch file you will have run OSQL and attached to the SQL Server replacing {server} (including the backets) with the name of the machine login on as the 'sa' user with the password replacing {sapassword} below which will run a script called backup.sql. You will need to then run a script containing the backup commands. You will also need to make the path point to the correct script. (The following is on one line)

C:\PUBLIC\osql.exe -S{server} -Usa -P{sapassword} -dmaster -iF:\apps\MonTel\support\msde\backup.sql -r1 -m-1

An example of this where the DatabaseName = "MONTELDB" might be (as an OSQL script):

USE master

GO

BACKUP DATABASE MONTELDB

TO DISK = 'I:\MonTel\ClientDB\SQLBU\monteldb.bak'

WITH FORMAT,

INIT,

NAME = 'Full Backup of MONTELDB';

GO

Please note that this will fail UNLESS you change the MSSQLServer to login as a user who has the relevant network access, or use a path on the local MSDE or SQL Server computer.

Also note that the next MonTel install will over write the default backup.sql script, so this should be moved to a safe location.

SQL Backup as an SQL Job.

This requires running the SQLServerAgent and detailed knowledge of MS SQL Server systems and is therefore beyond the scope of this topic to explain in detail.

As a starting note an untested example of adding a job might be (as an OSQL script)::

USE master

EXEC sp_add_job @job_name = 'NightlyBackup'

USE master

EXEC sp_add_jobstep @job_name = 'NightlyBackup',
@step_name = 'Add db dump device',
@subsystem = 'TSQL',
@command = 'sp_addumpdevice ''disk'', ''MonTel_dat_Bu'', ''i:\data\MonTel\clientdb\MonTelbu.dat' '',

USE master

EXEC sp_add_jobstep @job_name = 'NightlyBackup',
@step_name = 'BACKUP DATABASE',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE MONTELDB_dat TO MonTel_dat_Bu',

USE master

EXEC sp_add_jobstep @job_name = 'NightlyBackup',
@step_name = 'Add log dump device',
@subsystem = 'TSQL',
@command = 'sp_addumpdevice ''disk'', ''MonTel_dat_Bu'', ''i:\data\MonTel\clientdb\MonTelbu.dat' '',

USE master

EXEC sp_add_jobstep @job_name = 'NightlyBackup',
@step_name = 'BACKUP LOG',
@subsystem = 'TSQL',
@command = 'BACKUP LOG MONTELDB_log TO MonTel_log_Bu',

USE master

EXEC sp_add_jobschedule @job_name = 'NightlyBackup',

@name = 'ScheduledBackup',
@freq_type = 4,
@freq_interval = 1,
@active_start_time = '23:00:00'