Browsed by
Category: SQL

Backup a MS Sql Server database via command line

Backup a MS Sql Server database via command line

When you use an express version of MS Sql Server you don’t have the option to use the Management Studio to create a maintenance plan for automatically perform database backups. But you can still automatically backup your databases by using the osql command-line tool.

Here’s a simple command to create a backup of a database. You only have to put in your database name and backup location.

Remark: uses trusted connection to connect to database server
osql -E -Q "Backup database databasename to disk=’c:\backup\databasename.bak’ with init if @@error != 0 raiserror (‘Error during backup.’, 16, 127)"

This command uses a trusted connection, but you can also give a username/password to the command. For more information about the parameters that can be used look at the MSDN osql utility page.  This command is normally executed at the computer where MS Sql Server is installed, but it is also possible to run the command from another computer. The only thing you need is the osql command to be available on that computer. Always keep in mind that the backup location which is given to the command should be a location which is present on the computer which is running the MS Sql Server, because the SQL command is always run on the MS Sql Server computer.