How to Create Full Database Backup on MS SQL Server for a Database using T-SQL Backup Database command and SqlCmd Utility

T-SQL BACKUP DATABASE

Below is the t-sql statement which takes a full database backup of a given SQL Server database with database name as a parameter to a given file folder as parameter on the same server or computer.
In order to complete the creating a full database sql backup task, we will use the T-SQL BACKUP DATABASE statement.


Backup Database MySQLSamples To Disk = 'C:\SQLDatabases\MySQLSamples.bak'

Code

For more detailed information with sample t-sql codes on SQL BACKUP DATABASE command you can reference to Create Full Database Backup.

SQLCMD

In order to run the above T-SQL Backup Database statement from command-prompt we can use the sqlcmd utility.
SqlCmd utility will assist sql developers and SQL Server administrators to execute t-sql statements as well as sql procedures from the command prompt.

SqlCmd has a numerous parameters which enables developers and administrators (dba's) great power, flexibility and many abilities on the command prompt over SQL Server.

Here is the usage syntax of SqlCmd command.


sqlcmd 

[{ { -U login_id [ -P password ] } | �E trusted connection }] 

[ -z new password ] [ -Z new password and exit]

[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]

[ -l login time_out ] [ -A dedicated admin connection ] 

[ -i input_file ] [ -o output_file ]

[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]

[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ] 

[ -R use client regional settings ]

[ -q "cmdline query" ] [ -Q "cmdline query" and exit ] 

[ -e echo input ] [ -t query time_out ]
 
[ -I enable Quoted Identifiers ] 

[ -v var = "value"...] [ -x disable variable substitution ]

[ -h headers ][ -s col_separator ] [ -w column_width ] 

[ -W remove trailing spaces ]

[ -k [ 1 | 2 ] remove[replace] control characters ] 

[ -y display_width ] [-Y display_width ]

[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ] 

[ -a packet_size ][ -c cmd_end ] 

[ -L [ c ] list servers[clean output] ] 

[ -p [ 1 ] print statistics[colon format]] 

[ -X [ 1 ] ] disable commands, startup script, environment variables [and exit] 

[ -? show syntax summary ]

Code

For our sample sqlcmd statement code, we will concantrate on �E trusted connection parameter, -S server_name, instance name parameter and -i input file path and name parameters.
Using -E parameter in a SqlCmd statement, we define that trusted connection will be used for connection to SQL Server instead of using user name - password pair to log on to the SQL Server instance.
Following -i parameter, we can define a file which includes the t-sql code scripts to be executed on the SQL Server. SQLCmd will run the sql codes placed on the file configured as the -i parameter value.

Let's save our sample T-SQL Backup Database statement in a .sql file.
Now we are ready to run the contents of the .sql file which I named BackupDatabaseScript.sql using the command-prompt utility.
Execute the below sqlcmd statement after configuring the parameters for your database environment to test and confirm the execution of the sqlcmd code.


sqlcmd -E -S servername -i C:\SQLDatabases\BackupDatabaseScript.sql

Code

Automating SQLCMD Database Backup using a Batch File

After approving the sqlcmd statement is working successfully for creating a full database sql backup, we can save the sqlcmd statement in a batch file (.bat file) for a simple execution.
By a double click on the batch file, the sqlcmd code will execute on the related MS SQL Server instance in order to create a SQL Server database full backup.

t-sql sqlcmd command for running sql scripts using command prompt

Here is the contents of the .bat (batch) file backup-script.bat which takes sql backup in SQL Server.


sqlcmd -E -S servername -i C:\SQLDatabases\BackupDatabaseScript.sql

Code

As you can see, by some t-sql developing SQL Server administrators can build their custom SQL backup software applications easily.
Download .sql file for SQL BACKUP DATABASE command BackupDatabaseScript.sql.
Download batch file for SQLCMD command backup-script.bat.

T-SQL programmes and Microsoft SQL Server administrators will find sample sql backup tutorial at MS SQL Server Backup using Cmd Windows Command Line Backup with SQLCmd.