Can I do a nightly backup of the QIE database?

Question

I would like to do a nightly automated backup of my MySQL, MariaDB, MSSQL, or H2 database.  Can this be scripted?

Answer 1

For MS SQL Server the easiest way it to schedule a backup via SQL Server Management Studio (for more details see the links below).  However, for consistency here is a sample batch file do backup the QIE database.

echo off

 

echo -- BACKUP DATABASE --

set qieDatabaseName=qie

 

set DATESTAMP=%DATE:~-4%.%DATE:~7,2%.%DATE:~4,2%

set SERVERNAME=localhost

 

sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%qieDatabaseName%] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Backup\%qieDatabaseName%-%DATESTAMP%.bak' WITH NOFORMAT, NOINIT,  NAME = N'%qieDatabaseName% backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"

More information can be found at: 

https://msdn.microsoft.com/en-us/library/ms187048.aspx

 

Answer 2

The following script is used to back up the MariaDB Database. The easiest way to do this is with a batch file.  You can take the attached one, then create a task to run nightly to do the mysqldump.

@echo off

 

REM "MySQL username"

SET muser="username"

 

REM "MySQL password"

SET mpass="password"

 

REM "Install Directory (must contain mysql and mysqldump executables)"

SET mbackup="C:\MariaDB\mariadb-5.5.35-winx64\bin"

 

REM "Backup destination for mysql"

SET mysqlbackup="C:\MariaDB\backup\"

 

REM "Exclude database backup"

SET dbname="('information_schema','performance_schema')"

 

REM "Dump database to file"

for /f %%i in ('"%mbackup%\mysql -u%muser% -p%mpass% -e "select distinct table_schema from information_schema.tables where table_schema NOT IN %dbname%" --skip-column-name "') do (

    %mbackup%\mysqldump %%i -u%muser% -p%mpass% > %mysqlbackup%%%i.sql

)

More information can be found at: 

https://mariadb.com/kb/en/backup-and-restore-overview/

Answer 3

For H2 there are several ways to backup the database.  The easiest is to stop QIE, copy the h2 database files, restart QIE.  Doing it this way requires QIE to be stopped.  

Note: if you need a backup plan for the QIE database, Qvera suggest using MySql or MS SQL Server.

@echo off

 

REM "The name of the QIE service"

set qieServiceName="QIEService"

 

REM "Install directory location for QIE H2 database"

SET qieH2DatabaseSourceDir="C:\ProgramData\QIE\h2"

 

REM "Backup directory location root"

SET backupDir="C:\ProgramData\QIE\h2backup"

 

REM Check if the QIE service is running then stop it.

for /F "tokens=3 delims=: " %%H in ('sc query %qieServiceName% ^| findstr "        STATE"') do (  

  if /I x"%%H" == x"RUNNING" (  

REM stop the QIE service

    net stop %qieServiceName%

  ) 

)

 

REM Check again for the stopped QIE service before continuing

for /F "tokens=3 delims=: " %%H in ('sc query %qieServiceName% ^| findstr "        STATE"') do (  

  if /I x"%%H" == x"STOPPED" (

    REM Get backup subfolder name using the day name for the day of the week

    for /f %%d in ('"powershell Get-Date -format ddd"') do @SET rotationFolderName=%%d

 

REM use the next line as an alternate for the above for a monthly rotation

REM for /f %%d in ('"powershell (Get-Date).Day"') do @SET rotationFolderName=%%d

 

    REM Copy the H2 database files for a 7 day rotation by placing them in a folder by the day of the week.    

    xcopy /I /Y %qieH2DatabaseSourceDir%\* %backupDir%\%rotationFolderName%

    

REM Start the QIE service after copy

net start %qieServiceName%

  )

)

More information can be found at:

http://www.h2database.com/html/tutorial.html#upgrade_backup_restore