How To Schedule Backups of SQL 2012 Express Databases

Original Link

SQL Express does not have a SQL agent, nor maintenance plans so there is no way to point and click your way to victory. But don’t despair, you can script it quite easily! In this example we’ll back up a SQL database to a date stamped file, and then back it up to a remote server. In my case, I wanted to keep two days worth of backups locally on the server and only ever keep one on the remote server as the remote server backs that file off to tape when it has landed as part of that servers nightly run.

Log onto the SQL 2012 Express server in question and create the following SQL script using notepad:

DECLARE @pathName NVARCHAR(512) 
SET @pathName = 'C:\SQL Backup\NameOfDataBaseBackupFile_' + Convert(varchar(8), GETDATE(), 112) + '.bak' 
BACKUP DATABASE [DatabaseName] TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N'db_backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Save it as whatever you like, in this example I’ll call it NightlyBackup.sql

Note, the script sets your backup directory path, the name of the backup file and then appends the days date to it so you end up with a file called NameOfDataBaseBackupFile_YYYMMDD.bak in your back up folder.

Next, create the following batch file using notepad:

sqlcmd -S servername\INSTANCENAME -U sqluser -P sqluserpassword -i "PathToNightlyBackup.sql"
forfiles /p "C:\SQLBackupLocation" /s /d -2 /c "cmd /c del @file : date >= 2 days >NUL"
del \\RemoteBackupServer\SQLBackupDIR\* /Q
robocopy C:\SQLBackupLocation" \\RemoteBackupServer\SQLBackupDIR\ /maxage:1

Again, save it as something meaningful, in this example SQLbackup.bat

Note, this script performs 4 tasks:

1. It uses the sqlcmd.exe to connect to your SQL server and instance and runs the SQL script created earlier. It’s a good idea to create a SQL user that simply has _backupoperator rights to the database you wish to back up

2. It then deletes any files in the backup directory over 2 days old

3. It deletes anything in the remote backup location path

4. It backs up any files younger than a day to the remote location

Once you’ve tweaked these to you liking, you’ll need to schedule the batch script to run using Task Scheduler. I won’t go into detail on this as it’s covered elsewhere on the net but I created a basic task on a schedule appropriate to my needs, told it to start a program, chose the batch file (remembering to at it’s folder location to the “Start In” field),  set it to run whether the user is logged on or not and ensured it was running as a user that had the ability to run both the bactch script and access the remote location.