How to schedule SQL Server Express backups…

This is a repost of a post from an old blog, made on March 7, 2012, that used to be on:

http://wp.me/p25nt4-4e

http://adminramble.com/schedule-sql-server-express-backups/

Original post:

…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.

I had to made and automate a backup of one of the databases that I use on my job today, but the problem was that the only acces I had to the database was with using a sql server authentication and I didn’t have all the permissions on the base 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[c] 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 <codepage> | i:<codepage>[,o:<codepage>]] [-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.