Corrupted database (1 Viewer)

[darkside40]

I'll definitely have to try that script because this database corruption issue is bugging me for some months now, dont now if my database is too big or whatever.

Thx for sharing that

 

[Vasilich]

@RoChess would you mind to put this script to wiki? as the db crash isn't really a seldom issue (if you read the forums)...

 

[RoChess]

I'll definitely have to try that script because this database corruption issue is bugging me for some months now, dont now if my database is too big or whatever.

Thx for sharing that

I initially created it because the Fanart handler database would corrupt on me almost once a month and would cause stuttering playback from all the SQL errors. Normally I would rely on the manual backups, but in a multi-user environment I was not always available to fix it. That is why I came up with this solution to run more autonomous.

@RoChess would you mind to put this script to wiki? as the db crash isn't really a seldom issue (if you read the forums)...

Yeah, unless a plugin is acting up then dbase corruption is extremly rare, but can be devistating when it happens and there is no recent backup to rely on. A forum post I can make without thinking, but a wiki entry takes me forever as my OCD kicks in, so I rather have somebody else handle that. Maybe @Dadeo can help me out, but he has been MIA for a while

 

[raffe]

Thanks! Nice script! I added a part that also compresses databases with 7z (free from http://www.7-zip.org/download.html ) if the integrity is OK and copies the archive and the log to a NAS at the end. Something like this (the "real" differences are in the lines 10-11, 40-43, 49-50, 89-90):

@Echo Off
::
:: Auto backup/restore MediaPortal databases based on integrity check.
::
:: Author  » RoChess
:: Updated » July 19th 2013
::

:: Make DATETIMEF to something like 2015-10-06_14-18-16 so it can be part of a file name
SET DATETIMEF=%date%_%time:~0,2%-%time:~3,2%-%time:~6,2%
SET DATETIMEF=%datetimef: =0%

SET BACKUP=AutoBackup
SET MEPO_PATH=Team MediaPortal\MediaPortal\database
IF EXIST "%AllUsersProfile%\Application Data\%MEPO_PATH%\*.db3"    SET DB3_PATH=%AllUsersProfile%\Application Data\%MEPO_PATH%
IF EXIST "%AllUsersProfile%\%MEPO_PATH%\*.db3"            SET DB3_PATH=%AllUsersProfile%\%MEPO_PATH%

IF "%DB3_PATH%"=="" GOTO Error_MePo
IF NOT EXIST "%~dp0\sqlite3.exe" GOTO Error_SQLite

:: Check to make sure MediaPortal is not still running
::
tasklist | find /i /c "MediaPortal.exe" >nul &&goto MediaPortalRunning
tasklist | find /i /c "PluginConfigLoader.exe" >nul &&goto MediaPortalRunning
tasklist | find /i /c "Moving Pictures Config.exe" >nul &&goto MediaPortalRunning
tasklist | find /i /c "MP-TVSeries-Configuration.exe" >nul &&goto MediaPortalRunning

echo Auto Backup/Restore STARTED  (%DATE% -- %TIME%)
echo Auto Backup/Restore STARTED  (%DATE% -- %TIME%) >"%~dpn0.log"
echo.>>"%~dpn0.log"

CD /D "%DB3_PATH%"
FOR %%I IN (*.db3) DO (
    CALL:checkSQLite "%%I"
)

echo.>>"%~dpn0.log"
echo    Compressed all DB files, copying compressed backup to NAS
echo    Compressed all DB files, copying compressed backup to NAS >>"%~dpn0.log"
xcopy "%DB3_PATH%\%BACKUP%\MediaPortalAutoBackup%DATETIMEF%*" \\192.168.1.100\Backup\MediaPortal /I /C /D /Y /Q >>"%~dpn0.log"
echo    Copying of MediaPortalAutoBackup%DATETIMEF%.7z is done!
echo.>>"%~dpn0.log"
echo    Copying of MediaPortalAutoBackup%DATETIMEF%.7z is done! >>"%~dpn0.log"
echo    (-: EVERYTHING IS DONE, HAVE A NICE DAY! :-) >>"%~dpn0.log"
echo.>>"%~dpn0.log"
echo Auto Backup/Restore FINISHED (%DATE% -- %TIME%)
echo Auto Backup/Restore FINISHED (%DATE% -- %TIME%) >>"%~dpn0.log"
    echo  *********************************************************************
    echo  *      Copying backup log to NAS...
copy "%~dpn0.log" \\192.168.1.100\Backup\MediaPortal\MediaPortalAutoBackup%DATETIMEF%.log /V /Y
    echo  *
    echo  *      (-: EVERYTHING IS DONE, HAVE A NICE DAY! :-)
    echo  *
    echo  *********************************************************************
    ping 1.1 -n 1 -w 5000 >nul
cd "%~dp0"

:LaunchMediaPortal
    :: Locating MediaPortal folder
    ::
    IF EXIST "%ProgramFiles%\Team MediaPortal\MediaPortal\MediaPortal.exe"  (
        start "MediaPortal" "%ProgramFiles%\Team MediaPortal\MediaPortal\MediaPortal.exe"
    ) ELSE (
        IF EXIST "%ProgramFiles(x86)%\Team MediaPortal\MediaPortal\MediaPortal.exe"  (
            start "MediaPortal" "%ProgramFiles(x86)%\Team MediaPortal\MediaPortal\MediaPortal.exe"
        ) ELSE (
            Goto MediaPortalNotFound
        )
    )
GOTO:EOF

:checkSQLite
    IF %~z1 EQU 0 (
        echo    Aborting: "%~nx1" is ZERO bytes
        echo    Aborting: "%~nx1" is ZERO bytes >>"%~dpn0.log"
        GOTO:EOF
    )
    SET Pass=No
    echo  *********************************************************************
    echo  * Checking Integrity for............: "%~nx1"
    echo PRAGMA integrity_check; | "%~dp0\sqlite3.exe" "%~dpf1" | find /c "ok" > nul &&SET Pass=Yes
    IF "%Pass%"=="Yes" (
        echo  * Integrity OK - Updating backup for: "%~nx1"
        echo    Integrity OK - Updating backup for: "%~nx1" >>"%~dpn0.log"
        xcopy "%DB3_PATH%\%~nx1" "%DB3_PATH%\%BACKUP%\" /I /C /D /Y /Q >nul
        echo  * Compressing this file to archive..: "%~nx1"
        echo    Compressing this file to archive..: "%~nx1" >>"%~dpn0.log"
        echo  *********************************************************************
        echo  Compression progress:
        %~dp0\7z.exe a "%DB3_PATH%\%BACKUP%\MediaPortalAutoBackup%DATETIMEF%" "%DB3_PATH%\%~nx1"
    )
    IF "%Pass%"=="No" (
        echo    ERROR: DATABASE CORRUPT!
        IF NOT EXIST "%DB3_PATH%\%BACKUP%\%~nx1" (
            echo     - No backup exists for: "%~nx1"
            echo     - No backup exists for: "%~nx1" >>"%~dpn0.log"
        )
        IF EXIST "%DB3_PATH%\%BACKUP%\%~nx1" (
            echo     - Restoring last-known good backup for: "%~nx1"
            echo     - Restoring last-known good backup for: "%~nx1" >>"%~dpn0.log"
            xcopy "%DB3_PATH%\%BACKUP%\%~nx1" "%DB3_PATH%\" /I /C /Y /Q >nul
        )
    )
GOTO:EOF

:MediaPortalRunning
    echo   WARNING: MediaPortal is still running!!
    echo.
    echo     MediaPortal and/or Configuration tools are still active.
    echo     Try it again later, after giving it more time to finish.
GOTO ErrorDelay

:MediaPortalNotFound
    echo   WARNING: MediaPortal *NOT* Found!!
    echo.
    echo     Unable to locate MediaPortal in the default location.
    echo     Please adjust this script manually to use your custom folder.
goto ErrorDelay

:Error_MePo
    echo   ABORTING: MediaPortal databases not found
    echo.
    echo     Nothing to do, please verify your MediaPortal installation.
GOTO ErrorDelay

:Error_SQLite
    echo   ABORTING: SQLite.exe command-line tool missing!
    echo.
    echo     Download binary shell for Windows from: http://www.sqlite.org/download.html
    echo     Unzip and copy sqlite3.exe to:
    echo.
    echo         "%~dp0"
GOTO ErrorDelay

:ErrorDelay
    echo.
    echo  *
    echo  *      This window will self-destruct in a few seconds.
    echo  *********************************************************************
    echo.
    ping 1.1 -n 1 -w 5000 >nul
GOTO:EOF