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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.