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