How to backup all SQL Server databases with script

It’s pretty easy and straightforward to backup single database through SQL Server Management Studio, but when number of databases to backup are more than couple of dozen, SSMS is pain to use. In such situation where SSMS is not an option, you can always use T-SQL.  Good thing is that you only need few commands to do this.

Here is the script:

This beautiful tiny TSQL Script will generate backup of all databases excluding system default databases such as ‘master’, ‘model’, ‘msdb’ and ‘tempdb’. If you want to exclude some databases, just add their names after ‘tempdb’ in “WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)”

Note: This script was originally written by Greg Robidoux