Automated MS SQL database backup using command line

A customer wanted automated backups for their MS SQL database. Unfortunately MS SQL management can be a little tricky since it doesn’t have something straight forward like phpMyAdmin for MySQL.

  1. Load SQL Server Management Console or Studio Express
  2. Select New Query
  3. Enter the query:
    backup database databasename to disk='C:\db.bak'
  4. Save query to C:\backupdb.sql
  5. Create a new .bat file by right clicking in Windows Explorer, select New Text Document, then rename to backupdb.bat
  6. Enter the command:
    sqlcmd -S .  -i "C:\backupdb.sql" > "C:\backupdb.log"
  7. Navigate to Control Panel, Scheduled Tasks, Add New Task, and select Command Prompt
  8. Change the task path to backupdb.bat, start path to C:\ and set how often you want to backup

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>