asked on

PS Script to restore latest .sql backup file in to database

The scenario is that we have bunch of .sql dump files and new ones are created before every deployment.

If anything goes wrong with migration scripts someone has to manually drop/create schema and use command line to restore dump from the latest backup.

I am writing a PS script to automate this process.

1. find latest dump from given path
2. drop schema
3. create schema
4. restore dump.

I have accomplished first 3 steps but have wasted a lot of time on the 4th one:

Write-Host "Restoring: " $path
$command = '"mysql.exe -uUsername -pPassword ' + $dbname + ' < ' + $path + '"'
Write-Host $command
cmd /C $command

Open in new window

It says "The system cannot find the file specified."

If i use cmd $command without /C it starts cmd in powershell but doesn't execute $command.

I have tried different variations to execute the command in cmd but it doesn't seem to work, and the reason i will have to use cmd is because powershell doesn't play well with '<'.

I tried Invoke-Item and Invoke-Expression but can't guarantee i used correct syntax.

Any suggestions would be greatly appreciated.

Thanks

Julian Hansen

Is mysql.exe in your path - if you open a command shell and type mysql at the prompt does it run or do you get a not recognised response?

the404guy

ASKER

Yes it's there, i am using it in cmd directly, mysql -uuser -ppass dbase < backup.sql works fine.

Tomas Helgi Johannsson

Hi!

When you take backup using mysqldump you have these options
--add-drop-database
--add-drop-table
Thos will eliminate the need to manually drop the database and/or tables.

Regards,
Tomas Helgi

oBdA

This has to do with the way PS treats variables passed as arguments to external programs. Because $Command contains spaces, it will enclose the complete string in double quotes before passing it to cmd.exe.
But since all you want to do is feed something to mysql.exe's stdin, you should be able to do that in plain PS as well:

Write-Host "Restoring: " $path
Get-Content -Path $Path | & mysql.exe -uUsername -pPassword $dbname

Open in new window

the404guy

ASKER

@ThomasHelgi, can't do that because backups are done before each deployment attempt and drop/create/restore is only done if any of the migration scripts fail during deployment.

@oBdA, thanks, i tried your solution, the machine froze for a couple of seconds and ended up in an error and after that crashed coz of low memory. PS was using over 2k memory. i'm on a good spec dev machine and don't have this issue when using cmd directly. see screen grab of the error.

Thanks for quick responses, appreciated!

the404guy

ASKER

I think it might be helpful if i add some of the script that i'm using to drop/create db:

[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
write-output "Opening connection to MySql..."
$connStr = "server=localhost;port=3306;uid=user;pwd=pass"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection
$conn.ConnectionString = $connStr
Try{
	$conn.Open()
}
Catch{
	write-output $_.Exception.Message
    write-output $_.Exception.ItemName
}

write-host "Drop schema" $dbname
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand
$cmd.Connection  = $conn
$cmd.CommandText = "DROP DATABASE IF EXISTS " + $dbname
Try{
	$cmd.ExecuteNonQuery()
}
Catch{
	write-output $_.Exception.Message
    write-output $_.Exception.ItemName
}
Finally{
	write-output "Success..."
}

Open in new window

I'm open to using a different solution if exists.
Best Regards,

oBdA

That is not a real error, that's a "feature" of the PS ISE. mysql.exe writes the warning about the password to stderr, and the ISE displays these as error. When you run the same script in a "real" PS console, it'll just be displayed.
Enter

net.exe user asdf

Open in new window

both in a real PS console and the ISE to see what's happening.
I have no idea why PS should eat a lot of memory for this.
Try this just to see how it should work:

Get-Content -Path C:\Windows\system.ini | & find.exe /v "`"`""

Open in new window

How big is the sql file, and in which encoding (ASCII, UTF8, Unicode)?

the404guy

ASKER

Hi oBdA, thanks, yes I know that's a warning and it shows up on the cmd also, but something strange is happening here when it reaches that line. see pic, it crashes with error saying mysql.exe bla bla out of memory exception bla bla bla doesn't give me a chance to screen grab or read the whole error message. PS takes up to 6k+ memory before crashing.

the .sql file is 500mb and i believe is using utf8 encoding.

I tried:

Get-Content -Path C:\Windows\system.ini | & find.exe /v "`"`""

Open in new window

and it prints out the contents of ini file.
Best Regards,

ASKER CERTIFIED SOLUTION

oBdA

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

the404guy

ASKER

Awesome! Thanks a million.