Corrupted database (1 Viewer)

darkside40

Portal Pro
August 17, 2009
482
32
40
Ruhrgebiet
Home Country
Germany Germany
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
 

RoChess

Extension Developer
  • Premium Supporter
  • March 10, 2006
    4,434
    1,897
    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

    Portal Pro
    March 28, 2008
    260
    80
    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):
    Code:
    @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
     
    Last edited:

    Users who are viewing this thread

    Top Bottom