Help on getting started with powershell - rewrite an old BAT + SQL file

Question

Hi, I'm totally new to powershell, and I need some help to get started. What I need is to write a small script that backup a SQL database, but every time with different name (to keep the last 4-5 versions only). Right now I have a BAT, which just launch osql with a sql script, as below

REM BAT file starts here
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -S myServer -E -iC:\Scripts\backupDB.sql

And the backup script is

BACKUP DATABASE [MyDB] TO  DISK = N'C:\Backup\MyDB.bak' WITH NOFORMAT, INIT,  NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

This BAT is run by task scheduler. Obvious, it does not creates different DB backup files. What I would like is to replace the whole thing with a powershell script, which will look in target directory and keep only last N backup files (starting from the newest one), then generate a backup filename like

MyDB-yy.mmdd.hhmmss.bak

and backup in that.

I could create a simple C# console application to do that, but I would like to start playing and testing with Powershell.

Any help is appreciated.

Thank you

Answer 1

Something like this should work:

if (Test-Path C:\Backup\MyDB.bak)
{
    Rename-Item C:\Backup\MyDB.bak `
                ("C:\Backup\MyDB-{0:yy.MMdd.hhmmss}.bak" -f (get-date))
}

Get-ChildItem C:\Backup\MyDB-*.bak | Sort Name -Desc | Select -skip 5 | 
    Remove-Item

$osql = "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe"
& $osql -S myServer -E -iC:\Scripts\backupDB.sql

Answer 2

Or you could skip the .sql file and do something like this. It could also be done with SMO but since you mentioned C# this might make more sense to you.

$datePart = Get-Date -Format "yy.MMdd.HHmmss"
$oConn = New-Object System.Data.SqlClient.SqlConnection("Server=myServer;Database=master;Integrated Security=True")
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $oConn
$oConn.Open()
$SqlCmd.CommandText = "BACKUP DATABASE [MyDB] TO  DISK = N'C:\Backup\MyDB-$datePart.bak' WITH NOFORMAT, INIT,  NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
$SqlCmd.ExecuteNonQuery()
$oConn.Close()