Today we will consider examples of MS SQL database backup automation using APBackup.

When performing a task in APBackup, it is possible to run external applications before and after archiving. In addition, you can configure the task not to perform archiving at all, but to run only external programs (i.e. get a regular start of the application according to the schedule). This can be done by setting the option “Only start external applications without archiving”.

Let’s consider for example the task with archiving.

We need to configure the task to create a standard backup of one MS SQL database in some tempo directory before archiving and then this file should be archived and placed in our backup storage.

To do this, let’s create a directory for temporary files, for example, C:\SQLTMPBackup, it will serve as a SOURCE for our task. Besides, after archiving we will delete temporary backups. To do this, set the “Delete source files after archiving” option

Saving the archive is set up in a standard way using the date and depth format, for example. You can also set up additional directories for secure backups, for example, on another server over the network and/or on the “Save to FTP” tab, and set up backups to a remote FTP server as well.

It is also possible to use an external archiver or simply copy the source file without archiving, as well as copying directly to the FTP without archiving. These are the standard features of APBackup and we won’t dwell on them here.

Now let’s examine in detail how to implement MS SQL database backup with the help of an external application. Here we will be helped by the utility included in the standard installation of MS SQL – OSQL.EXE

For example, creating a backup of the msdb database will look like this:

osql.exe -S<Server Name> -U<Username> -P<Password> -Q “BACKUP DATABASE msdb TO DISK = ‘c:\msdb_data.bak’ with skip,init

I.e. here is where the -Q switch is followed by the Transact-SQL command to be executed. In this case, it is a BACKUP DATABASE as useful to execute the BACKUP LOG command for example to truncate a log file.

ATTENTION: If the server is not located on your machine, you will not find c:\msdb_data.bak on your computer. Since this is the path where the server will store the data, i.e. this file will be located on the server on the C:\ drive. In principle, APBackup can also retrieve the file from the server if you create a shared folder on the server, or MS SQL server can save the backup file to another server, but for this purpose, MS SQL service must be loaded with the domain account, not localSystem (as default).

If you physically run OSQL where the server is installed, then -S may not be included in the command line, by default OSQL connects to the local server.

If Windows Trusted Authentication is used on the server and your domain name has MSSQL backup rights, you can use the following call:

osql.exe -S<Server Name> -E -Q “BACKUP DATABASE msdb TO DISK = ‘c:\msdb_data.bak’ with skip,init

Or without a server if it is a local server:

osql.exe -E -Q “BACKUP DATABASE msdb TO DISK = ‘c:\msdb_data.bak’ with skip,init

The -E switch means using the Trusted connection i.e. under your domain name instead of explicitly specifying: -U<Username> -P<Password>

If you need to make a backup of several databases at once, just create a script file (on your machine), for example, C:\mybackup.sql:

BACKUP DATABASE msdb TO DISK = ‘c:\msdb_data.bak’ with skip,init;

BACKUP DATABASE master TO DISK = ‘c:\master_data.bak’ with skip,init;

go

and we’re doing the team:

osql.exe -S< Server name > -E -i “C:\mybackup.sql

Or

osql.exe -S< Server name > -U< User name > -P< Password > -i “C:\mybackup.sql

For example… (chuckles)

== sync, corrected by elderman ==

(Don’t make such passwords for sa )

Thus, we will be able to do a single task in APBackup to make a backup of all databases on the MS SQL server at once. Let’s get, for example, 2 files with backups in the temp directory, one file – one database.

Here is an example of how to set up the MS SQL database archive creation in APBackup

It is also possible to adjust the parameters here:

“Run in window” if it is HIDE, the console window in which osql.exe will not be visible. With SHOWNORMAL, you will see a console window at startup.

“Wait for no more than 0 – If 0 then APBackup will wait endlessly for the application to run. For example, you can set it to 60 minutes. If the application does not close within 60 minutes, APBackup will stop waiting and continue the task. And if no files are found in the source directory, the task execution error will occur.

P.S. In the documentation for MS SQL 2008 it is recommended to use the new utility sqlcmd instead of osql as osql will be excluded from future versions of MS SQL. The new utility has similar keys and allows you to make a backup copy of the database in the same way.