Automated SQL Backup

An automated SQL backup consists of three parts: a SQL query that performs the backup, a batch file that executes the SQL query, and a Scheduled Task that runs the batch file at the designated interval. 

NOTE: These instructions apply to SnapStream systems running Windows Server 2008 R2, Windows Server 2012 R2, or Windows Server 2016.

1. SQL backup query

The backup query should be constructed as follows and saved in plain text with the extension .sql

TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SNAPSTREAMTVDB\MSSQL\Backup\[name_of_backup_file].bak'


2. Executing batch file

This is an example of a batch file that can be used to execute the SQL query above:

ren "C:\Program Files\Microsoft SQL Server\MSSQL12.SNAPSTREAMTVDB\MSSQL\Backup\[name_of_backup_file].bak*" [name_of_backup_file_old].bak
sqlcmd -S localhost\snapstreamtvdb -U sa -P [PASSWORD**] -i "C:\SnapStream Support\master node recovery\Backup\[name_of_SQL_query].sql"
del "C:\Program Files\Microsoft SQL Server\MSSQL12.SNAPSTREAMTVDB\MSSQL\Backup\[name_of_backup_file_old].bak" /Q

Note that this batch file includes steps to rename and delete the previous backup file in addition to backing up the current database. 

*This file path is specific to SQL Server 2012. Your system may have a slightly different file path, depending on the version of SQL Server that is installed.

**Contact SnapStream Support to obtain the password for the SQL database on your system.


3. Scheduled Task

The batch file can be scheduled to run automatically using Windows Task Scheduler


4. Additional steps

If desired, steps can be taken to copy the backup file to another location or take other steps, using additional batch files and scheduled tasks. 

Have more questions? Submit a request


Please sign in to leave a comment.