Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
PowerShell script to prevent corrupt Access databases from overwriting backups
132 views
Skip to first unread message
CDMAP...@fortunejames.com
May 6, 2015, 6:34:11 PM5/6/15
to
A virus so new that the virus checker didn't know about it hit one of the places I work. It wasn't detected until after my backup routine overwrote my last known good set of data. As a result, I am changing to weekday directory backups. Note that my backups are in addition to the company's normal backups, but they made the same mistake and ended up losing a few day's work because of the most recent good backup they could get. I decided that this would be a good PowerShell exercise. Here's what I have so far:
function Backup-GoodDatabase {
<#
.SYNOPSIS
Determine if an Access database is not corrupt before saving a backup copy
.DESCRIPTION
For each database file given as input, the function will test to see if Access
can successfully open it (invisibly), allowing backup files to be checked before
overwriting older files. The script is designed primarily for backing up backend
Access databases on a server, so no check is made to see if a user has the
database open. The backup drive is assumed to have the same directory structure
as the network backup files.
.EXAMPLE
(After dot sourcing)
Backup-GoodDatabase "C:\Databases\Corrupted.accdb"
.EXAMPLE
Backup-GoodDatabase "C:\Databases\Test.accdb", "C:\Databases\Test-Corrupt.accdb"
.EXAMPLE
Get-Content C:\Databases\DatabaseList.txt | Backup-GoodDatabase -verbose
.PARAMETER DatabasePath
Fully qualified path(s) to the database file(s) to test
.PARAMETER BackupDriveRootDirectory
The root of the target directory, e.g. 'F:\'
.PARAMETER logname
The name of a file for storing the result of backup attempt(s).
#>
[CmdletBinding(SupportsShouldProcess=$True,ConfirmImpact='Low')]
param
(
[Parameter(Mandatory=$True,
ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage='What database file(s) would you like to backup?')]
[Alias('AccessFile')]
[string[]]$DatabasePath,
[string]$BackupDriveRootDirectory = 'F:\',
[string]$logname = 'C:\Databases\BackupResults.txt'
)
begin {
Write-Output "Starting...";
#See if I forgot to insert the USB drive.
If (!(Test-Path $BackupDriveRootDirectory)) {
Write-Output "$BackupDriveRootDirectory was not found! Exiting.";
Start-Sleep -s 2;
Break
}
#Start with a clean log file
if (Test-Path $logname) {
Clear-Content $logname;
}
else {
New-Item $logname -type file
}
[int]$i = -1;
New-Variable -name MaxDatabases -value 20 -Option ReadOnly;
Write-Verbose "Initializing Arrays";
$OpenSuccessful = @($False) * $MaxDatabases;
$FullDatabasePath = @("") * $MaxDatabases ;
$Destination = @("") * $MaxDatabases
}
process {
foreach ($File in $DatabasePath) {
Write-Verbose "Processing $File";
$i++;
$Database = New-Object -ComObject Access.Application;
$Database.OpenCurrentDatabase($File);
Start-Sleep -s 2;
$Path = [IO.Path]::ChangeExtension($File, "laccdb")
$OpenSuccessful[$i] = Test-Path -Path $Path;
Write-Debug "Calculating Destination Path";
$FullDatabasePath[$i] = [IO.Path]::GetFullPath($File);
$Root = [IO.Path]::GetPathRoot($File);
$RootLength = [IO.Path]::GetFullPath($Root).Length;
$PathLength = [IO.Path]::GetFullPath($File).Length;
$NewLength = $PathLength - $RootLength;
$Destination[$i] = "F:\" + [IO.Path]::GetFullPath($File).SubString($RootLength, $NewLength);
Write-Debug $FullDatabasePath[$i];
Write-Debug $Destination[$i]
}
}
end {
$Database.DoCmd.Quit(-1)
for ($j = 0; $j -le $i; $j++) {
Write-Debug $OpenSuccessful[$j];
$Source = $FullDatabasePath[$j];
$Target = $Destination[$j];
if ($OpenSuccessful[$j]) {
Write-Verbose "Backing up $Source.";
Write-Verbose "Copy-Item $Source -destination $Target -force";
Copy-Item $Source -destination $Target -force;
Add-Content $logname "Database $Source was backed up successfully."
}
else {
Add-Content $logname "Database $Source was not backed up because it could not be opened."
}
}
Write-Output "Done."
notepad $logname
}
}
Sample Input:
PS C:\PowerShell\Scripts> . .\Backup-GoodDatabase.ps1
PS C:\PowerShell\Scripts> Backup-GoodDatabase "C:\Databases\Test.accdb", "C:\Databases\Corrupted.accdb" -verbose
ISE Console Output:
Starting...
VERBOSE: Initializing Arrays
VERBOSE: Processing C:\Databases\Test.accdb
VERBOSE: Processing C:\Databases\Corrupted.accdb
VERBOSE: Backing up C:\Databases\Test.accdb
VERBOSE: Copy-Item C:\Databases\Test.accdb -destination F:\Databases\Test.accdb -force
Done.
logfile contents:
Database C:\Databases\Test.accdb was backed up successfully.
Database C:\Databases\Corrupted.accdb was not backed up because it could not be opened.
A possible improvement would be to specify a directory and have the script backup any Access databases in that directory that aren't corrupt. Another improvement could be to relax the condition that the directory structure be the same on the source and target media. With that improvement, it would be possible to backup the databases automatically to a set of weekday directories. I should also ensure that the databases to backup exist by using Test-Path. I was able to get up to speed with the basics of PowerShell quickly because of the ample free training tutorials and videos available. Also, many of the tutorials helped convey a proper spirit about how PowerShell can be used.
James A. Fortune
CDMAP...@FortuneJames.com
Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular
procedure, but I am under no obligation to modify these examples to
provide added functionality or to construct procedures to meet your
specific requirements. Any code samples posted contain no known
hidden material defects. However, anyone who uses any code sample
posted does so with the understanding that they are responsible for
any testing of any illustrative code sample for any particular use.
Furthermore, anyone using an illustrative code sample I provide or
code derived from it does so at their own risk.
function Backup-GoodDatabase {
<#
.SYNOPSIS
Determine if an Access database is not corrupt before saving a backup copy
.DESCRIPTION
For each database file given as input, the function will test to see if Access
can successfully open it (invisibly), allowing backup files to be checked before
overwriting older files. The script is designed primarily for backing up backend
Access databases on a server, so no check is made to see if a user has the
database open. The backup drive is assumed to have the same directory structure
as the network backup files.
.EXAMPLE
(After dot sourcing)
Backup-GoodDatabase "C:\Databases\Corrupted.accdb"
.EXAMPLE
Backup-GoodDatabase "C:\Databases\Test.accdb", "C:\Databases\Test-Corrupt.accdb"
.EXAMPLE
Get-Content C:\Databases\DatabaseList.txt | Backup-GoodDatabase -verbose
.PARAMETER DatabasePath
Fully qualified path(s) to the database file(s) to test
.PARAMETER BackupDriveRootDirectory
The root of the target directory, e.g. 'F:\'
.PARAMETER logname
The name of a file for storing the result of backup attempt(s).
#>
[CmdletBinding(SupportsShouldProcess=$True,ConfirmImpact='Low')]
param
(
[Parameter(Mandatory=$True,
ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage='What database file(s) would you like to backup?')]
[Alias('AccessFile')]
[string[]]$DatabasePath,
[string]$BackupDriveRootDirectory = 'F:\',
[string]$logname = 'C:\Databases\BackupResults.txt'
)
begin {
Write-Output "Starting...";
#See if I forgot to insert the USB drive.
If (!(Test-Path $BackupDriveRootDirectory)) {
Write-Output "$BackupDriveRootDirectory was not found! Exiting.";
Start-Sleep -s 2;
Break
}
#Start with a clean log file
if (Test-Path $logname) {
Clear-Content $logname;
}
else {
New-Item $logname -type file
}
[int]$i = -1;
New-Variable -name MaxDatabases -value 20 -Option ReadOnly;
Write-Verbose "Initializing Arrays";
$OpenSuccessful = @($False) * $MaxDatabases;
$FullDatabasePath = @("") * $MaxDatabases ;
$Destination = @("") * $MaxDatabases
}
process {
foreach ($File in $DatabasePath) {
Write-Verbose "Processing $File";
$i++;
$Database = New-Object -ComObject Access.Application;
$Database.OpenCurrentDatabase($File);
Start-Sleep -s 2;
$Path = [IO.Path]::ChangeExtension($File, "laccdb")
$OpenSuccessful[$i] = Test-Path -Path $Path;
Write-Debug "Calculating Destination Path";
$FullDatabasePath[$i] = [IO.Path]::GetFullPath($File);
$Root = [IO.Path]::GetPathRoot($File);
$RootLength = [IO.Path]::GetFullPath($Root).Length;
$PathLength = [IO.Path]::GetFullPath($File).Length;
$NewLength = $PathLength - $RootLength;
$Destination[$i] = "F:\" + [IO.Path]::GetFullPath($File).SubString($RootLength, $NewLength);
Write-Debug $FullDatabasePath[$i];
Write-Debug $Destination[$i]
}
}
end {
$Database.DoCmd.Quit(-1)
for ($j = 0; $j -le $i; $j++) {
Write-Debug $OpenSuccessful[$j];
$Source = $FullDatabasePath[$j];
$Target = $Destination[$j];
if ($OpenSuccessful[$j]) {
Write-Verbose "Backing up $Source.";
Write-Verbose "Copy-Item $Source -destination $Target -force";
Copy-Item $Source -destination $Target -force;
Add-Content $logname "Database $Source was backed up successfully."
}
else {
Add-Content $logname "Database $Source was not backed up because it could not be opened."
}
}
Write-Output "Done."
notepad $logname
}
}
Sample Input:
PS C:\PowerShell\Scripts> . .\Backup-GoodDatabase.ps1
PS C:\PowerShell\Scripts> Backup-GoodDatabase "C:\Databases\Test.accdb", "C:\Databases\Corrupted.accdb" -verbose
ISE Console Output:
Starting...
VERBOSE: Initializing Arrays
VERBOSE: Processing C:\Databases\Test.accdb
VERBOSE: Processing C:\Databases\Corrupted.accdb
VERBOSE: Backing up C:\Databases\Test.accdb
VERBOSE: Copy-Item C:\Databases\Test.accdb -destination F:\Databases\Test.accdb -force
Done.
logfile contents:
Database C:\Databases\Test.accdb was backed up successfully.
Database C:\Databases\Corrupted.accdb was not backed up because it could not be opened.
A possible improvement would be to specify a directory and have the script backup any Access databases in that directory that aren't corrupt. Another improvement could be to relax the condition that the directory structure be the same on the source and target media. With that improvement, it would be possible to backup the databases automatically to a set of weekday directories. I should also ensure that the databases to backup exist by using Test-Path. I was able to get up to speed with the basics of PowerShell quickly because of the ample free training tutorials and videos available. Also, many of the tutorials helped convey a proper spirit about how PowerShell can be used.
James A. Fortune
CDMAP...@FortuneJames.com
Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular
procedure, but I am under no obligation to modify these examples to
provide added functionality or to construct procedures to meet your
specific requirements. Any code samples posted contain no known
hidden material defects. However, anyone who uses any code sample
posted does so with the understanding that they are responsible for
any testing of any illustrative code sample for any particular use.
Furthermore, anyone using an illustrative code sample I provide or
code derived from it does so at their own risk.
CDMAP...@fortunejames.com
May 7, 2015, 10:05:04 AM5/7/15
to
James A. Fortune
CDMAP...@FortuneJames.com
CDMAP...@fortunejames.com
Jun 11, 2015, 11:49:12 AM6/11/15
to
On Wednesday, May 6, 2015 at 6:34:11 PM UTC-4, cdmap...@fortunejames.com wrote:
> A virus so new that the virus checker didn't know about it hit one of the places I work. It wasn't detected until after my backup routine overwrote my last known good set of data. As a result, I am changing to weekday directory backups. Note that my backups are in addition to the company's normal backups, but they made the same mistake and ended up losing a few day's work because of the most recent good backup they could get. I decided that this would be a good PowerShell exercise. Here's what I have so far:
I note that the same idea (seeing if the database will open or not) can be used to create a similar backup system in Access alone with the same amount of coding or less. Still, PowerShell continues to provide unexpected benefits when working with Access. PowerShell's use of the .NET Framework is causing me to change some of my focus to Visual Studio for awhile. Just as Access can be thought of as a GUI for SQL :-), PowerShell can be thought of as a GUI for the .NET Framework :-). Obviously, they're both capable of more than that. I'm optimistic about what Access, PowerShell and Visual Studio can do when they're all working together.
James A. Fortune
CDMAP...@FortuneJames.com
ricardo.brill...@gmail.com
Aug 5, 2015, 9:51:28 AM8/5/15
to
CDMAP...@fortunejames.com
Aug 5, 2015, 1:27:13 PM8/5/15
to
On Wednesday, August 5, 2015 at 9:51:28 AM UTC-4, ricardo.brill...@gmail.com
James A. Fortune
CDMAP...@FortuneJames.com
> Here is an alternative: http://www.msaccessdeveloper.pro/#!Scheduled-backups/c6x8v/55b0e94f0cf25b8bf7f07a1f
Note: If PowerShell is used to control the backup process, 'switch ((get-date).DayOfWeek)' will allow sending the backup to different daily directories.
James A. Fortune
CDMAP...@FortuneJames.com
0 new messages