SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2017
download SQL Server 2016
download SQL Server 2014

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


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'

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


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.

[{ { -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 ]

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

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

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.

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems