Powershell scripts to backup SQL, SVN

If you are using powershell to backup your SVN repositories using svnadmin dump then be aware that piping to a file will silently corrupt your backups.

Powershell likes to change things to UTF-16 when piping, it also changes unix linebreaks to windows ones. This will come back to haunt you when you try and restore.

Problem well described here:

http://thoughtfulcode.wordpress.com/2010/01/29/powershells-object-pipeline-corrupts-piped-binary-data/

Solution here:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.windows.powershell&tid=e4cd89e9-427b-407d-a94f-c24be3f1e36f&cat=&lang=&cr=&sloc=&p=1

In summary, use cmd.exe instead of powershell:

cmd.exe /c svnadmin dump ... `> dumpfile.dump

Note that the backtick on the output redirection is required to stop powershell parsing it.

Well, in the meantime I cobbled together another solution. Maybe it will be of use to someone..

[Run.cmd] --May need to change powershell path

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -nologo -noninteractive -command "C:\Scripts\RunBackup.ps1"

[RunBackup.ps1] --Out-File not having the desired effect, maybe someone can figure out why?

C:\Scripts\SqlBackup.ps1 | Out-File "C:\Scripts\log.txt"C:\Scripts\SVNBackup.ps1 | Out-File "C:\Scripts\log.txt"C:\Scripts\Zip.ps1 | Out-File "C:\Scripts\log.txt"

[SqlBackup.ps1] --You may need to modify which SMO assemblies are loaded, depending on yourversion of SQL server. Don't forget to set $instance and $bkdir.

#http://www.mssqltips.com/tip.asp?tip=1862&home$instance = ".\SQLEXPRESS"[System.Reflection.Assembly]::LoadFrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SMO.dll") | out-null[System.Reflection.Assembly]::LoadFrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SMOExtended.dll") | out-null$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance$bkdir = "c:\Backups" #We define the folder path as a variable$dbs = $s.Databasesforeach ($db in $dbs){     if($db.Name -ne "tempdb") #We don't want to backup the tempdb database     {     $dbname = $db.Name     $dt = get-date -format yyyyMMddHHmm #We use this to create a file name based on the timestamp     $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")     $dbBackup.Action = "Database"     $dbBackup.Database = $dbname     $dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")     $dbBackup.SqlBackup($s)     }     if($db.RecoveryModel -ne 3) #Don't issue Log backups for DBs with RecoveryModel=3 or SIMPLE     {     $dbname = $db.Name     $dt = get-date -format yyyyMMddHHmm #Create a file name based on the timestamp     $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")     $dbBackup.Action = "Log"     $dbBackup.Database = $dbname     $dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_log_" + $dt + ".trn", "File")     $dbBackup.SqlBackup($s)     } }

[SVNBackup.ps1] --Modify repo and backup paths

#set alias to svnadmin exeset-alias svnadmin "C:\Program Files (x86)\CollabNet Subversion Server\svnadmin.exe"#create dumpcmd.exe /c svnadmin dump "C:\Repo" `> "C:\Backups\svn.dmp"#remove aliasremove-item alias:svnadmin

[Zip.ps1] --Need to have 7zip installed, modify 7z.exe path if necessary

#set alias to command line version of 7zipset-alias sevenz "c:\program files\7-zip\7z.exe"#Backups locationcd 'C:\Backups'#rar the contents of the directory$dt = get-date -format yyyyMMddHHmm #We use this to create a file name based on the timestamp$outputFileName = "SQLSVNBackup$dt.7z"$exclude1 = "-x!*.rar"$exclude2 = "-x!*.7z"sevenz a -t7z "$outputFileName" *.* "$exclude1" "$exclude2"#find all .bak files in the immediate directory dir '*.bak' | foreach-object{#remove the bak fileremove-item $_.name}#find all .dmp files in the immediate directory dir '*.dmp' | foreach-object{#remove the dmp fileremove-item $_.name}#find all .trn files in the immediate directory dir '*.trn' | foreach-object{#remove the trn fileremove-item $_.name}#remove 7zip aliasremove-item alias:sevenz

I used GoodSync to backup to WebDAV and scheduled two tasks to run the .cmd file and then sync/backup offsite.