What is a simple command line program or script to backup SQL server databases?

Solution 1:

To backup a single database from the command line, use osql or sqlcmd.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" 
    -E -Q "BACKUP DATABASE mydatabase TO DISK='C:\tmp\db.bak' WITH FORMAT"

You'll also want to read the documentation on BACKUP and RESTORE and general procedures.

Solution 2:

I use ExpressMaint.

To backup all user databases I do for example:

C:\>ExpressMaint.exe -S (local)\sqlexpress -D ALL_USER -T DB -BU HOURS -BV 1 -B c:\backupdir\ -DS

Solution 3:

Schedule the following to backup all Databases:

Use Master

Declare @ToExecute VarChar(8000)

Select @ToExecute = Coalesce(@ToExecute + 'Backup Database ' + [Name] + ' To Disk =     ''D:\Backups\Databases\' + [Name]   + '.bak'' With Format;' + char(13),'')
From
Master..Sysdatabases
Where
[Name] Not In ('tempdb')
and databasepropertyex ([Name],'Status') = 'online'

Execute(@ToExecute)

There are also more details on my blog: how to Automate SQL Server Express Backups.

Solution 4:

I found this on a Microsoft Support page http://support.microsoft.com/kb/2019698.

It works great! And since it came from Microsoft, I feel like it's pretty legit.

Basically there are two steps.

1. Create a stored procedure in your master db. See msft link or if it's broken try here: http://pastebin.com/svRLkqnq

2. Schedule the backup from your task scheduler. You might want to put into a .bat or .cmd file first and then schedule that file.

   sqlcmd -S YOUR_SERVER_NAME\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQL_Backup\', @backupType='F'"  1>c:\SQL_Backup\backup.log            
   

Obviously replace YOUR_SERVER_NAME with your computer name or optionally try .\SQLEXPRESS and make sure the backup folder exists. In this case it's trying to put it into c:\SQL_Backup