SQL Server command line backup statement

Question

Does any one know if there is a way to script out SQL Server backup in to a batch file, so that it could be executed from a command line?

Answer 1

if you need the batch file to schedule the backup, the SQL management tools have scheduled tasks built in...

Answer 2

Here's what I use:

echo off
cls
echo -- BACKUP DATABASE --
set /p DATABASENAME=Enter database name:
set BACKUPFILENAME=%CD%\%DATABASENAME%.bak
set SERVERNAME=your server name here
echo.
sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%' WITH INIT , NOUNLOAD , NAME = N'%DATABASENAME% backup', NOSKIP , STATS = 10, NOFORMAT"
echo.
pause

And to add a date-stamp into the output filename (eg MyBackup-2009.5.19.bak):

set DATESTAMP=%DATE:~-4%.%DATE:~7,2%.%DATE:~4,2%
set BACKUPFILENAME=%CD%\%DATABASENAME%-%DATESTAMP%.bak

Answer 3

I am using SQL Server 2005 Express, and I had to enable Named Pipes connection to be able to backup from the Windows Command. My final script is this:

@echo off
set DB_NAME=Your_DB_Name
set BK_FILE=D:\DB_Backups\%DB_NAME%.bak
set DB_HOSTNAME=Your_DB_Hostname
echo.
echo.
echo Backing up %DB_NAME% to %BK_FILE%...
echo.
echo.
sqlcmd -E -S np:\\%DB_HOSTNAME%\pipe\MSSQL$SQLEXPRESS\sql\query -d master -Q "BACKUP DATABASE [%DB_NAME%] TO DISK = N'%BK_FILE%' WITH INIT , NOUNLOAD , NAME = N'%DB_NAME% backup', NOSKIP , STATS = 10, NOFORMAT"
echo.
echo Done!
echo.

It's working just fine here!!