Introduction
We have put together the information below to help you understand how SQL Server can be scripted to create a backup or “flat” file.
The concept is that you create a SQL script to start the SQL backups, then run that script from the Run Before command option in the appliance Management Console › Clients › Edit. When the backup begins, we can then capture the SQL backup file created and saved to a certain location by your script file.
Create a script file
Using a simple text editor, create a file with a descriptive name like: SqlDatabaseBackup.sql. For this example, we will save the file on C disk, so the full path would be C:\SqlDatabaseBackup.sql.
The contents of this file are explained below.
Database full backup
Regardless of how many files your database uses, you can create a complete backup of your database with one simple command:
BACKUP DATABASE [dbname] to [backup_device]
For example, BACKUP DATABASE pubs to disk = 'c:\mssql\backup\pubs.bak' creates a complete backup of the pubs database using the file c:\mssql\backup\pubs.bak.
The database can remain online and accessible to users while this backup is being made. To take a consistent snapshot of the database, a copy of the transaction log is also included with the database backup.
In the backup file, SQL Server stores the names and locations of the files actually used in the database. Upon restoring the database, SQL Server recreates all the necessary files, as many as there might be. A database thus restored is equivalent to the point in time that the backup finished. A complete database backup is very simple to execute and use in recovery. If your data does not change often, you might schedule a nightly full backup of your database. Even if you need more frequent backups, regularly scheduling a full database backup each hour might be sufficient (if your database is small enough).
So your SqlDatabaseBackup.sql file simply has one BACKUP DATABASE command for each database you wish to back up. Enter each command on a new line. Be sure to save each database BAK file in a directory included (either inherently or specifically) in your backup file set.
Finally, edit your SQL client on the appliance to include a Run Before Backup command. The command to run should be this: sqlcmd -i C:\SqlDatabaseBackup.sql.
Make sure when you are backing up your SQL Server as a “flat” file that you are backing it up to the same file name each time. Using unique file names can cause problems with customers who use replication.