How to schedule SQL Server Express backups…

…or how to backup your Microsoft SQL Server databases when you don’t have any access to the db machine and db servers local storage.

Today I had to made and automate a backup of one of the databases that I use on my job, but the problem was that the only access I had to the database was with using a sql server authentication and I didn’t have all the permissions on the database or the server. So here are the steps how to backup your MS SQL database without the use of third party tools or SQL agent (which you can’t use in Express editions of SQL Server).

This will be accomplished using the sqlcmd utility which is a free tools that you get with your Server of Management Studio installation.

  1.  Create a sql script for backup using the BACKUP DATABASE statement, my script looks something like this:
    DECLARE @name NVARCHAR(256);
    set @name='\\server\shared_folder\your_db_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak';
    Backup Database your_db To Disk = @name;

    this will create backup file in format of your_db_YYYYMMDD.bak
    If you would like a different format of the date check this link for date formats.

  2. Create a bat file that you can put in Task Scheduler, you can put something like this in bat file:
    sqlcmd -S your_server -U username -P password -i C:\some_folder\your_sql_backup_script.sql

    Remember that if you’re using SQL Express you need to use your_server\instance_id to connect to the server.
    You can also use some other switches if you like:

    Microsoft (R) SQL Server Command Line Tool
    Version 10.50.1600.1 NT x64
    Copyright (c) Microsoft Corporation.  All rights reserved.
    
    usage: Sqlcmd            [-U login id]          [-P password]
      [-S server]            [-H hostname]          [-E trusted connection]
      [-N Encrypt Connection][-C Trust Server Certificate]
      [-d use database name] [-l login timeout]     [-t query timeout]
      [-h headers]           [-s colseparator]      [-w screen width]
      [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
      [-c cmdend]            [-L list servers[clean output]]
      [-q "cmdline query"]   [-Q "cmdline query" and exit]
      [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
      [-u unicode output]    [-r[0|1] msgs to stderr]
      [-i inputfile]         [-o outputfile]        [-z new password]
      [-f  | i:[,o:]] [-Z new password and exit]
      [-k[1|2] remove[replace] control characters]
      [-y variable length type display width]
      [-Y fixed length type display width]
      [-p[1] print statistics[colon format]]
      [-R use client regional setting]
      [-b On error batch abort]
      [-v var = "value"...]  [-A dedicated admin connection]
      [-X[1] disable commands, startup script, enviroment variables [and exit]]
      [-x disable variable substitution]
      [-? show syntax summary]
  3.  Put you bat file in Task Scheduler to run automatically.

Easy graphical tool to automate and schedule backups for those who use SQL Server Express:

Great third-party backup software for automating and scheduling SQL backups is SQLBackupAndFTP, in free version it lets you backup two databases and it also gives you ability to compress your backups and upload them to some FTP server if you want.

 

One thought on “How to schedule SQL Server Express backups…

  1. Also you can try SQLServerBooster. The software has this features:

    – Free
    – Compress (zip), 3 levels of compression, splits into multiple files, up to 95% compression
    – 256-bit AES Encryption
    – Sends to FTP, Amazon S3, Amazon Glacier and Microsoft Azure or network folders
    – proxy compatible
    – Schedules backup tasks
    – Configure it to removes old backups automatically
    – Multiple instances and multiple databases
    – Creates .bak files or script files

    http://www.sqlserverbooster.com

Leave a Reply