How to create process to backup SQL Express Database

SQL Server Express is great but it doesn't include SQL Agent, the SQL Server scheduler. So you can't create a backup job to backup your databases like you can with regular SQL Server.

Here's a work around.

Step 1 - Create a batch file using Notepad (just save the file with a .bat extension). Give it any name you want.

This batch file that contains 5 lines:

cd\Program Files\Microsoft SQL Server\90\Tools\Binn\

sqlcmd -S server1 -i C:\sqlbackups\sqlbackup.sql -o c:\sqlbackups\sqlbackuplog.txt

cd\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\

copy Database1backup G:\Shared\DATABASE\SQLServerDatabaseBackups\database1backup.bak

copy Database2backup G:\Shared\DATABASE\SQLServerDatabaseBackups\database2backup.bak

NOTE: the 2nd line of this batch file executes a sql backup script (see step 2).

STEP 2 - Create a sql script file called sqlbackup.sql.

It should contain sql script for each database you want to backup:

USE master;

BACKUP DATABASE [Database1] TO DISK='Database1Backup' with INIT;

BACKUP LOG [Database1] with TRUNCATE_ONLY;

USE Database1;

DBCC SHRINKFILE (2, 20);

BACKUP DATABASE [Database2] TO DISK='Database2Backup' with INIT;

BACKUP LOG [Database2] with TRUNCATE_ONLY;

USE Database2;

DBCC SHRINKFILE (2, 20);

NOTE: this sql script creates a backup in your default location specified in sql server for backups, typically somewhere like:

c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\

Typically, you want to copy the backup file to another server or file location in case your sql database hard drive crashes (you would lose both the database and your backup). So lines 4 and 5 in the batch file in step 1 copy your backup files to another server.

STEP 3- Schedule your batch file in step 1 to run on a schedule using Windows Scheduler.