MediaPortal scheduled backup using WinRar (now including SQLExpress database)

[Terriff]

I thought I'd share my little scheduled task for backing up the MediaPortal installation, settings, and SQLExpress database using WinRar.

The attached zip contains all of the necessary files. Extract the files into a folder on your hard drive and follow the instructions:

• MediaPortalDirs.txt - tells WinRar which directories to backup
  • Add paths you wish to include in the backup archive to separate lines in this file.
    Warning: I do not advise adding your MSSQL or MySQL paths to this file. Database backups using this method are very prone to errors as they are constantly being written.
  
  
• MediaPortalExcludes.txt - tells WinRar which directories / files to exclude from the backup (currenlty ignores log directories)
  • Add paths (including wildcards) you wish to exclude from the backup archive to separate lines in this file
  
  
• MediaPortalBackup.bat - This file contains the command that runs WinRar using the above .txt files as input.
  • Change the SET variables in this .bat file to paths appropriate for your system.
  
  
• RunMSSQLScript.bat - This file runs SQLCMD.EXE from the SQL Express installation to execute a sql script.
  • This can be run by itself by passing in the name of the sql script file to run (ex. "MediaPortal MSSQL Database Backup.bat" "D:\Backup\MediaPortal SQLExpress Backup.sql")
  • Change the SET variables in this .bat file for your SQLExpress username and password or backup will not work.
  
  
• MediaPortal SQLExpress Backup.sql - This file contains the SQL BACKUP command to backup the MediaPortal database.
  
  
• MediaPortal Backup.job - This is the Scheduled Task job.
  • Drag the .job file into your Scheduled Tasks window (open Control Panel -> Scheduled Tasks).
  • Change the working directory of the scheduled task to the path where your MediaPortalBackup.bat lives.
  • Set the permissions and schedule appropriate for your system.
  
  
• ForFiles.exe - Windows resource kit utility that allows the batch file to delete old archives based on date
  • Note: ForFiles.exe comes with Windows Server 2003 and Vista. These versions use the "/" switch notation versus the "-" switch notation. The ForFiles.exe found in the Win98 resource kit and other location on the net claiming to be from the Windows 2000 resource kit use the "-" switch notation. Using the incorrect switch notation depending on which version you have can cause the archive deletion to fail.

The backup jobs get named as such: "MediaPortalBackup (2008.04.22).rar". If you don't like this, you can change the format by modifying the "BackupName" and "DateFormat" SET variables in the .bat file. Just be careful as it may break the database backup file insertion (database backup is added using another call to Winrar using the generated date for filename. If too much time passes, the database backup will be in a newly generated rar).

Old backups are deleted automatically when this batch file is run. You can specify how long to keep backups by setting the "DaysToKeepBackups" variable.


Updates:

2008.04.22
- incorporated the deletion of old backup archives using the ForFiles.exe tool (thanks uggabugga)
- added -dh switch to the Winrar command to attempt open (shared) file archiving

2008.04.23
- added documentation to batch file
- added self-describing variable names

2008.04.23 - 14:30
- added MS SQL Express database backup support
- modified MediaPortalDirs.txt paths to account for more readable base paths in the backup archive
- added the -ep1 switch to turn off full path inclusion in the rar
- changed the date format to a "daily" format to allow for database backup to be inserted into the archive. If the backup took over 1 minute, a new archive was created with the database instead of including it in the current backup archive.

2008.05.06
- Added set variables in main MediaPortalBackup.bat for database name and backup path/filename
- Removed the hard-coding of paths for SQL backup location, database name, etc
- Upgrade to Winrar 3.71 didn't like the database backup file insertion using -n"filepath". Took out -n and just used the path to file to fix

[charli181]

This looks good, I will add this to tips and tricks section on the wiki.

I am assuming this can be run with MediaPortal open?
Can this also be used for the TV Server?

Well Done.

[uggabugga]

Terrif:

I have used this script for some time and happy to share it.
I'll attach it as a text file so you need to rename the extension to .cmd.

And of course you need to download forefiles.exe. M$ tool from some support pack(can't remember).
Don't think I'm allowed to attach it to this post for copyright something...

Forefiles.exe has to be in the same folder that the script is run from.
Just make a schedule to run the batch file and that should be it.
Do tell if you like this, so others can read this and use this solution too.

[Terriff]

Originally Posted by charli181

I am assuming this can be run with MediaPortal open?

I have run it while MP was open with no problems. However, WinRar has a specific switch (-dh) that will try to open shared files for archiving purposes. I have added it to the batch file in the revised package (attached on the first post).

Originally Posted by charli181

Can this also be used for the TV Server?

Yes. Currently, the MediaPortalDirs.txt includes MediaPortal, TV Server, and the config directory (C:\Documents and Settings\All Users.....). You can add any paths you like to the MediaPortalDirs.txt file and they will be included in the backup rar file.

Originally Posted by uggabugga

M$ tool from some support pack(can't remember).
Don't think I'm allowed to attach it to this post for copyright something...

Thanks for this! That's a quick and dirty way to clean up. Exactly what I needed.

You can get the resource kit files here. Forfiles is included on the page.

I've updated the MediaPortalBackup.bat to include the archive deletions as well (man, that forfiles syntax is picky. Don't add quotes around paths if you change the batch file).

I've also cleaned up the paths somewhat by using variables.

Hope this helps.

[charli181]

Add a link to this thread on the wiki under MediaPortal 1/Tips and Tricks
UserGuides - MediaPortal Wiki Documentation

[uggabugga]

Originally Posted by uggabugga

M$ tool from some support pack(can't remember).
Don't think I'm allowed to attach it to this post for copyright something...

Thanks for this! That's a quick and dirty way to clean up. Exactly what I needed.

You can get the resource kit files here. Forfiles is included on the page.

I've updated the MediaPortalBackup.bat to include the archive deletions as well (man, that forfiles syntax is picky. Don't add quotes around paths if you change the batch file).

I've also cleaned up the paths somewhat by using variables.

Hope this helps.[/quote]

I see that you updated it and are now using the SET=XXX variable
That's always easier if somone else wants to change the pathes and so on.
Feel inner peace that I could somehow contribute with some knowledge

[revs]

Thanks, I was half way through writing my own backup, but think ill just use this now!!

[Terriff]

Originally Posted by uggabugga

I see that you updated it and are now using the SET=XXX variable

Ya, I usually do this, but it was a first iteration of the functionality, and I was only concerned with having it work for me.
When I posted it and saw that it might actually get used, I figure that I should make it easier for others to change their settings (ie. change in one spot versus every instance).

[uggabugga]

See you have updated the script, Thanks
Keep up the nice work

[entrecour]

I am having problems with this on Vista. I get the following message when I try and run the .bat

Backing up database...
Msg 911, Level 16, State 11, Server HTPC-1\SQLEXPRESS, Line 5
Could not locate entry in sysdatabases for database 'MediaPortal'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Server HTPC-1\SQLEXPRESS, Line 5
BACKUP DATABASE is terminating abnormally.
Deleting backup archives older than 7 days...
'E:\mediaportal' is not recognized as an internal or external command,
operable program or batch file.
Backup complete.
E:\mediaportal backup>

I am not familiar with using SQLExpress but when I look under Databases there are two System Databases named
MpTvDbRC1 and TvLibrary.

Why do I need to change to fix the backup?