Posey's Tips & Tricks
Using PowerShell To Back Up SQL Server Express Edition
For as long as I can remember, I have always configured the computers within my own organization to store data on network servers as opposed to the data being stored on workstations. That way, if one of my workstations were to fail, I can simply reinstall Windows and a few applications and I’m back in business. Centrally locating my data on a backend server also makes it easier to make sure that the data is being backed up.
Recently however, I started running an application on one of my desktops and this particular application stores data in a locally installed SQL Server Express database. Unfortunately, the way that the application is written makes it impossible to move the database to a backend server. As such, I have little choice but to host the data locally.
This of course raises the question of how best to back up the database. I have never bothered to back up my workstations in the past because there is no data residing on them. Even now, a full workstation backup seems like overkill. As I have recently discovered though, you can use a PowerShell script to back up a SQL Server Express database.
Before I show you how to perform such a backup, there are a few things that you should know. For starters, the script is only intended to be run directly on a workstation and it assumes that SQL Server Express has been installed locally. I am also assuming that your Windows operating system and SQL Server Express are not severely outdated. The script that I have created makes use of the Invoke-SQLCmd cmdlet, which doesn’t exist in some older deployments. Incidentally, if this cmdlet does not exist on your computer then try installing the SQL Server Management Studio.
One more thing that I want to point out is that the script that I have created assumes that you are using Windows Authentication and that you have the necessary permissions to backup and restore the database. The script inherits the permissions of whichever user runs the script, hence there are no credentials provided by the script.
So with that said, here is the script:
$SQLServer = "BrienMain\SQLEXPRESS"
$Database = "SampleData"
$BackupFolder = "C:\Backups"
$BackupFile = "SampleData.bak"
<pre class="codesnippet"># Create the Backup Folder if Necessary
if ((Test-Path $BackupFolder) -eq $False) {
New-Item -ItemType Directory -Path $BackupFolder
}
# Backup the Database
$Cmd = "Invoke-Sqlcmd -ServerInstance " + $SQLServer + " -Database " + $Database + " -Query `"BACKUP DATABASE " + $Database + " TO DISK = '" + $BackupFolder + "\" + $BackupFile + "' WITH INIT`""
Invoke-Expression $Cmd
Write-Host "Backup completed: $BackupFile"
OK, so let’s talk about how this works. The first section sets up some variables. The contents of these variables will be specific to your own environment. You will notice that I have set the $SQLServer variable equal to BrienMain\SQLEXPRESS. BrienMain is the computer name that was assigned to my PC. SQLEXPRESS is the name of the SQL Server instance where the database that I am backing up resides.
Although it probably goes without saying, the $Database variable stores the name of the database that is being backed up. Similarly, the $BackupFolder variable points to the path where the database will be backed up and the $BackupFile variable stores the name of the backup file that will be created.
The next block of code checks to see whether the backup folder currently exists. The Test-Path cmdlet used within this section returns a value of $True or $False. If a value of $False is returned then it means that the backup path does not currently exist, so the New-Item cmdlet is used to create the folder.
The actual backup process consists of two steps. First, I am creating a string variable called $Cmd. Since I can’t enter the backup command directly (at least, not if I want to use variables), I am creating a string that will use the variable contents to mimic the backup command. Once the string has been assembled, I am using the Invoke-Expression cmdlet to execute the string’s contents as though it were a command. In case you are wondering, the command that is embedded in the string is:
Invoke-Sqlcmd -ServerInstance BrienMain\SQLEXPRESS -Database SampleData -Query "BACKUP DATABASE SampleData TO DISK = 'C:\Backups\SampleData.bak' WITH INIT"
It’s worth noting that the INIT command causes any existing backups to be overwritten. If this is not the desired behavior, then you will need to modify the command accordingly.
The process of restoring the database works very similarly to that of backing it up. The command string used for a restoration is:
$Cmd = "Invoke-Sqlcmd -ServerInstance " + $SQLServer + " -Database " + $Database + " -Query `"RESTORE DATABASE " + $Database + " FROM DISK = '" + $BackupFolder + "\" + $BackupFile + "' WITH REPLACE`""
The actual command that is generated by this code is:
Invoke-Sqlcmd -ServerInstance BrienMain\SQLEXPRESS -Database SampleData -Query "RESTORE DATABASE SampleData FROM DISK = 'C:\Backups\SampleData.bak' WITH REPLACE"
The REPLACE command that is embedded in this string causes any existing database with the same name to be overwritten. As such, the restore operation could fail if an existing database is running at the time when you perform the restoration.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.