How To Schedule Backups of SQL 2012 Express Databases

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.

Advertisements

Tags: , , ,

11 responses to “How To Schedule Backups of SQL 2012 Express Databases”

  1. Alexey says :

    There are simple UI tools to schedule such jobs.
    SQLBackupAndFTP is one of the best tools. You can schedule backup jobs with this software (full, differential and transaction log backups), save backups at local or shared network folders, FTP, Dropbox, Box, Google Drive, Amazon S3 and SkyDrive, delete old backups and configure email notifications… Basic features are available in free version. Also you can try all features in trial mode.

    • SysBADmin says :

      Hi Alexey, thanks for the info but I was covering the topic for those (like myself) who are not able to/would rather not, use 3rd party solutions in their environments.

  2. Rich Murphy says :

    Good simple solution. Problem though: I can run the .bat file manually but Task Scheduler will show successful execution and does not actually run the script. Ideas?

  3. Najeeb Abdu Ngb says :

    Is there any way to compress the backup?

  4. Najeeb Abdu Ngb says :

    There is a problem i noticed , if we run it manually twice , the second time backup file will append to the previous backup file.

  5. Adrian says :

    How do you restore from a backup? And if I wanted to do a full restore, including table creation etc would I have to change the SQL script ? – looking at the NOFORMAT etc part

  6. Gregory says :

    No matter what account I use I am getting “Login failed for user ‘SQLBackup’

  7. George says :

    I also get error “error microsoft sql server native client 11.0 login failed for user”, tips?

  8. tushar says :

    you can try vsql-email (http://sql-email.com) it is very simple and saved me a lot of time

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: