Automating SQL Express database backups

SQL Server Express is a fantastic, free database engine for small, standalone databases. However, the limitations of the Express database engine can make automating database backups tricky. Due to a lack of a SQL Server Agent, you will likely have to roll your own process for backing up Express databases. While there are enterprise database backup solutions, these solutions are typically licensed by server. Therefore, eating up a valuable license for a small, standalone Express database might be cost prohibitive. There is, however, an easy way to roll your own automated backup solution using a stored procedure, PowerShell, and a Windows scheduled task.

First, we need a stored procedure that accepts a parameter for the directory where we want to save backups. This stored procedure should back up every database except tempdb. Also, the stored procedure needs to be saved in a database that will never get deleted (e.g. master).

Here is a script to create the spBackupDatabases procedure in the master database…

Each database in the instance will save to the backup location specified by the path parameter. As written, the file name for each backup will be in the form _.bak (e.g. InventoryDB_201401072105.bak); however, you can change this to whatever naming convention you prefer. One important factor to note is that the directory you pass to the stored procedure to needs to allow the SQL Server service account “write” permissions. If you are running the SQL Express service as the system account, you will have to grant appropriate permissions to the backup directory for that system.

Let’s test the stored procedure to confirm everything is working. Be sure the destination folder already exists or you will get Sql ErrorNumber 3013.

Now that we have a stored procedure to backup all databases, we need to create a script that will run the stored procedure. As an added caveat, the script needs to delete the backup files that are older than 30 days. PowerShell is a perfect solution for this because it can natively interact with SQL Server using the .Net Framework. The following script will connect to the SQL Express instance, run the new stored procedure, and then “DELETE ALL FILES” that are over 30 days old. Note that the databases are backed-up using the SQL Server service account, but the old files are deleted using the account that is running the PowerShell script. Therefore, be sure both accounts can read and write to the backup directory.

You will note that the database connection string above is using “Integrated Security=True”. This is because I did not want to store the database connection user ID and password in the PowerShell script. Instead, I will schedule the PowerShell script via a Windows Scheduled Task with the credentials for a domain account saved in the Scheduled Task. This makes it harder for someone to obtain a user ID and password for the database. If you are using a SQL Account, and you are okay with putting the credentials in the connection string, then you can use the following…

Finally, the last step for automated SQL Express database backups is creating a Windows Scheduled task. The trick for running a PowerShell script from a Windows Scheduled Task is to specify “PowerShell” in the Program/Script section of the Edit Action setting. Then, in the “Add arguments” section, add the path to your saved PowerShell script.

PowerShellScheduledTaskNote… If you have a problem running the PowerShell script, you can add the “-noexit” switch to the “Add arguments” section so that the error text stays on-screen after the script runs. Just be sure to remove the “-noexit” switch after testing so that PowerShell closes after it finishes running.

    One thought on “Automating SQL Express database backups”

    1. IncredibleMouse on reddit pointed out that you can skip PowerShell and just call the stored procedure from the scheduled task. (Thank you!).

      It’s true, you can skip the PowerShell component and just use a stored procedure and a scheduled job using SQL Command. Using this technique, you could backup your databases by changing the scheduled job to run the following instead of a PowerShell script.

      sqlcmd -Q “EXECUTE [master].[dbo].[spBackupDatabases] ‘c:\SqlBackups\’”

      One caveat is, SQL Command will have to be installed on the server where the scheduled job resides.

      In my approach, I chose PowerShell because I had some requirements to clean up old backups and some other file management behaviors that I didn’t want to run from batch scripts or SQL stored procedures.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    4 × nine =

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">