Automating Your Database Backups With ExportSQLCE Database backups protect your application data against corruption, hardware failure, and accidental deletion. If you use SQL Server Compact Edition (SQL CE), you know it lacks a built-in SQL Agent for scheduling automated backups. Fortunately, the open-source community provides a powerful command-line utility called ExportSQLCE to solve this problem.
This guide will show you how to automate your SQL CE database backups by combining ExportSQLCE with Windows Task Scheduler. What is ExportSQLCE?
ExportSQLCE is a command-line tool developed by Erik Ejlskov Jensen. It generates SQL scripts from a SQL Server Compact database file (.sdf). The tool exports both the database schema (tables, indexes, keys) and the actual data into a standard .sql script file. Why use ExportSQLCE for backups?
Human-Readable Backups: It creates plain text SQL scripts, allowing you to easily view changes using version control systems like Git.
Platform Migration: The generated scripts can be executed directly against full SQL Server or Azure SQL Database, making future migrations seamless.
No Database Locks: You can script the database while the application is running, minimizing downtime. Step 1: Download and Extract ExportSQLCE
Before automating the process, you need to get the command-line utility.
Download the latest version of the ExportSQLCE command-line tool from GitHub or the NuGet package manager.
Extract the contents to a permanent folder on your server or local machine (e.g., C:\Tools\ExportSQLCE</code>). Step 2: Create the Backup Batch Script
To automate the tool, you need to write a simple Windows Batch (.bat) script. This script will run ExportSQLCE, dynamically name the output file with the current date, and save it to your backup directory. Open Notepad or your favorite text editor. Paste the following script:
@echo off :: Configuration SET “DB_PATH=C:\AppData\MyDatabase.sdf” SET “BACKUP_DIR=D:\Backups\SQLCE\” SET “TOOL_PATH=C:\Tools\ExportSQLCE\ExportSQLCE.exe” :: Generate timestamp (YYYY-MM-DD) FOR /F “tokens=2-4 delims=/ ” %%a IN (‘date /t’) DO SET “DATE_STAMP=%%c-%%a-%%b” :: Set output file name SET “OUTPUT_FILE=%BACKUPDIR%Backup%DATE_STAMP%.sql” :: Ensure backup directory exists IF NOT EXIST “%BACKUP_DIR%” mkdir “%BACKUP_DIR%” :: Run ExportSQLCE “%TOOL_PATH%” “Data Source=%DB_PATH%;” “%OUTPUT_FILE%” schema,data echo Backup completed successfully at %DATE_STAMP% Use code with caution. Save the file as AutomateBackup.bat in your tools folder. Script Breakdown: Data Source=: Points to your source SQL CE .sdf file.
schema,data: Tells the tool to export both the structural design and the rows of data. You can change this to just data or schema if needed. Step 3: Automate with Windows Task Scheduler
With your batch file ready, you can now schedule Windows to run it automatically at regular intervals. Open the Start Menu, type Task Scheduler, and press Enter. In the right-hand Actions pane, click Create Basic Task.
Name the Task: Type a clear name, like SQL CE Database Backup, then click Next.
Trigger: Choose your preferred frequency (e.g., Daily) and click Next. Set the specific time you want the backup to run (preferably during low-traffic hours). Action: Select Start a program and click Next.
Script Location: Click Browse and select the AutomateBackup.bat file you created in Step 2. Finish: Review your settings and click Finish. Step 4: Verify Your Backups Never assume a backup system works until you test it. Right-click your new task in Task Scheduler and click Run. Navigate to your backup folder (D:\Backups\SQLCE</code>). Verify that a new .sql file has been generated.
Open the .sql file in a text editor to ensure it contains valid CREATE TABLE and INSERT statements.
By spending a few minutes setting up ExportSQLCE and Windows Task Scheduler, you ensure your application data remains secure, portable, and completely automated.
If you want to optimize this setup further, let me know. I can show you how to: Add automatic ZIP compression to save disk space.
Write a script to delete backups older than 30 days so your drive doesn’t fill up. Add error logging to track if a backup fails.
Leave a Reply