HOW TO: Automated database backup with SQL Express
  • KB ID: KB-01027
  • Created: 09/26/2017 7:41 AM
  • Updated: 10/24/2017 8:25 AM
  • Views: 234
  • 0 Ratings

Summary

You want an automatic backup of your SQL Express databases.

More Information

If you want to achieve an automatic backup of your SQL Express databases, you can do this using the Task Scheduler, SQLCMD, SQL Scripts, and a batch file.

Instructions

Follow these steps:

  1. Download the zip file SQL-DB-Backup.zip and unpack the contents on the server to the folder C:\Tools\Scripts\SQL-Backup.
  2. The two batch files SQL-DB-Backup-Diff.cmd and SQL-DB-Backup-Full.cmd must be adapted.
    In both files you have to set the three variables SQLSERVERNAME, BACKUPPATH and PROTOKOLLPATH.



    In addition you have to insert the three marked rows in the two Files for each database wich you want to backed up. The variable DATABASENAME must then be set.



    In the default script the three Columbus default databases Audit, Columbus & EndPoint as well as the three SQL system databases master, model & msdb are allready included.

  3. The PowerShell script file SQL-DB-Backup-Reorg.ps1 has to be adapted. You have to set the four variables $versions, $BackupPath, $ProtocolPath and $ScriptFile.

    In addition, you must insert the three marked lines into the XML file for each database whose backup files you want to reorganize. The $ ReorgFileFilter variable must then be set.


    In this default script the three Columbus default databases Audit, Columbus & EndPoint as well as the three SQL system databases master, model & msdb are allready included.
    Moreover, the backup and reorg log files are also reorganized with this standard script.

  4. Open the Task Scheduler and import the three templates SQL-DB-Backup-Diff.xml, SQL-DB-Backup-Full.xml, and SQL-DB-Backup-Reorg.xml.
    To do this, the template has to be selected via Import Task... the desired XML file and then saved with OK.

     

  5. The task must now be started once in the following sequence. (1. SQL-DB-Backup-Full, 2. SQL-DB-Backup-Diff, 3. SQL-DB-Backup-Reorg)

    Afterwards, you should check that the backup files have been created correctly and that no errors have been logged in the log files.


     

Products

C7

Feedback

Did you find this article helpful?
Powered by Spider Knowledge Base, a product of brainwaregroup