July 06, 2012

Command line SQL backups on SQL Express


SQL Express doesn't have the same capability as the Standard edition.  So, Maintenance Tasks cannot be used to backup databases.  There are a couple options:

If the database names are known, backups can be run using sqlcmd from the command line or scheduled task:
sqlcmd -S [. or servername][\instance] -Q "BACKUP DATABASE [dbname] TO DISK='[path\file.bak]'"

If the database names are not known or could change over time and you don't want remember to update your script, Microsoft published a TechNet article (KB 2019698) containing a script which creates a stored procedure that can be run using sqlcmd.

Note, the copyright info will need to be commented out in the script.  Also, there are also some "smart" quotes in the batch file contents instead of straight quotes which cause it to fail.

To restore, a similar sqlcmd can be run:
sqlcmd -S [. or servername][\instance] -Q "RESTORE DATABASE [dbname] FROM DISK='[path\file.bak]'"

If you happen to be moving a database to a new server, this TechNet article (KB 918992) explains how to transfer SQL users between servers while retaining SPID and password information.