832 - ESE backup script example: SQL Server database dump

Follow the steps below to backup a Microsoft SQL Server database dump with ESE.

You will need:

• The name of a SQL Server instance, e.g. MSSQLSERVER or SQLEXPRESS
• Some dump space on disk that needs to be included in the selection
• Permission to write to the dump location (this may need to be explicitly granted)
• A user with db_owner rights to the database, which can be an SQL user (e.g. SA) or a Windows user with the appropriate permissions
• The Redstor Pro service (to be run as the Windows user with permissions, otherwise SQL credentials need to be configured in the script as shown below)

How to backup a SQL server database dump

1. Create a script

Create a batch file containing the following:

SQLCMD.exe –S <SERVERNAME>\<INSTANCENAME> -Q"BACKUP DATABASE <DatabaseName> TO DISK = '<FILEPATH>'" [-U <USERNAME>] [-P <PASSWORD>]

Examples:

If backing up "MYDB" to C:\SQLDump\MYDB.bak using SA credentials:

SQLCMD.exe –S LOCALHOST\SQL2014 -Q"BACKUP DATABASE MYDB TO DISK = 'C:\SQLDUMP\MYDB.BAK'" –U SA –P Password123

If your service is running as a user with adequate permissions, the credentials can be omitted. For example:

SQLCMD.exe –S LOCALHOST\SQL2014 -Q"BACKUP DATABASE MYDB TO DISK = 'C:\SQLDUMP\MYDB.BAK'"

Should you wish to back up multiple databases, these can be specified by repeating the command with different database names and filenames. For example:

SQLCMD.exe –S LOCALHOST\SQL2014 -Q"BACKUP DATABASE FINANCE TO DISK = 'C:\SQLDUMP\FINANCE.BAK'"

Note:

• Use a static filename for .bak files, so that files will be patched and not added as new files.
• Follow the steps in FAQ 835 to truncate (shrink) the logs if desired.

2. Test your script

It is strongly recommended to test the the script from command-line before adding it to the Agent for automation.

3. Configure the script in the Agent

Add the script to the Agent configuration, set to run before each backup.

4. Add the script to the backup selection

Ensure that the dump locations are included in the backup selection.

Note: Transaction log backups can be configured and scripted separately, but should be added to backup selection as well.