Backing up MS SQL databases using APBackup

Today, we will take a look at some examples of MS SQL database backup automation using APBackup.

When a task is started in APBackup , you can launch external applications before and after the actual backup process. Besides, you can configure a task not to back up data at all, but to launch these programs only (i.e. schedule a program start). This can be done by enabling the “Execute only “To Do before/after backup” without backup” option.

Let’s take a look at an example of a backup task.

We need to configure the program to create a standard backup of an MS SQL database in a temporary folder prior to the creation of the main backup file. We then need to compress this database file and move it to our backup folder.

To do that, let’s create a temporary folder – for instance, C:\SQLTMPBackup. It will serve as a SOURCE for our task. Besides, we will delete temporary backup files after backing up our data. To do that, let’s enable the “Delete files after archiving “:

Saving of backups is configured using standard methods, as well as the date format and backup depth parameters. You can specify additional folders for storing backup files – for instance, on another network server or a remote FTP server (see the “Upload to FTP” tab).

You can also use an external archiver or copy the source file without archiving, as well as copy the file tp an FTP server without archiving. These are default capabilities of APBackup and we won’t focus on them in detail here.
Let’s take a look at how we can back up an MS SQL database using an external application. In this case, we will need a small tool that is a part of the MS SQL installation package – OSQL.EXE

For example, an msdb database file can be backed up in the following way:
osql.exe -S -U -P -Q "BACKUP DATABASE msdb TO DISK = 'c:\msdb_data.bak' with skip,init"

You can see a Transact-SQL command after the -Q. In this case, this is the BACKUP DATABASE command. It’s also a good idea to run the BACKUP LOG command to shorten the log file.

ATTENTION:If the server is not located on your system, you will not find c:\msdb_data.bak on it as well. Since this is a server path, the server will save the file on the computer it is installed on. Theoretically, APBackup can download the file from the server if you save it to a shared folder. MS SQL can also save the backup file to another server, but in this case, the MS SQL service must be started under a domain account and not the default localSystem account.

If you are physically starting OSQL in the same location your server is installed in, you can skip the -S trigger – QSQL will connect to a local server by default.
If your server uses Windows Trusted Authentication and your domain name has MSSQL database backup rights, you can use the following command:

osql.exe -S -E -Q "BACKUP DATABASE msdb TO DISK = 'c:\msdb_data.bak' with skip,init"

If it’s a local server, the server name parameter becomes unnecessary:

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

The -E parameter makes the program use a trusted connection – that is, log in under your domain name instead of specifying your login credentials directly: -U -P

If you need to back up several databases, just create a script file (on your system), for instance, 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 run the following command:
osql.exe -S -E -i "C:\mybackup.sql"
or
osql.exe -S -U -P -i "C:\mybackup.sql"

Example
osql.exe -SSQLSERVER -Usa -P1111 -i "C:\mybackup.sql"
(never use such passwords for sa :))

Therefore, we can use a single task in APBackup to back up all databases on an MS SQL server. As the result, we will get 2 files (for instance) in the temporary folder, where each file will represent a database.

Here’s how an MS SQL database backup task is configured in APBackup

Starting OSQL from APBackup

You can also edit some parameters here:

“Start in window” – if set to HIDE, the console window where osql.exe will be started will not be visible. If SHOWNORMAL is selected, you will see the console window when the task is started.

“Wait for end no more” – If set to 0, APBackup will wait for the application to finish for an unlimited period of time. You can set this parameter to 60 minutes, for instance. If the application doesn’t stop working in 60 minutes, APBackup will unpause and will continue executing the current task. And if there are no files in the source folder , this will trigger an execution error.

P.S. MS SQL 2008 manual recommends using a new tool called sqlcmd instead of osql, since osql will not be included in the future versions of MS SQL. The new tool has similar keys and provides the same database backup possibilities as osql.