How to backup the database automatically to the specify path with Continuum Standalone system

Issue

Continuum Standalone system use SQL Express without function of maintenance, So other method need to be implemented  to do this task

Product Line

Andover Continuum

Environment

Cyberstation

SQL Express

Cause

Require to backup the database with a record.

Resolution

  1. Install SQL Server Management  Studio Tools for SQL Express
  2. Backup the database to specify path, such as "D:\backup\ContDB_Mon.bak"
  3. Run SQL Server Management Studio Express and create a new query
  4. Copy the following SQL syntax and paste in this new query then  run it

    EXEC Sp_Addumpdevice 'disk', 'ContinuumDB', 'D:\backup\ContDB_Mon.bak'
     
  5. Create another new query called ContDB_BU_Mon.sql, copy the following SQL syntax and paste in the query  then run it

    Select getdate() as "ContDB backup startTime"
    go
    BACKUP DATABASE  [ContinuumDB]  TO [ContinuumDB] with Format, INIT,NAME= N'Contdb_Mon Database Backup', SKIP, Norewind, NOUNLOAD, STATS = 10
    go
    select getdate() as "Contdb Backup Endtime"
    go 
  6. Close the query above and save it to D:\backup\ContDB_BU_Mon.sql
  7. Create a .txt file with the following command then save it as .bat file

    OSQL -S(Local)\SQLEXPRESS -E -i  "D:\backup\ContDB_BU_Mon.sql" -O  "D:\Backup\Contdb_BU_Mon.log"
     
  8. From Windows Control panel > Performance and Maintenance > Scheduled Tasks, Add Scheduled Task, select the "Browse" button, find the .bat file you want to run, then set the time schedule you desired.

After the scheduled task created successfully, the database backup will be executed scheduled with record. the record was written in the file Contdb_BU_Mon.log.