0

I am using a Windows Server 2012. I need to perform a dump of my database every two hours and zip the SQL generated file.

The zip file must have this syntax: nameOfFile_date_hour.zip.
I don't need the minutes and second details for the zip file name.
Is there a way to do that?

I know how to do that with Linux but can't figure out how to do that with Windows.

1 Answer 1

1

You can write a PowerShell script and have Windows Task Scheduler run it every 2 hours

A quick and dirty example script:

# Declare variables
$db = "[DB NAME]"
$user = "[DB USERNAME]"
$pw = "[DB PASSWORD]"
$date = (Get-Date).ToString("yyyy-MM-dd_HH.mm.ss")
$mysql_backupfile = ".\backup_$date.sql"
$zipped_backupfile = "$mysql_backupfile.zip"

# Backup MySql database
mysqldump --user=$user --password=$pw --databases $db > $mysql_backupfile

# Compress file using 7zip
sz a -tzip $zipped_backupfile $mysql_backupfile

# Remove uncompressed backup file
rm -Path $mysql_backupfile

Change the database name, username and password and date format to whatever you need.

Note: I'm not entirely sure of the security implications of inserting the username and password directly into the script but if your server is secure I'm sure it'll be fine. But you can load the credentials however you like (e.g. from an environment variable)

Edit for question in comment:

To delete '.zip' files older than 3 days:

Get-ChildItem -Filter '*.zip' |
    where { $_.LastWriteTime -lt (Get-Date).AddDays(-3) } |
    Remove-Item
3
  • Thanks a lot. What would be the script to remove every zipped file older than 3 days? Jan 18, 2016 at 13:02
  • Just a one liner (split on to 3 for readability) edited into the answer
    – Sethi
    Jan 18, 2016 at 14:18
  • ok, thanks. I have found this command on internet : forfiles -p c:\Programmes\Mysql\Backup\ -s -m *.zip -d -2 -c "cmd /c del @path". Is it good too? Is there one better than the other? Jan 18, 2016 at 14:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .