How to Create Full Database Backup on MS SQL Server for a Database using T-SQL "Backup Database" command and SqlCmd Utility
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'
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 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, enviroment 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 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 istw1849 -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.
Here is the contents of the .bat (batch) file backup-script.bat which takes sql backup in SQL Server.
sqlcmd -E -S istw1849 -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 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.
|